pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Greg Stark <gsstark@mit.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner matching constants across tables in a join
Date: Wed, 5 Mar 2003 14:24:12 +0000
Message-ID: <200303051424.12351.dev@archonet.com> (raw)
In-Reply-To: <87bs0q9e8l.fsf@stark.dyndns.tv>
References: <200303051113.14320.dev@archonet.com>
	<87bs0q9e8l.fsf@stark.dyndns.tv>

On Wednesday 05 Mar 2003 12:42 pm, Greg Stark wrote:
> Really? They produce virtually the same plan for me.
>
> Why do you think it'll run slower?
> What query are you actually finding slow?

The actual query uses three tables, but isn't very complicated. Apologies for 
the wrapping on the explain.

EXPLAIN ANALYSE SELECT a.line_id, a.start_time, a.call_dur, i.cam_id,
i.prod_id, i.chg_per_min, i.rev_per_min
FROM campaign_items i, campaign c, activity a
WHERE
i.cam_id=c.id AND a.line_id=i.line_id
AND a.start_time BETWEEN c.cam_from AND c.cam_to
AND a.line_id='0912345 0004' AND i.line_id='0912345 0004';

                                                                                   
QUERY PLAN
----------
 Merge Join  (cost=348.01..348.72 rows=1 width=72) (actual time=115.43..116.27 
rows=21 loops=1)
   Merge Cond: ("outer".id = "inner".cam_id)
   Join Filter: (("outer".line_id)::text = ("inner".line_id)::text)
   ->  Sort  (cost=245.45..245.75 rows=118 width=40) (actual time=83.98..84.10 
rows=94 loops=1)
         Sort Key: c.id
         ->  Nested Loop  (cost=0.00..241.40 rows=118 width=40) (actual 
time=3.83..83.27 rows=94 loops=1)
               Join Filter: (("outer".start_time >= 
("inner".cam_from)::timestamp without time zone) AND ("outer".start_time <= 
("inner".cam_to)::timestamp without time zone))
               ->  Seq Scan on activity a  (cost=0.00..199.00 rows=11 
width=28) (actual time=3.06..54.14 rows=19 loops=1)
                     Filter: ((line_id)::text = '0912345 0004'::text)
               ->  Seq Scan on campaign c  (cost=0.00..2.00 rows=100 width=12) 
(actual time=0.02..0.84 rows=100 loops=19)
   ->  Sort  (cost=102.56..102.57 rows=5 width=32) (actual time=31.36..31.39 
rows=20 loops=1)
         Sort Key: i.cam_id
         ->  Seq Scan on campaign_items i  (cost=0.00..102.50 rows=5 width=32) 
(actual time=17.16..31.11 rows=6 loops=1)
               Filter: ((line_id)::text = '0912345 0004'::text)
 Total runtime: 117.08 msec
(15 rows)


and this is the plan where I just check the one line_id:


EXPLAIN ANALYSE SELECT a.line_id, a.start_time, a.call_dur, i.cam_id, 
i.prod_id, i.chg_per_min, i.rev_per_min
FROM campaign_items i, campaign c, activity a
WHERE
i.cam_id=c.id AND a.line_id=i.line_id
AND a.start_time BETWEEN c.cam_from AND c.cam_to
AND i.line_id='0912345 0004';
                                                                             
QUERY PLAN
---------------------------------------
 Hash Join  (cost=2.25..1623.70 rows=6 width=72) (actual time=48.27..974.30 
rows=21 loops=1)
   Hash Cond: ("outer".cam_id = "inner".id)
   Join Filter: (("outer".start_time >= ("inner".cam_from)::timestamp without 
time zone) AND ("outer".start_time <= ("inner".cam_to)::timestamp without 
time zone))
   ->  Nested Loop  (cost=0.00..1619.87 rows=53 width=60) (actual 
time=24.49..969.33 rows=114 loops=1)
         Join Filter: (("inner".line_id)::text = ("outer".line_id)::text)
         ->  Seq Scan on campaign_items i  (cost=0.00..102.50 rows=5 width=32) 
(actual time=15.72..28.52 rows=6 loops=1)
               Filter: ((line_id)::text = '0912345 0004'::text)
         ->  Seq Scan on activity a  (cost=0.00..174.00 rows=10000 width=28) 
(actual time=0.03..101.95 rows=10000 loops=6)
   ->  Hash  (cost=2.00..2.00 rows=100 width=12) (actual time=1.54..1.54 
rows=0 loops=1)
         ->  Seq Scan on campaign c  (cost=0.00..2.00 rows=100 width=12) 
(actual time=0.06..0.94 rows=100 loops=1)
 Total runtime: 975.13 msec
(11 rows)

Table campaign has 100 rows, campaign_items 5000, activity 10000. My guess is 
that the planner starts with "campaign" because of the low number of rows, 
but it still looks like filtering on "activity" would help things. Indeed, 
testing a.line_id instead of i.line_id does make a difference.

                                                                                   
QUERY PLAN
-------------------
 Hash Join  (cost=241.70..457.54 rows=6 width=72) (actual time=161.20..225.68 
rows=21 loops=1)
   Hash Cond: ("outer".cam_id = "inner".id)
   Join Filter: (("inner".line_id)::text = ("outer".line_id)::text)
   ->  Seq Scan on campaign_items i  (cost=0.00..90.00 rows=5000 width=32) 
(actual time=0.03..72.00 rows=5000 loops=1)
   ->  Hash  (cost=241.40..241.40 rows=118 width=40) (actual time=85.46..85.46 
rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..241.40 rows=118 width=40) (actual 
time=3.80..84.66 rows=94 loops=1)
               Join Filter: (("outer".start_time >= 
("inner".cam_from)::timestamp without time zone) AND ("outer".start_time <= 
("inner".cam_to)::timestamp without time zone))
               ->  Seq Scan on activity a  (cost=0.00..199.00 rows=11 
width=28) (actual time=3.03..54.48 rows=19 loops=1)
                     Filter: ((line_id)::text = '0912345 0004'::text)
               ->  Seq Scan on campaign c  (cost=0.00..2.00 rows=100 width=12) 
(actual time=0.03..0.89 rows=100 loops=19)
 Total runtime: 226.51 msec
(11 rows)

-- 
  Richard Huxton



view thread (9+ messages)  latest in thread

Message-ID: <200303051424.12351.dev@archonet.com>
Permalink:  ../200303051424.12351.dev@archonet.com/
Also on:    postgresql.org/message-id/200303051424.12351.dev@archonet.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, gsstark@mit.edu
  Subject: Re: Planner matching constants across tables in a join
  In-Reply-To: <200303051424.12351.dev@archonet.com>
  References: <200303051113.14320.dev@archonet.com> <87bs0q9e8l.fsf@stark.dyndns.tv> <200303051424.12351.dev@archonet.com>

* 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