From: Michael Glaesemann <grzm@seespotcode.net>
To: tyrrill_ed@emc.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Join question
Date: Tue, 21 Aug 2007 16:16:35 -0500
Message-ID: <3D88F0CD-4914-460C-96D8-A7E06CC29C83@seespotcode.net> (raw)
In-Reply-To: <5C7C0B0734F87445AFC8B63EDCC4901E015ED7EE@CORPUSMX60C.corp.emc.com>
References: <5C7C0B0734F87445AFC8B63EDCC4901E015ED7EE@CORPUSMX60C.corp.emc.com>
On Aug 21, 2007, at 12:48 , tyrrill_ed@emc.com wrote:
> SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id =
> c.b_id GROUP by a.x;
Shouldn't affect performance, but another way to write this which you
may find more readable is to list your join conditions with the joins
rather than grouping them all in the WHERE clause:
SELECT a.x, max(b.x)
FROM a
JOIN c USING (a_id)
JOIN b USING (b_id)
GROUP BY a.x;
You can also write this using NATURAL JOIN which joins on like-named
columns.
SELECT a.x, max(b.x)
FROM a
NATURAL JOIN c
JOIN b USING (b_id)
GROUP BY a.x;
You can't use NATURAL JOIN to join b because you've got columns named
x in both a and b that you're *not* joining on.
Another nice thing about the USING and NATURAL JOIN syntax is that
the result includes only one column for the joined columns, rather
than two. In this case, there would be only one b_id and one a_id
column in the result set. Using ON or putting the join condition in
the WHERE clause puts two b_id and two a_id columns in the result set.
> Does anyone know a
> way I could restructure this query to get only one b for each a in a
> faster way?
You might want to ask on the pgsql-performance list as well, as
people there are generally interested in improving query performance.
One thing they'll ask you for is the output of EXPLAIN ANALYZE for
your query.
Here's an alternative, but I don't know how it'd compare in terms of
performance:
EXPLAIN ANALYZE
SELECT DISTINCT ON (a.x)
a.x, b.x
FROM a
NATURAL JOIN c
JOIN b USING (b_id);
Give that a shot. (DISTINCT ON is a non-standard PostgreSQL
extension, if non-portable syntax is something you're looking to avoid.)
EXPLAIN ANALYZE shows you how the planner decided to proceed with the
query, which can be useful when comparing alternatives, so you can
compare using DISTINCT ON with your own query using max.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
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: grzm@seespotcode.net, tyrrill_ed@emc.com
Subject: Re: Join question
In-Reply-To: <3D88F0CD-4914-460C-96D8-A7E06CC29C83@seespotcode.net>
* 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