[postgis] Indexing....ack!

[harmonk00]

Before I start complaining, let me lead in with this:  The postgis 
extensions that you've done are very impressive!   Good job!



Okay, now I complain:

I have a table with about 90,000 rows in it.  The table has a 
geometry column that always contains a 2D POINT.  I created a GIST 
index on this column.  

I do a query against the table to select the records inside a certain 
area, and it will NOT use the index, even though if I force it to, by 
turning off the table scans, the query executes MUCH faster!  Is 
there anything that can be done about this?  I would greatly 
appreciate any help.


Here is my table:

create table pole (bec_record_id numeric(8), ipid numeric(20), 
featuresymgeometries geometry);




Here is my index:

CREATE INDEX POLE_NDX_1 ON Pole USING GIST ( featuresymgeometries 
GIST_GEOMETRY_OPS ) WITH ( ISLOSSY );




Here is my select:

explain select count(*) from  Pole where  Pole.featuresymgeometries 
&& 'BOX3D(-69.9392089498987 18.47719733442078,-69.9368322996733 
18.478711603947218)'::box3d   and on_image is null




Results with seqscan enabled:

Aggregate  (cost=8699.73..8699.73 rows=1 width=0)
  ->  Seq Scan on pole  (cost=0.00..8677.16 rows=9025 width=0)



Results with seqscan off:

set enable_seqscan=off;

explain select count(*) from  Pole where  Pole.featuresymgeometries 
&& 'BOX3D(-69.9392089498987 18.47719733442078,-69.9368322996733 
18.478711603947218)'::box3d   and on_image is null

Aggregate  (cost=22441.74..22441.74 rows=1 width=0)
  ->  Index Scan using pole_ndx_1 on pole  (cost=0.00..22419.18 
rows=9025 width=0)





To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/