[postgis] Indexing....ack!

[Paul Ramsey]

This is kind of a "known problem" and is pointed out in the
documentation: in order to get the spatial indexes to work, you have to
force them on with 'set enable_seqscan = off'. So, does this suck? Oh
yeah, baby, yeah!

Dave has made noises in the past that it might be possible to get the
planner to do the "right thing" with respect to spatial indexes by
jimmying with the weighting and scores for our index types in the system
tables. Perhaps he could look into this... *cough*


harmonk00 at yahoo.com wrote:
> 
> 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/

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/