Solved! Go to Solution.
- If the feature data has been gathered over time it is likely badly spatially
fragmented. One of the hallmarks of spatial fragmentation is poor spatial
query performance, not matter how artfully the spatial index has been tuned.
There are many possible ways to defragment spatially, but the easiest is
to do an ORDER BY query on some attribute that imposes spatial order
(county code, etc). More exotic queries are available if you can use the
ST functions to group features into bands in X (or Y), then order by Y (or X).
Note that spatial defragmentation will hurt join performance on other
query constraints (like time), so that may need to be mitigated.
CREATE TABLE mytab_backup AS SELECT * FROM mytab_backup
INSERT INTO mytab SELECT * FROM mytab_backup ORDER BY county_fips
I have two unversioned featureclasses in a dataset that our organization adds and deletes up to 10s of thousands of records daily. One is over 3million points and the other is over 500k lines