From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Sajith Prabhakar Shetty <ssajith@blackduck.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: Todd Cook <cookt@blackduck.com>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
Date: Thu, 31 Jul 2025 16:24:13 -0400
Message-ID: <2121299.1753993453@sss.pgh.pa.us> (raw)
In-Reply-To: <CAH2-WzmNhY2HWNkPku0ugesSp=AxWyy+9tCaq4YqNMSRV8QDqw@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>
Peter Geoghegan <pg@bowt.ie> writes:
> Perhaps Tom can weigh-in here. I removed code that generated these
> alternative index paths from the planner because its original
> justification (see bugfix commit a4523c5a, a follow-up to bugfix
> commit 807a40c5) no longer applied. Perhaps this should be revisited
> now, or perhaps the issue should be ameliorated on the nbtree side. Or
> maybe we should just do nothing -- the issue can be worked around in
> the application itself.
Well, maybe it was a mistake to no longer consider such plans, but
this example doesn't prove it. Quoting the submitted readme file,
we selected this plan in v15:
-> Index Scan using zsf_pkey on zsf sf (cost=1.49..1.51 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=47089)
Index Cond: (id = sdo.sfi)
Filter: (cid = ANY ('{...}'::bigint[]))
versus this in v17:
-> Index Only Scan using zsf_id_fpi_cid_key on zsf sf (cost=0.29..0.31 rows=1 width=24) (actual time=0.023..0.023 rows=1 loops=47089)
Index Cond: ((id = sdo.sfi) AND (cid = ANY ('{...}'::bigint[])))
IIUC you're saying the planner no longer even considers the first
case --- but if it did, it'd surely still pick the second one,
because the estimated cost is a lot less. So undoing that choice
would not help the blackduck folks.
I do think we should do something about this, though. My suggestion
is that we should always presort in the planner if the SAOP argument
is a Const array, and then skip the run-time sort if the executor
sees the argument is a Const. Yes, there will be cases where the
plan-time sort is wasted effort, but not too darn many.
An alternative thought is that maybe the run-time sort is expensive
enough that the planner ought to account for it in its estimates.
However, that's a bit of a research project, and I don't think we'd
dare shove it into v17 at this point even if it turns out to fix
this particular case. But a pre-sort seems like a pretty safe change.
regards, tom lane
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: tgl@sss.pgh.pa.us, pg@bowt.ie, ssajith@blackduck.com, lepihov@gmail.com, pgsql-bugs@lists.postgresql.org, cookt@blackduck.com
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
In-Reply-To: <2121299.1753993453@sss.pgh.pa.us>
* 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