Functional dependencies and GROUP BY

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Functional dependencies and GROUP BY
Date: 2010-06-07 18:33:48
Message-ID: 1275935628.11078.12.camel@vanquo.pezone.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have developed a patch that partially implements the "functional
dependency" feature that allows some columns to be omitted from the
GROUP BY clause if it can be shown that the columns are functionally
dependent on the columns in the group by clause and therefore guaranteed
to be unique per group. The full functional dependency deduction rules
are pretty big and arcane, so I concentrated on getting a useful subset
working. In particular:

When grouping by primary key, the other columns can be omitted, e.g.,

CREATE TABLE tab1 (a int PRIMARY KEY, b int);

SELECT a, b FROM tab1 GROUP BY a;

This is frequently requested by MySQL converts (and possibly others).

Also, when a column is compared with a constant, it can appear
ungrouped:

SELECT x, y FROM tab2 WHERE y = 5 GROUP BY x;

For lack of a better idea, I have made it so that merge-joinable
operators qualify as equality operators. Better ideas welcome.

Other rules could be added over time (but I'm current not planning to
work on that myself).

At this point, this patch could use some review and testing with unusual
queries that break my implementation. ;-)

Attachment Content-Type Size
functional-deps.patch text/x-patch 26.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Smith 2010-06-07 18:39:03 Re: [HACKERS] Invalid YAML output from EXPLAIN
Previous Message Josh Berkus 2010-06-07 18:26:24 Re: [BUGS] Invalid YAML output from EXPLAIN