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
