Skip site navigation (1) Skip section navigation (2)

Re: Join optimisation Quandry

From: Ceri Storey <cez(at)necrofish(dot)org(dot)uk>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Ceri Storey <cez(at)necrofish(dot)org(dot)uk>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Join optimisation Quandry
Date: 2004-01-17 01:03:34
Message-ID: 20040117010334.GA43530@mrtall.compsoc.man.ac.uk (view raw or flat)
Thread:
Lists: pgsql-performance
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>

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-01-17 01:13:55
Subject: Re: Potential Problem with PostgeSQL performance on SuSE
Previous:From: Mark KirkwoodDate: 2004-01-16 22:11:33
Subject: Re: [PERFORM] Potential Problem with PostgeSQL performance on SuSE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group