mapcentia / geocloud2

The GC2 framework helps you build a spatial data infrastructure quickly and easily. Powered using open source components for a scalable solution focused on freedom rather than fees.
https://www.osgeo.org/projects/gc2-vidi/
GNU Affero General Public License v3.0
147 stars 67 forks source link

Using privileges from view is slow when adding multiple external sources #144

Open giovanniborella opened 1 month ago

giovanniborella commented 1 month ago

https://github.com/mapcentia/geocloud2/blob/75d2aec4f45f61be150573a37bcdbf7ccd94a7c1/app/inc/http_basic_authen.php#L64

we have currently changes to this instead: $sql = "SELECT privileges FROM settings.geometry_columns_join WHERE _key_ = :schema"; which cuts the query time dramatically.

our working theory is, that the view is slowed down when accessing sourced that were define ie. as FT. the code block only uses privileges field. before this, we successfully sped up the query by setting random_page_cost to 1 (from default 4), to make sure indexes were used.