[postgis-users] Query help

[Stephen Crawford]

Thanks.  Just learning that EXPLAIN and EXPLAIN ANALYZE are what I needed to
look up in the docs is a great help.

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: Wednesday, April 18, 2007 2:28 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Query help


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