<html dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style id="owaParaStyle" type="text/css">P {margin-top:0;margin-bottom:0;}</style>
</head>
<body ocsi="0" fpstyle="1">
<div style="direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;">Dude, I am soooo dumb. You're right, the query planner difference is in the WHERE. And, yes, right again, I need to use the && to trigger the index, which works much more swiftly.<br>
<br>
/r/b<br>
<div style="font-family: Times New Roman; color: #000000; font-size: 16px">
<hr tabindex="-1">
<div style="direction: ltr;" id="divRpF883002"><font color="#000000" face="Tahoma" size="2"><b>From:</b> postgis-users-bounces@lists.osgeo.org [postgis-users-bounces@lists.osgeo.org] on behalf of BladeOfLight16 [bladeoflight16@gmail.com]<br>
<b>Sent:</b> Thursday, August 29, 2013 10:08 PM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] Query PLanning for spatial containment<br>
</font><br>
</div>
<div></div>
<div>
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">On Thu, Aug 29, 2013 at 3:02 PM, Burgholzer, Robert (DEQ)
<span dir="ltr"><<a href="mailto:Robert.Burgholzer@deq.virginia.gov" target="_blank">Robert.Burgholzer@deq.virginia.gov</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left:1px solid rgb(204,204,204); padding-left:1ex">
<div>
<div style="direction:ltr; font-size:10pt; font-family:Tahoma">I just noticed something interesting, perhaps its trivial and well understood, but this is the first time I figured it out (older postgis and postgresql to boot). I am doing a spatial containment
query on two tables, table "a" being a point table in SRID 4326, with GIST index, and table "b" being a polygon with GIST index in SRID 26918, using the "transform" function to bring them into a common projection. If I apply the transform to the polygon layer,
the query planner (and indeed the query) is verrrry slow, whereas, if I apply the transform to the point layer, things go along much more swiftly. Now I know, so I just thought I'd share, if anyone has any suggestions of course, I would be delighted to hear
them.<br>
</div>
</div>
</blockquote>
<div><br>
Are you sure it's not the filter Filter: ((riverseg)::text = 'PS3_5100_5080'::text) that appears to only be present in the first query? I do notice that the estimate from the sc_cbp53 goes down from 1447 to 1 with the filter. EXPLAIN ANALYZE would give more
information about what actually happened, and I wouldn't expect the query planner to behave the same in other versions of PostGIS. You might also benefit from a spatial index, and I believe in the older version, you need to do a bounding box check (&&) manually
to trigger use of the index. (I can't recall which version, but one version of PostGIS built the && check into ST_Contains and many other functions.)<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>