X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 90CF3D1D8D2 for ; Sat, 17 Jan 2004 10:41:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 00683-09 for ; Sat, 17 Jan 2004 06:40:56 -0400 (AST) Received: from probity.mcc.ac.uk (probity.mcc.ac.uk [130.88.200.94]) by svr1.postgresql.org (Postfix) with ESMTP id 1D5E5D1B7ED for ; Sat, 17 Jan 2004 06:40:53 -0400 (AST) Received: from compsoc.umu.man.ac.uk ([130.88.22.5] helo=mrtall.compsoc.man.ac.uk ident=exim) by probity.mcc.ac.uk with esmtp (Exim 4.20) id 1Ahnsn-0000iW-Bp; Sat, 17 Jan 2004 10:40:49 +0000 Received: from cez by mrtall.compsoc.man.ac.uk with local (Exim 3.33 #1) id 1Ahnsn-000LKV-00; Sat, 17 Jan 2004 10:40:49 +0000 Date: Sat, 17 Jan 2004 10:40:49 +0000 From: Ceri Storey To: Stephan Szabo Cc: Ceri Storey , pgsql-performance@postgresql.org Subject: Re: Join optimisation Quandry Message-ID: <20040117104049.GC43530@mrtall.compsoc.man.ac.uk> References: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk> <20040116094935.P66040@megazone.bigpanda.com> <20040117010334.GA43530@mrtall.compsoc.man.ac.uk> <20040116220146.N81715@megazone.bigpanda.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="J2SCkAp4GZ/dPZZf" Content-Disposition: inline In-Reply-To: <20040116220146.N81715@megazone.bigpanda.com> User-Agent: Mutt/1.4i X-Scanner: exiscan for exim4 (http://duncanthrax.net/exiscan/) *1Ahnsn-0000iW-Bp*dXECNjkCDjw* X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/190 X-Sequence-Number: 5364 --J2SCkAp4GZ/dPZZf Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --J2SCkAp4GZ/dPZZf Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=exp-out 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 --J2SCkAp4GZ/dPZZf--