Re: Join optimisation Quandry

On Fri, Jan 16, 2004 at 10:17:50AM -0800, Stephan Szabo wrote:
> As a starting point, we're likely to need the exact query, explain analyze
> output for the query and version information.

Okay, from top to bottom:

  SELECT p1.chan_name, p1.prog_start AS now_start, p1.prog_id, p1.title_text,
    p2.prog_start AS next_start, p2.prog_id, p2.title_text,
    p1.title_wanted, p2.title_wanted, p1.chan_id
  FROM (programme natural join channel NATURAL JOIN title) AS p1
    LEFT OUTER JOIN (programme NATURAL JOIN title) AS p2
    ON p1.prog_next = p2.prog_id
  WHERE p1.prog_start <= timestamp 'now' AND p1.prog_stop > timestamp 'now'
  ORDER BY p1.chan_id ASC

QUERY PLAN
----
 Sort  (cost=983.38..983.38 rows=1 width=85) (actual time=10988.525..10988.557 rows=17 loops=1)
   Sort Key: public.programme.chan_id
   ->  Nested Loop Left Join  (cost=289.86..983.37 rows=1 width=85) (actual time=631.918..10988.127 rows=17 loops=1)
         Join Filter: ("outer".prog_next = "inner".prog_id)
         ->  Nested Loop  (cost=4.33..9.12 rows=1 width=55) (actual time=4.111..7.960 rows=17 loops=1)
               ->  Hash Join  (cost=4.33..5.64 rows=1 width=37) (actual time=4.017..5.182 rows=17 loops=1)
                     Hash Cond: ("outer".chan_id = "inner".chan_id)
                     ->  Seq Scan on channel  (cost=0.00..1.20 rows=20 width=17) (actual time=0.017..0.403 rows=20
loops=1)
                     ->  Hash  (cost=4.32..4.32 rows=1 width=24) (actual time=3.910..3.910 rows=0 loops=1)
                           ->  Index Scan using prog_stop_idx on programme  (cost=0.00..4.32 rows=1 width=24) (actual
time=0.140..3.809rows=17 loops=1) 
                                 Index Cond: (prog_stop > '2004-01-17 01:01:51.786145'::timestamp without time zone)
                                 Filter: (prog_start <= '2004-01-17 01:01:51.786145'::timestamp without time zone)
               ->  Index Scan using "$3" on title  (cost=0.00..3.47 rows=1 width=26) (actual time=0.078..0.114 rows=1
loops=17)
                     Index Cond: ("outer".title_id = title.title_id)
         ->  Hash Join  (cost=285.54..892.91 rows=6507 width=34) (actual time=191.612..586.407 rows=7145 loops=17)
               Hash Cond: ("outer".title_id = "inner".title_id)
               ->  Seq Scan on programme  (cost=0.00..121.07 rows=6507 width=16) (actual time=0.036..42.337 rows=7145
loops=17)
               ->  Hash  (cost=190.83..190.83 rows=10683 width=26) (actual time=190.795..190.795 rows=0 loops=17)
                     ->  Seq Scan on title  (cost=0.00..190.83 rows=10683 width=26) (actual time=0.143..113.223
rows=10715loops=17) 
 Total runtime: 10989.661 ms

And both client and server are:
postgres (PostgreSQL) 7.4.1

Thanks for looking into it.
--
Ceri Storey <cez@necrofish.org.uk>