[PostGIS] #5807: Uncorrelated ST_Transform runs on each filter evaluation

PostGIS trac at osgeo.org
Thu Oct 31 09:33:17 PDT 2024


#5807: Uncorrelated ST_Transform runs on each filter evaluation
---------------------+---------------------
 Reporter:  lnicola  |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:
Component:  postgis  |    Version:  3.5.x
 Keywords:           |
---------------------+---------------------
 During the following query, ST_Transform runs 1817760 times:

 {{{
 with tiles as (
     select tile_id, ST_Transform(geom, 2157) as geom
     from shape_tiles_s2
     where tile_id in ('29UMA', '29UMT', '29UMU', '29UMV', '29UNA',
 '29UNT', '29UNU', '29UNV', '29UPA', '29UPT', '29UPU', '29UPV')
 )
 select count(*)
 from polygons, tiles
 where ST_Intersects(polygons.wkb_geometry, tiles.geom);
 }}}

 If you switch to a materialized CTE, it only runs 12 times:

 {{{
 with tiles as materialized (
     select tile_id, ST_Transform(geom, 2157) as geom
     from shape_tiles_s2
     where tile_id in ('29UMA', '29UMT', '29UMU', '29UMV', '29UNA',
 '29UNT', '29UNU', '29UNV', '29UPA', '29UPT', '29UPU', '29UPV')
 )
 select count(*)
 from polygons, tiles
 where ST_Intersects(polygons.wkb_geometry, tiles.geom);
 }}}

 Don't read too much into the CTE. I checked with robe, and pretty much
 every other way to write it (with a join, with a subquery, with a lateral
 join) is even slower than the two CTE version.

 To test, use:

 {{{
 set track_functions = 'all';
 select * from pg_stat_user_functions; select pg_stat_reset();
 }}}

 You can download the {{{polygons}}} dataset from
 https://data.europa.eu/data/datasets/d18f6f95-1d96-4b86-a4ba-
 291a416e45bb?locale=en, it's the one called "Anonymous LPIS 2023 Parcels",
 import using e.g.:

 {{{
 ogr2ogr -nln polygons -nlt geometry "PG:host=127.0.0.1 user=postgres
 dbname=postgres" GEOSERVICESHELP-213-PARCELS-ANON.shp
 }}}

 The {{{shape_tiles_s2}}} table is attached. It's probably derived from
 S2A_OPER_GIP_TILPAR_MPC__20151209T095117_V20150622T000000_21000101T000000_B00.zip,
 which you can find on https://sentiwiki.copernicus.eu/web/s2-products.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5807>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.


More information about the postgis-tickets mailing list