<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<html >
<head>
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
font-face
{font-family:"Cambria Math";}
font-face
{font-family:Calibri;}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{
color:purple;
text-decoration:underline;}
span.EmailStyle17
{
font-family:"Calibri","sans-serif";
color:windowtext;}
..MsoChpDefault
{}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=PT link=blue vlink=purple>
<DIV id=idOWAReplyText1753 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>I assume the srid of your
t.geometry field is 4326.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>How do you have your index specified?
If you have it something like</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>CREATE INDEX
idx_pt_madeira_topnymy_geometry ON pt_madeira_toponymy USING
GIST(transform(geometry, 4326));</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>rather than </FONT></DIV>
<DIV dir=ltr>
<DIV dir=ltr><FONT face=Arial size=2>CREATE INDEX
idx_pt_madeira_topnymy_geometry ON pt_madeira_toponymy USING
GIST(geometry);</FONT></DIV></DIV>
<DIV dir=ltr><FONT face=Arial size=2> </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>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)</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>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'</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><SPAN
class=content> </DIV></SPAN></FONT></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 Pedro Doria
Meunier<BR><B>Sent:</B> Sat 3/31/2007 2:57 PM<BR><B>To:</B> 'PostGIS Users
Discussion'<BR><B>Subject:</B> [postgis-users] Indexes not being
used<BR></FONT><BR></DIV>
<DIV>
<DIV class=Section1>
<P class=MsoNormal><SPAN lang=EN-US>Hi all (with a special wink to Regina ;-
)</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>This is the query not using
indices:</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>SELECT parish,county,geometry FROM
pt_madeira_toponymy as t WHERE parish=upper('se') AND</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US> t.geometry &&
geomfromtext('POINT(-16.9213592631455 32.6437878212273)',4326) AND
intersects(geomfromtext('POINT(-16.9213592631455 32.6437878212273)',4326),
t.geometry);</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>As you can plainly see it checks if a point
is inside some polygon.</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>EXPLAIN ANALYZE returns this:</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>"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)"</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>" Filter: ((parish = 'SE'::text) AND
(geometry &&
'0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry) AND
intersects('0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry,
geometry))"</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>"Total runtime: 1.312 ms"</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>The toponymy has two indices: one for 4326
and another for 32628 using GiST.</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Is the intersects function not using indices
at all??</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Already thankful for any ideas,</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>With best regards,</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>Pedro Doria
Meunier.</SPAN></P></DIV></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>