Message-ID: <3e755fd2-cf59-49d2-ae95-38c78daeba85@postgrespro.ru>
Date: Thu, 30 May 2024 11:22:46 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC: GROUP BY optimization
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Richard Guo <guofenglinux@gmail.com>,
 Pavel Borisov <pashkin.elfe@gmail.com>, vignesh C <vignesh21@gmail.com>,
 PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>,
 Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Teodor Sigaev <teodor@sigaev.ru>, David Rowley <dgrowleyml@gmail.com>,
 "a.rybakina" <a.rybakina@postgrespro.ru>
References: <CA+q6zcVRrd-z4YZ4M43ccst7aGL9==w5r1fionRWhP9ot6mybQ@mail.gmail.com>
 <CAMbWs4-NKLa+Ss+X=WR6h0x=T07YBJoAz70ZGHzc-2zcHUHb0A@mail.gmail.com>
 <f5dae142-d351-4991-a2aa-92d12dcea004@postgrespro.ru>
 <CAMbWs49oMEFUEy6RRaenx5ak4AuZzuDEDzi0otEBYwJzBBYz7Q@mail.gmail.com>
 <CAPpHfdtMD=RhmB80DMRejs8s72Nr_y0OprUD9YiJFGgPVMdwXw@mail.gmail.com>
 <CAMbWs48MSejYST6pX8q-rEJMmRwo+fycDpc5vkkDusc=KF5y7Q@mail.gmail.com>
 <890ed877-e2c0-448a-93b8-b07ccf3a2b37@postgrespro.ru>
 <CAMbWs4-wAwJgxAnUNjCz8Qgzgjwn9t_NN9=i46hVPdaOT6Jmmg@mail.gmail.com>
 <266850.1712879082@sss.pgh.pa.us>
 <db0fc3a4-966c-4cec-a136-94024d39212d@postgrespro.ru>
 <CAPpHfdvb3hHyNLKBeqpFZtAvrSi0c=wJYxMNiBLGyoDD968Ocg@mail.gmail.com>
 <CAPpHfdvyWLMGwvxaf=7KAp-z-4mxbSH8ti2f6mNOQv5metZFzg@mail.gmail.com>
 <CAPpHfdvbxK3nszdk6YXWpV7p+P+pGOQhpKTxJ3xME3uD+qZmXg@mail.gmail.com>
 <d5a363ea-c606-4623-9872-963452c8167a@postgrespro.ru>
 <CAPpHfds4cR5DiqnPQxLEPFczFxHDgvhPG=eNGjW2Oh7T-RUkgg@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <CAPpHfds4cR5DiqnPQxLEPFczFxHDgvhPG=eNGjW2Oh7T-RUkgg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/3e755fd2-cf59-49d2-ae95-38c78daeba85%40postgrespro.ru>
Precedence: bulk

On 5/29/24 19:53, Alexander Korotkov wrote:
> Hi, Andrei!
> 
> Thank you for your feedback.
> 
> On Wed, May 29, 2024 at 11:08 AM Andrei Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> On 5/27/24 19:41, Alexander Korotkov wrote:
>>> Any thoughts?
>> About 0001:
>> Having overviewed it, I don't see any issues (but I'm the author),
>> except grammatical ones - but I'm not a native to judge it.
>> Also, the sentence 'turning GROUP BY clauses  into pathkeys' is unclear
>> to me. It may be better to write something like:  'building pathkeys by
>> the list of grouping clauses'.
> 
> OK, thank you.  I'll run once again for the grammar issues.
> 
>> 0002:
>> The part under USE_ASSERT_CHECKING looks good to me. But the code in
>> group_keys_reorder_by_pathkeys looks suspicious: of course, we do some
>> doubtful work without any possible way to reproduce, but if we envision
>> some duplicated elements in the group_clauses, we should avoid usage of
>> the list_concat_unique_ptr.
> 
> As I understand Tom, there is a risk that clauses list may contain
> multiple instances of equivalent SortGroupClause, not duplicate
> pointers.
Maybe. I just implemented the worst-case scenario with the intention of 
maximum safety. So, it's up to you.
> 
>> What's more, why do you not exit from
>> foreach_ptr immediately after SortGroupClause has been found? I think
>> the new_group_clauses should be consistent with the new_group_pathkeys.
> 
> I wanted this to be consistent with preprocess_groupclause(), where
> duplicate SortGroupClause'es are grouped together.  Otherwise, we
> could just delete redundant SortGroupClause'es.
Hm, preprocess_groupclause is called before the standard_qp_callback 
forms the 'canonical form' of group_pathkeys and such behaviour needed. 
But the code that chooses the reordering strategy uses already processed 
grouping clauses, where we don't have duplicates in the first 
num_groupby_pathkeys elements, do we?
>> 0004:
>> I was also thinking about reintroducing the preprocess_groupclause
>> because with the re-arrangement of GROUP-BY clauses according to
>> incoming pathkeys, it doesn't make sense to have a user-defined order—at
>> least while cost_sort doesn't differ costs for alternative column orderings.
>> So, I'm okay with the code. But why don't you use the same approach with
>> foreach_ptr as before?
> 
> I restored the function as it was before 0452b461bc with minimal edits
> to support the incremental sort.  I think it would be more valuable to
> keep the difference with pg16 code small rather than refactor to
> simplify existing code.
Got it

-- 
regards,
Andrei Lepikhov
Postgres Professional