Message-ID: <aada8f97-924e-5661-aead-257aa346899c@enterprisedb.com>
Date: Fri, 15 Jul 2022 21:46:51 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.0
Subject: Re: POC: GROUP BY optimization
Content-Language: en-US
To: David Rowley <dgrowleyml@gmail.com>
Cc: Zhihong Yu <zyu@yugabyte.com>,
 "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>,
 Teodor Sigaev <teodor@sigaev.ru>, Tomas Vondra
 <tomas.vondra@2ndquadrant.com>,
 PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
References: 
 <CA+q6zcVRrd-z4YZ4M43ccst7aGL9==w5r1fionRWhP9ot6mybQ@mail.gmail.com>
 <CALtqXTe4rKXHXxNNGVy0wHVwGYYJpy2QNPTN29qq+ErBJJakrA@mail.gmail.com>
 <b1d468ba-2961-044c-deab-817b9a900e9c@enterprisedb.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>
 <CAApHDvpVFgWzXdtUQkjyOPhNrNvumRi_=ftgS79KeAZ92tnHKQ@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAApHDvpVFgWzXdtUQkjyOPhNrNvumRi_=ftgS79KeAZ92tnHKQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/aada8f97-924e-5661-aead-257aa346899c%40enterprisedb.com>
Precedence: bulk


On 7/15/22 07:18, David Rowley wrote:
> On Thu, 31 Mar 2022 at 12:19, Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>> Pushed, after going through the patch once more, running check-world
>> under valgrind, and updating the commit message.
> 
> I'm still working in this area and I noticed that db0d67db2 updated
> some regression tests in partition_aggregate.out without any care as
> to what the test was testing.
> 
> The comment above the test reads:
> 
> -- Without ORDER BY clause, to test Gather at top-most path
> 
> and you've changed the expected plan from being a parallel plan with a
> Gather to being a serial plan. So it looks like the test might have
> become useless.
> 

I agree this is a mistake in db0d67db2 that makes the test useless.

> I see that the original plan appears to come back with some
> adjustments to parallel_setup_cost and parallel_tuple_cost. It seems a
> bit strange to me that the changes with this patch would cause a
> change of plan for this. There is only 1 GROUP BY column in the query
> in question. There's no rearrangement to do with a single column GROUP
> BY.
> 

It might seem a bit strange, but the patch tweaked the costing a bit, so
it's not entirely unexpected. I'd bet the plan cost changed just a teeny
bit, but enough to change the cheapest plan. The costing changed for all
group counts, including a single group.

regards

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