<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7651.59">
<TITLE>[postgis-users] Query help</TITLE>
</HEAD>
<BODY>
<DIV id=idOWAReplyText84307 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 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>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></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>