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

 Tested on

 {{{
 POSTGIS="3.5.0 d2c3ca4" [EXTENSION] PGSQL="170" GEOS="3.13.0-CAPI-1.19.0"
 PROJ="9.5.0 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
 DATABASE_PATH=/usr/local/share/proj/proj.db" (compiled against PROJ
 9.13.0) LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1" WAGYU="0.5.0
 (Internal)" TOPOLOGY
 }}}

 and

 {{{
 POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="160" GEOS="3.12.2-CAPI-1.18.2"
 PROJ="9.4.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/var/lib/pgsql/.local/share/proj
 DATABASE_PATH=/usr/proj94/share/proj/proj.db" LIBXML="2.9.13"
 LIBJSON="0.14" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
 }}}

--
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5807#comment:3>
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.