pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Todd Cook <cookt@blackduck.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Sajith Prabhakar Shetty <ssajith@blackduck.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
Date: Thu, 25 Sep 2025 19:28:34 +0000
Message-ID: <2CB274CF-6282-48F9-988F-59F16E1D0EEC@blackduck.com> (raw)
In-Reply-To: <CAH2-Wz=b5E+Ru4RfE+9QkF56ncH4dWm==SQeOvNGQBDtv_J-Yw@mail.gmail.com>
References: <PH7PR19MB6481278D3A55C8BD04B9B9EDB590A@PH7PR19MB6481.namprd19.prod.outlook.com>
	<2a8243eb-a8e8-4c2d-9c46-7152782d84f8@gmail.com>
	<DM4PR19MB64869049D2AD4882D114BB90B551A@DM4PR19MB6486.namprd19.prod.outlook.com>
	<CAH2-WzmBXPLn=j+nqwGtzoQiMzjY=otTt1irzb39+NkfnHMsnw@mail.gmail.com>
	<DM4PR19MB6486B947E5F0D800127897F5B551A@DM4PR19MB6486.namprd19.prod.outlook.com>
	<CAH2-Wzkduf6pevzTfGpXr29SoXb9jH6AxAkbJM-cJo1EU0M57Q@mail.gmail.com>
	<DM4PR19MB648605FF2806007FC55C0AA5B55AA@DM4PR19MB6486.namprd19.prod.outlook.com>
	<CAH2-WznCkB0V86y+OO0_pp2NfS14-gM2FbMr9uXx9yjdQkpbxQ@mail.gmail.com>
	<DM4PR19MB6486E73FF04BADFD120A0568B525A@DM4PR19MB6486.namprd19.prod.outlook.com>
	<CAH2-WzmNhY2HWNkPku0ugesSp=AxWyy+9tCaq4YqNMSRV8QDqw@mail.gmail.com>
	<2121299.1753993453@sss.pgh.pa.us>
	<CAH2-Wz=O3C=9EWu1azGuHT-3iz1jOon21vG53xFfwJhiaG2CFg@mail.gmail.com>
	<2128451.1753997128@sss.pgh.pa.us>
	<CAH2-WzmOOBV7wtu3K6R_zO105Qi_BE-2Ki5T9KLsHETP=GQwAA@mail.gmail.com>
	<2532876.1754096999@sss.pgh.pa.us>
	<2538941.1754100308@sss.pgh.pa.us>
	<CAH2-Wzndh_wt-vNxwzUC09dCGMBVr00Z34sf5J8HRNu09gzpSA@mail.gmail.com>
	<AD7A39D2-1A49-4A73-A28B-1720134E013D@blackduck.com>
	<CAHyXU0xgOE99AuTjSAo+SNK_00P=F8JcbdoH3+OcV+zs1r00vA@mail.gmail.com>
	<53859E8F-67E7-4A25-AEE1-C7E472262F47@blackduck.com>
	<DEE6B3DC-B626-4C9F-A7E1-A06D85950ABF@blackduck.com>
	<CAH2-Wz=b5E+Ru4RfE+9QkF56ncH4dWm==SQeOvNGQBDtv_J-Yw@mail.gmail.com>

On 9/25/25, 2:46 PM, "Peter Geoghegan" <pg@bowt.ie <mailto:pg@bowt.ie>> wrote:
> What does EXPLAIN ANALYZE actually show you on 18, compared to 16,
> with the same real world (non-minimal) test case? Are the joins and
> scan nodes all the same as before (i.e. is the Postgres 18 plan
> *identical to the "bad" Postgres 17 plan)?
>
> Do you see any "Heap Fetches:", particularly with the problematic
> index-only scan? Could it just be that you made sure to run VACUUM
> ahead of the test this time, allowing the index-only scan seen on
> Postgres 17 and 18 to avoid heap accesses?
>
> Recall that the 15 and 16 plan had a plain index scan on another
> index, and that the 17 plan you showed a few weeks back had "Heap
> Fetches: 598,916" (meaning that the index-only scan was completely
> ineffective at avoiding heap accesses). If you're now able to get the
> most out of index-only access, it could be enough to flip things in
> favor of the new plan -- in spite of the fact that there is definitely
> still a regression tied to needlessly sorting the scan's SAOP array
> many times.

The tests I reported on are application-level load tests that last about
10 hours and generate ~4 million queries, so I don't have any of that
per-query info.  We generate a lot of queries with "IN (constant_list)"
expressions, so that seemed like a logical explanation, but maybe
something else is going on too?  Nothing really stands out from
crawling through pg_stat_statements, so maybe it's a small effect that
is multiplied by repetition?

FWIW, except for large multi-join queries with "IN (constant_list)"
expressions, nearly every individual query I run is as fast or faster in
17 than in 16.  The ones that are slower are ones that were previously
munged around to get a specific plan; those that I've unmunged have
so far been much faster on 17.

It just occurred to me while typing this that I should go count joins to
see if we're exceeding join_collapse_limit.  Could something have
changed in 17 that would affect how such queries are planned?

-- todd



view thread (54+ messages)  latest in thread

Message-ID: <2CB274CF-6282-48F9-988F-59F16E1D0EEC@blackduck.com>
Permalink:  ../2CB274CF-6282-48F9-988F-59F16E1D0EEC@blackduck.com/
Also on:    postgresql.org/message-id/2CB274CF-6282-48F9-988F-59F16E1D0EEC@blackduck.com

 · 

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: pgsql-bugs@postgresql.org
  Cc: cookt@blackduck.com, pg@bowt.ie, mmoncure@gmail.com, tgl@sss.pgh.pa.us, ssajith@blackduck.com, lepihov@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
  In-Reply-To: <2CB274CF-6282-48F9-988F-59F16E1D0EEC@blackduck.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox