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 01:03:34 +0000
Message-ID: <20040117010334.GA43530@mrtall.compsoc.man.ac.uk> (raw)
In-Reply-To: <20040116094935.P66040@megazone.bigpanda.com>
References: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk>
	<20040116094935.P66040@megazone.bigpanda.com>

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



view thread (7+ messages)  latest in thread

Message-ID: <20040117010334.GA43530@mrtall.compsoc.man.ac.uk>
Permalink:  ../20040117010334.GA43530@mrtall.compsoc.man.ac.uk/
Also on:    postgresql.org/message-id/20040117010334.GA43530@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: <20040117010334.GA43530@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