[postgis-users] Query help

[Obe, Regina]

Do you have a gist index on the mo_org table as well?
 
To see if your query is using the gist index,  you would do an explain analyze on your query.
 
If you are using PgAdmin III - under the query menu, there is an explain option which gives you a graphical explain.  I find it quite helpful for viewing the explain plan.
 
It may be helpful also to have an index on your mo_org.mo field depending on the distribution of that (e.g. if you have lots of different numbers, then an index would be helpful, if it varies between say 1 and 2, then an index on that field would probably be ignored.)
 
Hope that helps,
Regina
 
 
 
 

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Stephen Crawford
Sent: Wed 4/18/2007 1:14 PM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] Query help



Hello All,

I have a query that gets the name and id of all the features from table Y
that intersect a feature fro table X:

SELECT DISTINCT(m.mlra_name) AS name,m.mlra_reg_s as id FROM mlra AS m,
mo_org AS o
WHERE m.the_geom && o.the_geom
AND intersects(m.the_geom,o.the_geom)
AND o.mo = 1;

Is this an efficient way to write this query? I have a gist index on the
mlra table, so I included the line:

WHERE m.the_geom && o.the_geom

In hope that it would speed things up, because it should use the gist index.
Am I correct?  It doesn't seem to make a difference.  I run  vacuum analyze.
How can I check the geometry stats, and how can I tell if the index is being
used?

Thanks,
Steve


Stephen Crawford
Center for Environmental Informatics
GeoVISTA Center
The Pennsylvania State University
814.865.9905
src176 at psu.edu

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





-----------------------------------------
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/20070418/3b328809/attachment.html>