public inbox for [email protected]
help / color / mirror / Atom feedFrom: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19377: Query planner interesting behaviour
Date: Tue, 13 Jan 2026 06:15:10 +0000
Message-ID: <[email protected]> (raw)
The following bug has been logged on the website:
Bug reference: 19377
Logged by: Juneidy Wibowo
Email address: [email protected]
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?
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19377: Query planner interesting behaviour
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox