public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Tom Lane <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: Pavel Borisov <[email protected]>
Cc: vignesh C <[email protected]>
Cc: PostgreSQL Developers <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Teodor Sigaev <[email protected]>
Cc: David Rowley <[email protected]>
Cc: a.rybakina <[email protected]>
Subject: Re: POC: GROUP BY optimization
Date: Mon, 22 Apr 2024 10:51:02 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CA+q6zcVRrd-z4YZ4M43ccst7aGL9==w5r1fionRWhP9ot6mybQ@mail.gmail.com>
<CAMbWs4-7oUGax4qWUwdiuyPKYKDO-DtKrcPedmjbPZyC-uYYrg@mail.gmail.com>
<CAPpHfdsfHn89cAFEa6-6Xm1sB_xf8Rf2WNab6DQzb8Rvs0yD8g@mail.gmail.com>
<CAPpHfdtNnX9PYmgEsh9DXYws2Px6UJhH5kYUxsg3hu-zJTYuWw@mail.gmail.com>
<[email protected]>
<CAMbWs4_NF0stupM8guasC_yCJxdTvRqm3a+JLarjvAxcH314TQ@mail.gmail.com>
<CAMbWs4-NKLa+Ss+X=WR6h0x=T07YBJoAz70ZGHzc-2zcHUHb0A@mail.gmail.com>
<[email protected]>
<CAMbWs49oMEFUEy6RRaenx5ak4AuZzuDEDzi0otEBYwJzBBYz7Q@mail.gmail.com>
<CAPpHfdtMD=RhmB80DMRejs8s72Nr_y0OprUD9YiJFGgPVMdwXw@mail.gmail.com>
<CAMbWs48MSejYST6pX8q-rEJMmRwo+fycDpc5vkkDusc=KF5y7Q@mail.gmail.com>
<[email protected]>
<CAMbWs4-wAwJgxAnUNjCz8Qgzgjwn9t_NN9=i46hVPdaOT6Jmmg@mail.gmail.com>
<d0265! [email protected]>
<[email protected]>
On 4/12/24 06:44, Tom Lane wrote:
> If this patch were producing better results I'd be more excited
> about putting more work into it. But on the basis of what I'm
> seeing right now, I think maybe we ought to give up on it.
Let me show current cases where users have a profit with this tiny
improvement (see queries and execution results in query.sql):
1. 'Not optimised query text' — when we didn't consider group-by
ordering during database development.
2. 'Accidental pathkeys' - we didn't see any explicit orderings, but
accidentally, the planner used merge join that caused some orderings and
we can utilise it.
3. 'Uncertain scan path' — We have options regarding which index to use,
and because of that, we can't predict the optimal group-by ordering
before the start of query planning.
4. 'HashAgg V/S GroupAgg' — sometimes, the GroupAgg strategy outperforms
HashAgg just because we don't need any ordering procedure at all.
And the last thing here — this code introduces the basics needed to add
more sophisticated strategies, like ordering according to uniqueness or
preferring to set constant-width columns first in grouping.
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:
[application/sql] query.sql (10.4K, ../[email protected]/2-query.sql)
download
view thread (54+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: POC: GROUP BY optimization
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox