From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Subject: adding a GROUP BY to an outer join
Date: Sun, 08 Dec 2002 22:38:43 -0500
Message-ID: <3DF3C9F3.8075.184CB710@localhost> (raw)
This select gives me the number of times a given element appears on
each of the watch lists owned by user 2;
SELECT COUNT(watch_list_id), element_id
FROM watch_list WL, watch_list_element WLE
WHERE WL.user_id = 2
AND WL.id = WLE.watch_list_id
GROUP BY WLE.element_id;
This query assumes there is only one watch list per person, and it tells
me whether or not a given item in commits_latest_ports appears on that
single watch list.
SELECT category, port,
CASE when WLE.element_id is null
then 0
else 1
END as watch
FROM watch_list_element WLE RIGHT OUTER JOIN
(
select * from commits_latest_ports
) AS TEMP
ON WLE.watch_list_id = 32
AND WLE.element_id = TEMP.element_id
ORDER BY commit_date_raw desc, category, port limit 10
My goal is to combine the two queries (i.e. allow multiple watch lists).
What I came up with works well. Can you see another solution?
select category, port, commits_latest_ports.element_id, commit_date_raw, TEMP.watch
from commits_latest_ports
LEFT OUTER JOIN
(SELECT element_id, COUNT(watch_list_id) as watch
FROM watch_list JOIN watch_list_element
ON watch_list.id = watch_list_element.watch_list_id
AND watch_list.user_id = 2
GROUP BY watch_list_element.element_id) AS TEMP
ON TEMP.element_id = commits_latest_ports.element_id
ORDER BY commit_date_raw, category, port;
She runs pretty well:
Sort (cost=1046.27..1046.27 rows=115 width=44) (actual time=6.18..6.75 rows=115 loops=1)
-> Hash Join (cost=1034.57..1042.34 rows=115 width=44) (actual time=1.94..4.88 rows=115 loops=1)
-> Seq Scan on commits_latest_ports (cost=0.00..7.15 rows=115 width=32) (actual time=0.09..1.51 rows=115 loops=1)
-> Hash (cost=1034.55..1034.55 rows=6 width=12) (actual time=1.74..1.74 rows=0 loops=1)
-> Subquery Scan temp (cost=1034.24..1034.55 rows=6 width=12) (actual time=1.18..1.64 rows=10 loops=1)
-> Aggregate (cost=1034.24..1034.55 rows=6 width=12) (actual time=1.17..1.52 rows=10 loops=1)
-> Group (cost=1034.24..1034.39 rows=63 width=12) (actual time=1.11..1.32 rows=10 loops=1)
-> Sort (cost=1034.24..1034.24 rows=63 width=12) (actual time=1.10..1.15 rows=10 loops=1)
-> Nested Loop (cost=0.00..1032.35 rows=63 width=12) (actual time=0.64..0.97 rows=10 loops=1)
-> Index Scan using watch_list_user_id on watch_list (cost=0.00..15.25 rows=4 width=4) (actual time=0.29..0.31 rows=3 loops=1)
-> Index Scan using watch_list_element_pkey on watch_list_element (cost=0.00..272.63 rows=75 width=8) (actual time=0.12..0.16 rows=3 loops=3)
Total runtime: 19.78 msec
Phew! That's fast!
--
Dan Langille : http://www.langille.org/
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: dan@langille.org
Subject: Re: adding a GROUP BY to an outer join
In-Reply-To: <3DF3C9F3.8075.184CB710@localhost>
* 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