BUG #19377: Query planner interesting behaviour
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?
On Tue, 13 Jan 2026 at 21:57, PG Bug reporting form
<noreply@postgresql.org> wrote:
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?
Yes, it's expected. We don't do anything special to try and walk up
function call chains of immutable functions for init plan parameters
to push function calls down to the init plan. Perhaps something is
possible here, but anyone proposing we change this would need to come
armed with proof that it's safe to do this. It's not really clear to
me that doing this couldn't change some evaluation order and cause
changes which could result in errors being raised when they shouldn't
or not being raised when they should. We do only run the initplan if
the outer query has at least 1 row, so maybe it's safe, but it would
take some analysis to veryify that's true for all possible cases.
You're best to just put the SELECT in the position so as to minimise
the redundant function calls.
David
David Rowley <dgrowleyml@gmail.com> writes:
On Tue, 13 Jan 2026 at 21:57, PG Bug reporting form
<noreply@postgresql.org> wrote: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?
Yes, it's expected. We don't do anything special to try and walk up
function call chains of immutable functions for init plan parameters
to push function calls down to the init plan. Perhaps something is
possible here, but anyone proposing we change this would need to come
armed with proof that it's safe to do this.
It would be a fairly hard sell, I think, because traditionally
PG users have understood that a scalar sub-SELECT like this is an
optimization fence. Pushing stuff across that fence is likely to
break some hand-optimized queries.
regards, tom lane