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)" < [email protected]> 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 > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
