Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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
share|improve this question
1  
can you post explain analyze results from both? – Jack Douglas♦ Apr 17 '12 at 15:06
@Jack - Updated original post. – Jmoney38 Apr 17 '12 at 15:16
things I notice - the cost estimate is higher on the second (as expected - that's why the optimizer chooses the sequential scan). Also of note, the actual time although quicker with the index is not an order of magnitude quicker, so the optimizer is in the right ball park – Jack Douglas♦ Apr 17 '12 at 20:43
can you post info on the objects (polyG, polyidx) as well as the full query SQL? Also a reference for "&&, which just use a bounding box" might help. – Jack Douglas♦ Apr 17 '12 at 20:47
I agree about the order of magnitude difference, but I would think the planner would always favor indicies, especially when this shows the index usage takes less than 1/2 the time as the seq scan. – Jmoney38 Apr 19 '12 at 14:31
show 4 more comments

closed as too localized by Jack Douglas♦ Apr 19 '12 at 18:33

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

Browse other questions tagged or ask your own question.