Postgres Pro
Downloads
Home   >   mailing lists

Thread: Optimization of unnecessary GROUP BY columns

Optimization of unnecessary GROUP BY columns

From
Kevin Murphy
Date:
When columns are required in a GROUP BY clause even though some of them
are fully dependent on others, is there any plan for making it possible
to do the GROUP BY only on the necessary columns?  The 8.X documentation
made me curious:

Section 7.2.3 in the 8.X documentation ("The GROUP BY and HAVING Clauses"):

"SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
FROM products p LEFT JOIN sales s USING (product_id)
GROUP BY product_id, p.name, p.price;
...
Depending on how exactly the products table is set up, name and price
may be fully dependent on the product ID, so the additional groupings
could theoretically be unnecessary, but this is not implemented yet."

-Kevin Murphy

Re: Optimization of unnecessary GROUP BY columns

From
Martijn van Oosterhout
Date:
On Tue, Dec 26, 2006 at 12:08:04PM -0500, Kevin Murphy wrote:
> When columns are required in a GROUP BY clause even though some of them
> are fully dependent on others, is there any plan for making it possible
> to do the GROUP BY only on the necessary columns?  The 8.X documentation
> made me curious:

Recent SQL standards require it, so it's likely to be implemented at
some point.

Havn't seen any patches in that direction though...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment

Re: Optimization of unnecessary GROUP BY columns

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Dec 26, 2006 at 12:08:04PM -0500, Kevin Murphy wrote:
>> When columns are required in a GROUP BY clause even though some of them
>> are fully dependent on others, is there any plan for making it possible
>> to do the GROUP BY only on the necessary columns?

> Recent SQL standards require it, so it's likely to be implemented at
> some point.

This optimization is in the nature of depending on a constraint (ie, a
PRIMARY KEY) for plan correctness, so I'm hesitant to think about doing
it before we have plan invalidation implemented.  Actually it's even
worse than that: it's in the nature of depending on a constraint for
*query* correctness.  If you ALTER DROP PRIMARY KEY then your query is
now illegal, and we really oughta notice that ...

            regards, tom lane