- Home
- :
- All Communities
- :
- Products
- :
- ArcGIS Enterprise
- :
- ArcGIS Enterprise Ideas
- :
- Support multi-column GIST index on PostgreSQL
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Follow this Idea
- Printer Friendly Page
- Report Inappropriate Content
Support multi-column GIST index on PostgreSQL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.