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.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.
--
Ceri Storey <cez(at)necrofish(dot)org(dot)uk>