<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>[postgis-users] Query help</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16414" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=455384618-18042007><FONT face=Tahoma
size=2>Thanks. Just learning that EXPLAIN and EXPLAIN ANALYZE are what I
needed to look up in the docs is a great help.</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Obe,
Regina<BR><B>Sent:</B> Wednesday, April 18, 2007 2:28 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> RE: [postgis-users] Query
help<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV id=idOWAReplyText84307 dir=ltr>
<DIV dir=ltr><FONT face=Arial size=2>Do you have a gist index on the mo_org
table as well?</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>To see if your query is using the gist
index, you would do an explain analyze on your query.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>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.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>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.)</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Hope that helps,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>Regina</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net on behalf of Stephen
Crawford<BR><B>Sent:</B> Wed 4/18/2007 1:14 PM<BR><B>To:</B> 'PostGIS Users
Discussion'<BR><B>Subject:</B> [postgis-users] Query help<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Hello All,<BR><BR>I have a query that gets the name and id of
all the features from table Y<BR>that intersect a feature fro table
X:<BR><BR>SELECT DISTINCT(m.mlra_name) AS name,m.mlra_reg_s as id FROM mlra AS
m,<BR>mo_org AS o<BR>WHERE m.the_geom && o.the_geom<BR>AND
intersects(m.the_geom,o.the_geom)<BR>AND o.mo = 1;<BR><BR>Is this an efficient
way to write this query? I have a gist index on the<BR>mlra table, so I included
the line:<BR><BR>WHERE m.the_geom && o.the_geom<BR><BR>In hope that it
would speed things up, because it should use the gist index.<BR>Am I
correct? It doesn't seem to make a difference. I run vacuum
analyze.<BR>How can I check the geometry stats, and how can I tell if the index
is being<BR>used?<BR><BR>Thanks,<BR>Steve<BR><BR><BR>Stephen Crawford<BR>Center
for Environmental Informatics<BR>GeoVISTA Center<BR>The Pennsylvania State
University<BR>814.865.9905<BR>src176@psu.edu<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR>postgis-users@postgis.refractions.net<BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV>
<P>
<HR SIZE=1>
<P></P><BR>
<P><STRONG><BR>The substance of this message, including any attachments, may
be<BR>confidential, legally privileged and/or exempt from disclosure<BR>pursuant
to Massachusetts law. It is intended solely for the<BR>addressee. If you
received this in error, please contact the sender<BR>and delete the material
from any computer.<BR></STRONG></P></BODY></HTML>