adding a GROUP BY to an outer join - Mailing list pgsql-sql

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/