Re: PG15 beta1 sort performance regression due to Generation context change

On Fri, Jun 3, 2022 at 10:14 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Wed, 1 Jun 2022 at 03:09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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