[postgis-users] Query PLanning for spatial containment

[BladeOfLight16]

On Thu, Aug 29, 2013 at 3:02 PM, Burgholzer, Robert (DEQ) <
Robert.Burgholzer at deq.virginia.gov> wrote:

>  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.
>

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.)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130829/4b9aaf1c/attachment.html>