From: "Dan Langille" <dan@langille.org>
To: pgsql-sql@postgresql.org
Date: Sun, 08 Dec 2002 22:38:43 -0500
MIME-Version: 1.0
Subject: adding a GROUP BY to an outer join
Message-ID: <3DF3C9F3.8075.184CB710@localhost>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

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/