pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ceri Storey <cez@necrofish.org.uk>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Ceri Storey <cez@necrofish.org.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join optimisation Quandry
Date: Sat, 17 Jan 2004 10:40:49 +0000
Message-ID: <20040117104049.GC43530@mrtall.compsoc.man.ac.uk> (raw)
In-Reply-To: <20040116220146.N81715@megazone.bigpanda.com>
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>

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 <cez@necrofish.org.uk>

Attachments:

exp-out (text/plain, 2.3K) download 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

view thread (7+ messages)  latest in thread

Message-ID: <20040117104049.GC43530@mrtall.compsoc.man.ac.uk>
Permalink:  ../20040117104049.GC43530@mrtall.compsoc.man.ac.uk/
Also on:    postgresql.org/message-id/20040117104049.GC43530@mrtall.compsoc.man.ac.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: cez@necrofish.org.uk, sszabo@megazone.bigpanda.com
  Subject: Re: Join optimisation Quandry
  In-Reply-To: <20040117104049.GC43530@mrtall.compsoc.man.ac.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox