Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19377: Query planner interesting behaviour
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: postgresql@juneidy.wibowo.au
Reply-To: postgresql@juneidy.wibowo.au, pgsql-bugs@lists.postgresql.org
Date: Tue, 13 Jan 2026 06:15:10 +0000
Message-ID: <19377-6797bfc0a06bf23c@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19377-6797bfc0a06bf23c%40postgresql.org>
Precedence: bulk

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:       =20

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:
=3D> 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=3D17036.38..18524.35 rows=3D55 width=3D8) (actual time=3D1.730..9592.=
568
rows=3D20921 loops=3D1)
   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=3D0.00..17023.60 rows=3D1360 width=3D32)
(actual time=3D0.324..0.328 rows=3D1 loops=3D1)
 Planning Time: 0.090 ms
 Execution Time: 9619.139 ms
(8 rows)

=3D> 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=3D17036.39..17836.86 rows=3D55 width=3D8) (actual time=3D1.467..111.5=
07
rows=3D20921 loops=3D1)
   Index Cond: (geometry && $1)
   Filter: st_intersects(geometry, $1)
   Rows Removed by Filter: 10731
   InitPlan 2 (returns $1)
     ->  Result  (cost=3D17023.60..17036.11 rows=3D1 width=3D32) (actual
time=3D1.232..1.237 rows=3D1 loops=3D1)
           InitPlan 1 (returns $0)
             ->  Seq Scan on region  (cost=3D0.00..17023.60 rows=3D1360
width=3D32) (actual time=3D0.360..0.364 rows=3D1 loops=3D1)
 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?