On Fri, Jan 16, 2004 at 10:05:54PM -0800, Stephan Szabo wrote:
> Well the plan would seems reasonable to me if there really was only 1 row
> coming from the where conditions on p1. As a first step, if you raise the
> statistics target (see ALTER TABLE) for prog_start and prog_stop and
> re-analyze the table, do you get a better estimate of the rows from that
> condition?
Indeed, that helps a fair bit with the estimate.
> Secondly, what do you get if you enable_nestloop=off before
> explain analyzing the query?
See attachment.
Saying that, I've managed to halve the query time by lifting the join of
the title out of the RHS of the left outer join into the top-level of
the FROM clause; which was really the kind of advice I was after. If
this is the wrong list for that kind of thing, please say so.
Again, thanks.
--
Ceri Storey <[EMAIL PROTECTED]>
Sort (cost=2144.43..2148.83 rows=1762 width=348) (actual time=993.292..993.329
rows=18 loops=1)
Sort Key: public.programme.chan_id
-> Hash Left Join (cost=1261.53..2049.43 rows=1762 width=348) (actual
time=991.208..992.905 rows=18 loops=1)
Hash Cond: ("outer".prog_next = "inner".prog_id)
-> Hash Join (cost=250.81..596.33 rows=1762 width=271) (actual
time=24.193..176.767 rows=18 loops=1)
Hash Cond: ("outer".chan_id = "inner".chan_id)
-> Merge Join (cost=249.56..568.64 rows=1762 width=77) (actual
time=23.710..171.313 rows=18 loops=1)
Merge Cond: ("outer".title_id = "inner".title_id)
-> Index Scan using "$3" on title (cost=0.00..266.13 rows=10683
width=27) (actual time=0.047..81.038 rows=10650 loops=1)
-> Sort (cost=249.56..253.96 rows=1762 width=54) (actual
time=23.254..23.298 rows=18 loops=1)
Sort Key: public.programme.title_id
-> Index Scan using prog_stop_idx on programme
(cost=0.00..154.56 rows=1762 width=54) (actual time=0.105..23.107 rows=18 loops=1)
Index Cond: (prog_stop > '2004-01-14
19:52:46'::timestamp without time zone)
Filter: (prog_start <= '2004-01-14
19:52:46'::timestamp without time zone)
-> Hash (cost=1.20..1.20 rows=20 width=198) (actual time=0.354..0.354
rows=0 loops=1)
-> Seq Scan on channel (cost=0.00..1.20 rows=20 width=198)
(actual time=0.062..0.252 rows=20 loops=1)
-> Hash (cost=887.51..887.51 rows=8085 width=77) (actual
time=782.379..782.379 rows=0 loops=1)
-> Hash Join (cost=285.54..887.51 rows=8085 width=77) (actual
time=212.806..703.331 rows=8085 loops=1)
Hash Cond: ("outer".title_id = "inner".title_id)
-> Seq Scan on programme (cost=0.00..173.85 rows=8085 width=54)
(actual time=0.023..45.351 rows=8085 loops=1)
-> Hash (cost=190.83..190.83 rows=10683 width=27) (actual
time=212.350..212.350 rows=0 loops=1)
-> Seq Scan on title (cost=0.00..190.83 rows=10683
width=27) (actual time=0.061..131.381 rows=10767 loops=1)
Total runtime: 995.482 ms
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
