Message-ID: <9acd27e9-3372-4d78-b9bc-73e407b8a007@postgrespro.ru>
Date: Tue, 26 Sep 2023 12:51:16 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC: GROUP BY optimization
Content-Language: en-US
To: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>
Cc: Teodor Sigaev <teodor@sigaev.ru>, 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>
 <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>
 <60610df1-c32f-ebdf-e58c-7a664431f452@enterprisedb.com>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <60610df1-c32f-ebdf-e58c-7a664431f452@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/9acd27e9-3372-4d78-b9bc-73e407b8a007%40postgrespro.ru>
Precedence: bulk

On 20/7/2023 18:46, Tomas Vondra wrote:
> 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.
According to this issue, I see two options:
1. Go through the grouping column list and find the most reliable one. 
If we have a unique column or column with statistics on the number of 
distinct values, which is significantly more than ndistincts for other 
grouping columns, we can place this column as the first in the grouping. 
It should guarantee the reliability of such a decision, isn't it?
2. If we have extended statistics on distinct values and these 
statistics cover some set of first columns in the grouping list, we can 
optimize these positions. It also looks reliable.

Any thoughts?

-- 
regards,
Andrey Lepikhov
Postgres Professional