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
FROMwatch_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_rawdesc, 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
OUTERJOIN
(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.34rows=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.55rows=6 width=12) (actual time=1.74..1.74 rows=0 loops=1) -> Subquery Scan temp
(cost=1034.24..1034.55rows=6 width=12) (actual time=1.18..1.64 rows=10 loops=1) -> Aggregate
(cost=1034.24..1034.55rows=6 width=12) (actual time=1.17..1.52 rows=10 loops=1) -> Group
(cost=1034.24..1034.39rows=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.31rows=3 loops=1) -> Index Scan using watch_list_element_pkey
onwatch_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/
