The following bug has been logged on the website:
Bug reference: 19377
Logged by: Juneidy Wibowo
Email address: postgresql@juneidy.wibowo.au
PostgreSQL version: 18.0
Operating system: linux postgres:18-trixie
Description:
Having two queries like below:
explain analyze SELECT id from table_a where ST_Intersects(geometry,
ST_MakeValid((SELECT st_transform(geom,4326) FROM table_b )));
explain analyze SELECT id from table_b where ST_Intersects(geometry, (SELECT
ST_MakeValid((SELECT st_transform(geom,4326) FROM table_b ))));
Result in two different query plan/execution:
=> explain analyze SELECT id from table_a where ST_Intersects(geometry,
ST_MakeValid((SELECT st_transform(geom,4326) FROM table_b )));
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using features_geometry_idx on features
(cost=17036.38..18524.35 rows=55 width=8) (actual time=1.730..9592.568
rows=20921 loops=1)
Index Cond: (geometry && st_makevalid($0))
Filter: st_intersects(geometry, st_makevalid($0))
Rows Removed by Filter: 10731
InitPlan 1 (returns $0)
-> Seq Scan on region (cost=0.00..17023.60 rows=1360 width=32)
(actual time=0.324..0.328 rows=1 loops=1)
Planning Time: 0.090 ms
Execution Time: 9619.139 ms
(8 rows)
=> explain analyze SELECT id from table_b where ST_Intersects(geometry,
(SELECT ST_MakeValid((SELECT st_transform(geom,4326) FROM table_b ))));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using features_geometry_idx on features
(cost=17036.39..17836.86 rows=55 width=8) (actual time=1.467..111.507
rows=20921 loops=1)
Index Cond: (geometry && $1)
Filter: st_intersects(geometry, $1)
Rows Removed by Filter: 10731
InitPlan 2 (returns $1)
-> Result (cost=17023.60..17036.11 rows=1 width=32) (actual
time=1.232..1.237 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Seq Scan on region (cost=0.00..17023.60 rows=1360
width=32) (actual time=0.360..0.364 rows=1 loops=1)
Planning Time: 0.085 ms
Execution Time: 136.482 ms
(10 rows)
It's weird that in the first query postgres doesn't seem to treat
st_makevalid($0) as a constant and I think re-evaluate st_makevalue($0) for
every row. Is that an expected behaviour?
