Message-ID: <60610df1-c32f-ebdf-e58c-7a664431f452@enterprisedb.com>
Date: Thu, 20 Jul 2023 13:46:19 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.12.0
Subject: Re: POC: GROUP BY optimization
Content-Language: en-US
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>,
 PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>
Cc: Zhihong Yu <zyu@yugabyte.com>, Teodor Sigaev <teodor@sigaev.ru>,
 Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 David Rowley <dgrowleyml@gmail.com>, "a.rybakina"
 <a.rybakina@postgrespro.ru>,
 =?UTF-8?B?0JHQtdC70Y/Qu9C+0LIg0JTQsNC80LjRgCDQndCw0LjQu9C10LLQuNGH?=
 <d.belyalov@postgrespro.ru>
References: 
 <CA+q6zcVRrd-z4YZ4M43ccst7aGL9==w5r1fionRWhP9ot6mybQ@mail.gmail.com>
 <ba2eccf2-d623-c12c-f7c2-a58ac4917c83@enterprisedb.com>
 <006667af-fc50-0627-4be7-5d9cf665219f@enterprisedb.com>
 <dc933c66-41b9-8907-327e-f6361a690654@postgrespro.ru>
 <4ee5b15c-1ce0-59c7-8d4a-a1fd68ee0d12@postgrespro.ru>
 <721b3ff9-f214-f5d4-86c7-bae515bbec18@enterprisedb.com>
 <7bea462e-8c3e-0f8c-c7d5-f4daa12f3baf@postgrespro.ru>
 <9af89543-3d17-4c98-59de-1daa5bceeb06@enterprisedb.com>
 <9408e450-60c6-6fbc-d5c4-467bb0abfe67@postgrespro.ru>
 <44472925-f6c6-e8de-6f72-0451458532c4@enterprisedb.com>
 <CALNJ-vQp3WNV-kKuGsjSOhjRfdECA1Dbxa-vjAgOmQ4f4Qaw-Q@mail.gmail.com>
 <7a80a207-5e8e-b80c-476c-2d290b0526e9@enterprisedb.com>
 <65941e87-86c1-c09c-8ad5-8e102aebe8ee@enterprisedb.com>
 <CAApHDvq65=9Ro+hLX1i9ugWEiNDvHrBibAO7ARcTnf38_JE+UQ@mail.gmail.com>
 <CAApHDvoEF0GsqmBuCPXb6f12jPwz8EeLG-oYsOSc5zrJtKZHOg@mail.gmail.com>
 <7256159a-bb83-7ef3-c9c9-b2188a29aaf8@postgrespro.ru>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <7256159a-bb83-7ef3-c9c9-b2188a29aaf8@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/60610df1-c32f-ebdf-e58c-7a664431f452%40enterprisedb.com>
Precedence: bulk

On 7/20/23 08:37, Andrey Lepikhov wrote:
> On 3/10/2022 21:56, Tom Lane wrote:
>> Revert "Optimize order of GROUP BY keys".
>>
>> This reverts commit db0d67db2401eb6238ccc04c6407a4fd4f985832 and
>> several follow-on fixes.
>> ...
>> Since we're hard up against the release deadline for v15, let's
>> revert these changes for now.  We can always try again later.
> 
> It may be time to restart the project. As a first step, I rebased the
> patch on the current master. It wasn't trivial because of some latest
> optimizations (a29eab, 1349d27 and 8d83a5d).
> Now, Let's repeat the review and rewrite the current path according to
> the reasons uttered in the revert commit.

I think the fundamental task is to make the costing more reliable, and
the commit message 443df6e2db points out a couple challenges in this
area. Not sure how feasible it is to address enough of them ...

1) procost = 1.0 - I guess we could make this more realistic by doing
some microbenchmarks and tuning the costs for the most expensive cases.

2) estimating quicksort comparisons - This relies on ndistinct
estimates, and I'm not sure how much more reliable we can make those.
Probably not much :-( Not sure what to do about this, the only thing I
can think of is to track "reliability" of the estimates and only do the
reordering if we have high confidence in the estimates. That means we'll
miss some optimization opportunities, but it should limit the risk.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company