<div dir="ltr">Remi -<div><br></div><div>Thanks again for the quick response. I dug into this a bit more last night, and apparently the issue was the distance I was using with the ST_DWithin call. When I had a projection under 2230, the unit of measure was feet. With 4326 it appears to be degrees. I kept the distance of 100 on both tests, so the 2230 project returned quick, but a distance of 100 on a 4326 projection is very large so it was returning my whole table. When I changed the distance to something small (0.0009) my query is now fast. ;-)</div>
<div><br></div><div>My next challenge will be figuring out the best way to convert between degrees & meters. I understand the geography type is better for queries like this, but I'm already using the geometry type for other queries. Is it common to have both geometry and geography for a record in a table? I know I can cast using ::geography but the queries are dramatically slower.</div>
<div><br></div><div>Alex </div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Thu, Jul 10, 2014 at 1:11 AM, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><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 class=""><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></div>) AS mip</div><div class=""><div>INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);</div>
</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" target="_blank">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>:<div>
<div class="h5"><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></div></div><br></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
</blockquote></div><br><br clear="all"><div><br></div>-- <br>Alexander W. Rolek<br>303-829-9989
</div>