Date: Wed, 23 Jul 2025 10:24:44 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Wrapping a select in another select makes it slower
Message-ID: <20250723082444.ksns3mhltxarwqfd@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <20250722151735.toridqqnb3krhimp@hjp.at>
 <CAApHDvrDM=QYt2m5oWpRME3Hdy6S=sQVBpxOTqOWvDeUOnR1EQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="qf2xaaizbgkdtyvh"
Content-Disposition: inline
In-Reply-To: <CAApHDvrDM=QYt2m5oWpRME3Hdy6S=sQVBpxOTqOWvDeUOnR1EQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20250723082444.ksns3mhltxarwqfd%40hjp.at>
Precedence: bulk


--qf2xaaizbgkdtyvh
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2025-07-23 10:08:31 +1200, David Rowley wrote:
> 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).
>=20
>=20
> >               ->  Merge Left Join  (cost=3D4613.25..7180.30 rows=3D8357=
 width=3D136) (actual time=3D222.037..292242.701 rows=3D40460 loops=3D1)
> >                     Merge Cond: (ns.nspname =3D s.schemaname)
> >                     Join Filter: ((s.tablename =3D tbl.relname) AND (s.=
attname =3D att.attname))
> >                     Rows Removed by Join Filter: 1215045276
>=20
> >                     ->  Sort  (cost=3D4476.84..4477.18 rows=3D139 width=
=3D200) (actual time=3D156.864..178628.030 rows=3D1215036357 loops=3D1)
> >                           Sort Key: s.schemaname
> >                           Sort Method: external sort  Disk: 6480kB
> >                           ->  Subquery Scan on s  (cost=3D2189.59..4471=
=2E89 rows=3D139 width=3D200) (actual time=3D81.840..131.044 rows=3D30921 l=
oops=3D1)
>=20
> > 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?
>=20
> 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.
>=20
> > 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.
>=20
> That's a good question. What happens if you SET
> enable_incremental_sort=3D0; and run the slow query again?

This fixes the problem.


> I've not managed to recreate this locally, so far.

I was afraid of that. I mentioned that it happened on one database but
didn't emphasize that it's really only one of several PostgreSQL 17.x
databases we have. There is even another database in the same cluster
where it doesn't happen- So it probably takes a particularly unlucky
combination of statistics to trigger this problem.

(FWIW, the database contains a limesurvey instance: About 500 smallish
tables. So nothing crazy)

> 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.

Yes, that's strange.


> 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?

Yes, that's still slow. So the additional columns were a red herring.
It's really just the additional SELECT.

        hjp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--qf2xaaizbgkdtyvh
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmiAnEMACgkQ8g5IURL+
KF2YwhAAqZPC58lh+J6ZQXcPCeGiLivUUMaRuFXS7Gf6iUH6opvfvg158R2j8Ckc
Li0k6BkDNNncvUF7bujVoeisiqmrN1V32Rw4QMp8zDtoVhIS9kw3qHceinD2TuO4
Oyqwp/pC7j+rqucZKE3nkzoLYweaPUhzN80MPrM1POwrmrVIa4hk+qtw02ZrHpIR
v1I1R+Z9pH5EuXx01qik8kPM2RkVeX0EpHm6qf+CWTDHhZiQ0/zE7RLtscrtHbRk
InwToc7g1he/RYZhNZaevhBCttGxSJxK7picUtyIa85JzQ5ZzeBqiufcE9W9uICJ
OeuBu5+LhSqoqgasLq2yEObT4ioXICP3mCKDtQrxENSYbyS3QNCK36t4chnobkWt
a3lfgrAHjAgzLxamqcxne2GJKcuBFo1BQgiJJ2+HArVdC17gzSuRxDz8KjzUzp6S
2KcD1lt+u2SYREnre88+8eot7LTWKTnbzWXiS+XQXqo6oxVZ3DNqnsyocs2Xdbe/
UEaK4+qtiaOCWwdPQl2YXLcACjSXTudu1eLCJP5uFbl5//yUOSmzn+O8Z4qDqUj4
sMLfDFVAxWnEWsEhz70Zrr+zQ2l09yw1ye9iLvn6WMoCvwXMvJMc9v4MSkDnTzcz
0gmdz4V11FzsGjyH6nMyXILihUW0zo3Hdw7eOEbqQBOWWFo0RM0=
=PIEX
-----END PGP SIGNATURE-----

--qf2xaaizbgkdtyvh--