From: Greg Stark <gsstark@mit.edu>
To: Anthony Molinaro <amolinaro@wgen.net>
Cc: gsstark@mit.edu
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg, mysql comparison with "group by" clause
Date: 13 Oct 2005 00:24:55 -0400
Message-ID: <87oe5uyrfs.fsf@stark.xeocode.com> (raw)
In-Reply-To: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182614@wgexch01.wgenhq.net>
References: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182614@wgexch01.wgenhq.net>
"Anthony Molinaro" <amolinaro@wgen.net> writes:
> Greg,
> You'll have to pardon me...
>
> I saw this comment:
>
> "I don't see why you think people stumble on this by accident.
> I think it's actually an extremely common need."
>
> Which, if referring to the ability to have items in the select that do not
> need to be included in the group, (excluding constants and the like) is just
> silly.
Well the "constants and the like" are precisely the point. There are plenty of
cases where adding the column to the GROUP BY is unnecessary and since
Postgres makes no attempt to prune them out, inefficient. And constants aren't
the only such case. The most common case is columns that are coming from a
table where the primary key is already included in the GROUP BY list.
In the case of columns coming from a table where the primary key is already in
the GROUP BY list it's possible for the database to deduce that it's
unnecessary to group on that column.
But it's also possible to have cases where the programmer has out of band
knowledge that it's unnecessary but the database doesn't have that knowledge.
The most obvious case that comes to mind is a denormalized data model that
includes a redundant column.
select dept_id, dept_name, count(*) from employee_list
For example if dept_name is guaranteed to be the same for all records with the
same dept_id. Of course that's generally considered poor design but it doesn't
mean there aren't thousands of databases out there with data models like that.
--
greg
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: pgsql-sql@postgresql.org
Cc: gsstark@mit.edu, amolinaro@wgen.net, tgl@sss.pgh.pa.us, smarlowe@g2switchworks.com, sszabo@megazone.bigpanda.com, rschumeyer@ieee.org
Subject: Re: pg, mysql comparison with "group by" clause
In-Reply-To: <87oe5uyrfs.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox