I have a large table that stores, among other attributes, a geometry type. I have a GIST index on this attribute. However, when I perform even the most basic queries [say, select count(*) from table where the_geom && st_geomfromtext('polygon(...)', 4326)], it doesn't use the index unless I force it using [set enable_seqscan=off].
So one question is why the index is not being used? I have read articles explaining that if the table size is relatively small, the query planner will favor a seq. scan over an index lookup. However, this table has over 450,000 entries. Not small, I don't think.
A second question is more related to the title. Many of these polygons have a large number of vertex points. They're automatically generated by an application we use. Therefore, the range is anywhere from 5 vertex points to 350. I realize this could be a performance problem for functions such as ST_Intersects, but for functions like &&, which just use a bounding box, this shouldn't matter much, right? We are contemplating an idea of "trimming" these high-vertex polygons to help with query performance. Any thoughts?
Explain with enable_seqscan=on (default value)
Aggregate (cost=23789.29..23789.30 rows=1 width=0) (actual time=59199.279..59199.281 rows=1 loops=1) -> Seq Scan on polyG (cost=0.00..22666.74 rows=449019 width=0) (actual time=0.021..57814.194 rows=449019 loops=1) Filter: (the_geom && '0103..etc..'::geometry) Total runtime: 59199.379 ms
Explain with enable_seqscan=off
Aggregate (cost=88552.75..88552.76 rows=1 width=0) (actual time=26577.496..26577.499 rows=1 loops=1) -> Index Scan using polyidx on polyG (cost=0.00..87430.20 rows=449019 width=0) (actual time=40.097..25398.465 rows=449019 loops=1) Index Cond: (the_geom && '0103...etc...'::geometry) Total runtime: 26611.017 ms