[postgis-users] Indexes not being used

[Obe, Regina]

I assume the srid of your t.geometry field is 4326.
 
How do you have your index specified?  If you have it something like
 
CREATE INDEX idx_pt_madeira_topnymy_geometry ON  pt_madeira_toponymy USING GIST(transform(geometry, 4326));
 
rather than 
CREATE INDEX idx_pt_madeira_topnymy_geometry ON  pt_madeira_toponymy USING GIST(geometry);
 
I don't think it will recognize it as a valid index for your query since your query is using geometry rather than transform(geometry, 4326)
 
Other thing to check - you should have an index on your parish field.  I think that may have better selectivity depending on how many parish are 'SE'
 
Hope that helps,
Regina
 

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Pedro Doria Meunier
Sent: Sat 3/31/2007 2:57 PM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] Indexes not being used



Hi all (with a special wink to Regina ;- )

 

This is the query not using indices:

 

SELECT parish,county,geometry FROM pt_madeira_toponymy as t WHERE parish=upper('se') AND

  t.geometry && geomfromtext('POINT(-16.9213592631455 32.6437878212273)',4326) AND intersects(geomfromtext('POINT(-16.9213592631455 32.6437878212273)',4326), t.geometry);

 

As you can plainly see it checks if a point is inside some polygon.

 

EXPLAIN ANALYZE returns this:

"Seq Scan on pt_madeira_toponymy t  (cost=0.00..3.03 rows=1 width=96) (actual time=1.086..1.278 rows=1 loops=1)"

"  Filter: ((parish = 'SE'::text) AND (geometry && '0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry) AND intersects('0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry, geometry))"

"Total runtime: 1.312 ms"

 

The toponymy has two indices: one for 4326 and another for 32628 using GiST.

 

Is the intersects function not using indices at all??

 

Already thankful for any ideas,

With best regards,

Pedro Doria Meunier.




-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070401/86f7cbb8/attachment.html>