<div dir="ltr"><div><div><div><div><div><div><div><div><div><div><div>Hey,<br></div></div>If I take into account your last mail, <br>you probably have forget to transform your data.<br><br></div>If you have a table in srid 4326 <br>
, you can't use a transform in your querry if you want it to uses indexes.<br></div>OR you have to buil and index like this : <br>CREATE INDEX ON sdgis.parcels4326 USING GIST (ST_Transform(geom, 4326));<br><br></div>So can you confirm thatl your geom have the correct srid in the correct table? <br>
<br></div>SELECT DISTINCT ST_SRID(geom)<br></div>FROM sdgis.parcels ;<br><br></div>and <br>SELECT DISTINCT ST_SRID(geom)<br>FROM sdgis.parcels4326 ;<br><br></div>You uses pgadmin , so instead of running your querry <br>, run it with "explain analyse" (Select the text of your querry, then press shift+F7).<br>
</div>You should see a graphic explaining what it happening. You should look for sequential scans.<br><br></div>You can also try the same querry without CTE :<br><br><div><div>SELECT p.*</div><div>FROM (<div><span style="white-space:pre-wrap"> </span>SELECT geom</div>
<div><span style="white-space:pre-wrap"> </span>FROM sdgis.parcels4326 AS parcels</div>
<span style="white-space:pre-wrap"> </span>WHERE apn = '3500600300<br>) AS mip</div><div>INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);</div>
</div><div><br></div><br><div><div><div><div><div><div><div><div><div><div><div><div>Of course vacuum analyze both table before running the querry.<br>It would be difficult to help you more without detailed information (tables declarations, querry used, result of explain analyse), because slowness can come from several reasons .<br>
<br></div><div>Here is a link of requirements for people having slow querry on postgres mailing list (<a href="https://wiki.postgresql.org/wiki/Slow_Query_Questions">https://wiki.postgresql.org/wiki/Slow_Query_Questions</a>). <br>
</div><div>I'm not guru so I couldn't use all this informations but that give you an idea of how many reasons of slow query they can be<br></div><div><br>Cheers,<br></div><div>Rémi-C<br></div><div><br><br><br><br>
""<br><div dir="ltr">I tried the query that Remi suggested (thank you!) and it
works and doesn't work. I have the exact same table in two different
projections (2230, 4326). When I run the ST_Dwithin query against the
2230 table, I get results in under 100 ms. When I run the same query
against the 4326 projection it still takes around 270 seconds! I have
confirmed that I have the gist index on the 4326 table. Any ideas why
the 4326 projection would be dramatically slower?</div>
<br>""<br><div class="gmail_extra"><br><br><div class="gmail_quote">2014-07-09 18:38 GMT+02:00 Alexander W. Rolek <span dir="ltr"><<a href="mailto:a.rolek@gmail.com" target="_blank">a.rolek@gmail.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Remi - <div><br></div><div>Thanks for the quick response. Sorry to respond to you directly, but my message settings are set on digest so I can't respond to the thread yet.</div>
<div><br></div><div>I went through your steps, and have built out my query, but it's still taking around 270 seconds to run the query, and it's returning ever record in my table. Here's my query:</div>
<div><br></div><div><div>WITH my_input_polygon AS (</div><div><span style="white-space:pre-wrap"> </span>SELECT geom</div><div><span style="white-space:pre-wrap"> </span>FROM sdgis.parcels4326 AS parcels</div><div>
<span style="white-space:pre-wrap"> </span>WHERE apn = '3500600300'</div><div>)</div><div>SELECT p.*</div><div>FROM my_input_polygon AS mip</div><div>INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);</div>
</div><div><br></div><div>I also have indexes on the apn and the geom columns (see attached screen shots)</div><div><br></div><div>Any ideas what I'm missing here?</div><div><br></div><div>Thanks again for the help</div>
<span><font color="#888888">
<div><br></div><div>-- <br>Alexander W. Rolek<br></div></font></span></div>
</blockquote></div><br></div></div></div></div></div></div></div></div></div></div></div></div></div></div>