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.