[postgis-users] Query PLanning for spatial containment

[Alexandre Neto]

Hello,

How about the vertices number of each of your  tables? I believe the
st_transform() will operate in each geometry vertex. With the same number
of row\features a polygon geometry table will have at least 3 times (a
triangle) more vertices than a point  geometry table.

Wouldn't it explain the difference?

Best regards,

Alexandre Neto
No dia 29 de Ago de 2013 20:11, "Burgholzer, Robert (DEQ)" <
Robert.Burgholzer at deq.virginia.gov> escreveu:

>  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.
>
> /r/b
>
> For example:
>   postgis_version
> ---------------------------------------
>  1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 / PostgreSQL 8.3
>
>
>                                           QUERY PLAN
>
> -----------------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..16874.46 rows=75352 width=82234)
>    Join Filter: contains(b.the_geom, transform(a.the_geom, 26918))
>    ->  Seq Scan on sc_cbp53 b  (cost=0.00..65.09 rows=1 width=81986)
>          Filter: ((riverseg)::text = 'PS3_5100_5080'::text)
>    ->  Seq Scan on vpdes_discharge_no_ms4_cache a  (cost=0.00..13418.55
> rows=226055 width=248)
> (5 rows)
>
> vpdes=# explain select * from vpdes_discharge_no_ms4_cache as a, sc_cbp53
> as b where
> contains(transform(b.the_geom,4326),a.the_geom);
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..18815543.18 rows=109033851 width=82234)
>    Join Filter: contains(transform(b.the_geom, 4326), a.the_geom)
>    ->  Seq Scan on vpdes_discharge_no_ms4_cache a  (cost=0.00..13418.55
> rows=226055 width=248)
>    ->  Seq Scan on sc_cbp53 b  (cost=0.00..61.47 rows=1447 width=81986)
> (4 rows)
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130830/aec76e0a/attachment.html>