Select to view content in your preferred language

Support multi-column GIST index on PostgreSQL

422
0
08-01-2022 08:09 AM
Status: Open
RandalGreene
Occasional Contributor

We have a large Point feature class in our non-hosted PostgreSQL enterprise geodatabase that our users access remotely from Pro and either 1) zoom in to view a subset of points or 2) provide an attribute query to limit the total number of points returned. We have a spatial (GIST) index and an attribute (B-Tree) index for the queried attribute. The issue is that if the user sets an attribute query then zooms out, the spatial index is used even though the attribute is more selective, resulting in poor performance (all points are returned to Pro which then filters based on the queried attribute). Apparently, this is due to the database not being able to accurately assess selectivity on spatial indexes. It was recommended by a PostgreSQL expert that we use a GIST index on the geometry plus the queried attribute, and this works well when zoomed out but introduces a bug whereby Pro does not display any records when the user zooms in! I find this surprising because database systems are supposed to be deterministic (i.e., should always give the correct answer even if it is not an efficient query plan). So, this enhancement request is to properly support these multi-column GIST indexes which will help query performance by geometry, by attribute, or both.