[postgis-users] Indexes not being used

[Pedro Doria Meunier]

Hello Regina,

 

Txs for replying.

 

You're right. I was missing an index for the text fields. I realized this
and immediately applied the theory with grim results. L

 

It turns out (?) that one as to use TSearch2 to have the indices fired up
for text fields. Someone please rebate me if I'm wrong.

 

Anyway I'm still not ready to mess up with my tables' schema in case someone
comes up with a more enlightened solution.

 

Best regards,

Pedro.

 

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: domingo, 1 de Abril de 2007 22:34
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Indexes not being used

 

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/0cd18b64/attachment.html>