Re: Removing Functionally Dependent GROUP BY Columns

On 1 December 2015 at 17:09, Marko Tiikkaja <marko(at)joh(dot)to> wrote:

> On 2015-12-01 05:00, David Rowley wrote:
>
>> We already allow a SELECT's target list to contain non-aggregated columns
>> in a GROUP BY query in cases where the non-aggregated column is
>> functionally dependent on the GROUP BY clause.
>>
>> For example a query such as;
>>
>> SELECT p.product_id,p.description, SUM(s.quantity)
>> FROM product p
>> INNER JOIN sale s ON p.product_id = s.product_id
>> GROUP BY p.product_id;
>>
>> is perfectly fine in PostgreSQL, as p.description is functionally
>> dependent
>> on p.product_id (assuming product_id is the PRIMARY KEY of product).
>>
>
> This has come up before (on other forums, at least), and my main concern
> has been that unlike the case where we go from throwing an error to
> allowing a query, this has a chance to make the planning of currently legal
> queries slower. Have you tried to measure the impact of this on queries
> where there's no runtime gains to be had?

I've performed a series of benchmarks on the following queries:

Test1: explain select id1,id2 from t1 group by id1,id2;
Test2: explain select id from t2 group by id;
Test3: explain select t1.id1,t1.id2 from t2 inner join t1 on t1.id1=t2.id
group by t1.id1,t1.id2;

I ran each of these with pgbench for 60 seconds, 3 runs per query. In each
case below I've converted the TPS into seconds using the average TPS over
the 3 runs.

In summary:

Test1 is the worst case test. It's a very simple query so planning overhead
of join searching is non-existent. The fact that there's 2 columns in the
GROUP BY means that the fast path cannot be used. I added this as if
there's only 1 column in the GROUP BY then there's no point in searching
for something to remove.

Average (Sec)
Master 0.0001043117
Patched 0.0001118961
Performance 93.22%
Microseconds of planning overhead 7.5844326722

Test2 is a simple query with a GROUP BY which can fast path due to there
being only 1 GROUP BY column.

Average (Sec)
Master 0.000099374448
Patched 0.000099670124
Performance 99.70%
Microseconds of planning overhead 0.2956763193

Test3 is a slightly more complex and is aimed to show that the percentage
of planning overhead is smaller when joins exist and overall planning cost
becomes higher

Average (Sec)
Master 0.0001797165
Patched 0.0001798406
Performance 99.93%
Microseconds of planning overhead 0.1240776236

Test3 results seem a bit strange, I would have expected more of a slowdown.
I ran the test again to make sure, and it came back with the same results
the 2nd time.

I've attached the spreadsheet that used to collect the results, and also
the raw pgbench output.

It seems that the worst case test adds about 7.6 microseconds onto planning
time. To get this worse case result I had to add two GROUP BY columns, as
having only 1 triggers a fast path as the code knows it can't remove any
columns, since there's only 1. A similar fast path also exists which will
only lookup the PRIMARY KEY details if there's more than 1 column per
relation in the GROUP BY, so for example GROUP BY rel1.col1, rel2.col1
won't lookup any PRIMARY KEY constraint.

Given that the extra code really only does anything if the GROUP BY has 2
or more expressions, are you worried that this will affect too many short
and fast to execute queries negatively?

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services