Re: PG15 beta1 sort performance regression due to Generation context change - Mailing list pgsql-hackers

From John Naylor
Subject Re: PG15 beta1 sort performance regression due to Generation context change
Date
Msg-id CAFBsxsGx8WF6Svgv8YTGKGVh-J=FPdRUQnPbKO1Nz0j2JV+62Q@mail.gmail.com
Whole thread Raw
In response to Re: PG15 beta1 sort performance regression due to Generation context change  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: PG15 beta1 sort performance regression due to Generation context change
List pgsql-hackers
On Fri, Jun 3, 2022 at 10:14 AM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Wed, 1 Jun 2022 at 03:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Right now my vote would be to leave things as they stand for v15 ---
> > the performance loss that started this thread occurs in a narrow
> > enough set of circumstances that I don't feel too much angst about
> > it being the price of winning in most other circumstances.  We can
> > investigate these options at leisure for v16 or later.
>
> I've been hesitating a little to put my views here as I wanted to see
> what the other views were first.  My thoughts are generally in
> agreement with you, i.e., to do nothing for PG15 about this.  My
> reasoning is:
>
> 1. Most cases are faster as a result of using generation contexts for sorting.
> 2. The slowdown cases seem rare and the speedup cases are much more common.
> 3. There were performance cliffs in PG14 if a column was added to a
> table to make the tuple size cross a power-of-2 boundary which I don't
> recall anyone complaining about. PG15 makes the performance drop more
> gradual as tuple sizes increase. Performance is more predictable as a
> result.
> 4. As I just demonstrated in [1], if anyone is caught by this and has
> a problem, the work_mem size increase required seems very small to get
> performance back to better than in PG14. I found that setting work_mem
> to 64.3MB makes PG15 faster than PG14 for the problem case. If anyone
> happened to hit this case and find the performance regression
> unacceptable then they have a way out... increase work_mem a little.

Since #4 is such a small lift, I'd be comfortable with closing the open item.

-- 
John Naylor
EDB: http://www.enterprisedb.com



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: pg_upgrade test writes to source directory
Next
From: John Naylor
Date:
Subject: Re: some aspects of our qsort might not be ideal