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

[PostGIS]

#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
Resolution:           |   Keywords:
----------------------+---------------------
Description changed by lnicola:

Old description:

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

New description:

 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#comment:1>
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.