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

help / color / mirror / Atom feed
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: Mon, 04 Aug 2025 18:25:56 -0400
Message-ID: <952036.1754346356@sss.pgh.pa.us> (raw)
In-Reply-To: <CAH2-Wzndh_wt-vNxwzUC09dCGMBVr00Z34sf5J8HRNu09gzpSA@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>
	<253! 8941.1754100308@sss.pgh.pa.us>
	<CAH2-Wzndh_wt-vNxwzUC09dCGMBVr00Z34sf5J8HRNu09gzpSA@mail.gmail.com>

Peter Geoghegan <pg@bowt.ie> writes:
> Attached is what I came up with. It makes the problematic test case
> query execute a little under 10x faster, compared to unpatched
> master -- see my numbers from Friday (the numbers for the less
> ambitious approach/the approach taken by this patch). This is still a
> WIP.

I took a very quick look at this, and there are a couple of things
I don't like:

* I don't love the fact that _bt_presort_const_array shares no code
with _bt_sort_array_elements.  Maybe there's nothing to be done
about it given that they're working on different representations.
But the comments could at least point out that the two functions
had better give equivalent results.

* I really don't like the fact that SK_PRESORTED gets set so far
away from the code that's actually doing the work to make that
valid (not to mention far from the code that consults that flag).
Even if it's bug-free today, that seems like an invitation to
maintenance problems.  Can we factor that differently?

> I'm unsure of how to assess my approach from a modularity perspective.
> The patch simply assumes that any amsearcharray index AM is either
> nbtree, or some other amsearcharray index AM that is okay with having
> its array deduplicated based on nbtree rules/a B-Tree ORDER proc.

Well, it's worse than that: it's assuming that support procedure 1
is in fact a btree ordering proc in any opclass of an amsearcharray
AM.  I don't think that's too safe.  We have our hands on enough
info about the index that we could skip trying to do the sort if
it's not a btree, and I think we should do so, at least for today.

> This is a little bit like
> how we deal with RowCompareExpr within nodeIndexScan.c -- there are
> similar hard-coded BTORDER_PROC lookups there.

One big difference is that we *know* that the comparisons in a
RowCompareExpr are btree operators, because the parser constructed it
that way.  We cannot make such assumptions about arbitrary
ScalarArrayOps, even if they are indexquals.

			regards, tom lane





view thread (54+ messages)  latest in thread

Message-ID: <952036.1754346356@sss.pgh.pa.us>
Permalink:  ../952036.1754346356@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/952036.1754346356@sss.pgh.pa.us

 · 

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: 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: <952036.1754346356@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