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

 Sort  (cost=983.38..983.38 rows=1 width=85) (actual time=10988.525..10988.557 rows=17 
   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.809 rows=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=10715 loops=17)
 Total runtime: 10989.661 ms

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

Thanks for looking into it.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to