Uncorrelated ST_Transform runs on each filter evaluation

[lnicola]

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)"