MIME-Version: 1.0
References: <20250722151735.toridqqnb3krhimp@hjp.at>
In-Reply-To: <20250722151735.toridqqnb3krhimp@hjp.at>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 23 Jul 2025 10:08:31 +1200
Message-ID: <CAApHDvrDM=QYt2m5oWpRME3Hdy6S=sQVBpxOTqOWvDeUOnR1EQ@mail.gmail.com>
Subject: Re: Wrapping a select in another select makes it slower
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvrDM%3DQYt2m5oWpRME3Hdy6S%3DsQVBpxOTqOWvDeUOnR1EQ%40mail.gmail.com>
Precedence: bulk

On Wed, 23 Jul 2025 at 03:18, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> PostgreSQL version 17.5 on Ubuntu 24.04 (PGDG build).


>               ->  Merge Left Join  (cost=4613.25..7180.30 rows=8357 width=136) (actual time=222.037..292242.701 rows=40460 loops=1)
>                     Merge Cond: (ns.nspname = s.schemaname)
>                     Join Filter: ((s.tablename = tbl.relname) AND (s.attname = att.attname))
>                     Rows Removed by Join Filter: 1215045276

>                     ->  Sort  (cost=4476.84..4477.18 rows=139 width=200) (actual time=156.864..178628.030 rows=1215036357 loops=1)
>                           Sort Key: s.schemaname
>                           Sort Method: external sort  Disk: 6480kB
>                           ->  Subquery Scan on s  (cost=2189.59..4471.89 rows=139 width=200) (actual time=81.840..131.044 rows=30921 loops=1)

> The plans are clearly different, with the slow one claiming to sort 1.2
> billion rows (but using only 6.5MB of disk space) despite the node below
> it only returning 30921 rows. 1215036357 is almost but not quite
> 30921*40460, but that nested loop is a sibling of the sort, so it
> shouldn't be included, right?

This is coming from the "mark and restore" due to the Merge Join that
executes that sort on its inner side "rewinding" the inner side back
to check the matches for the next outer row. This is happening because
the Merge Join on the slow query only contains 1 of the 3 columns
you're joining on. Notice the Sort's subnode only returned 30921 rows.

> And why are the plans different at all? Computing a few extra values per
> row shouldn't change the cost of the query delivering the rows, IMHO.
> But then the costs are very similar, so maybe it's just some random
> variation.

That's a good question. What happens if you SET
enable_incremental_sort=0; and run the slow query again?

I've not managed to recreate this locally, so far. I tried on 17.5
with hash joins disabled to get the merge join plan but my merge join
includes all 3 clauses rather than your 1 clause. I'm unsure why your
planner doesn't include all 3.

To eliminate 1 moving part, is it still slow if you make the outer
query just SELECT * FROM the subquery rather than have the additional
calculations in the select list?  (the planner won't pullup the
subquery due to the GROUP BY, it'll only be eliminated during
setrefs.c, which is well after the planner will choose the merge join
clauses)

David