X-Original-To: pgsql-performance@postgresql.org Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by postgresql.org (Postfix) with ESMTP id 56961475A6D for ; Wed, 5 Mar 2003 09:25:03 -0500 (EST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2) id 18qZpR-0000Th-0Z; Wed, 05 Mar 2003 14:25:05 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 4699F16393; Wed, 5 Mar 2003 14:25:04 +0000 (GMT) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 7FB8716286; Wed, 5 Mar 2003 14:25:03 +0000 (GMT) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: Greg Stark Subject: Re: Planner matching constants across tables in a join Date: Wed, 5 Mar 2003 14:24:12 +0000 User-Agent: KMail/1.4.3 Cc: References: <200303051113.14320.dev@archonet.com> <87bs0q9e8l.fsf@stark.dyndns.tv> In-Reply-To: <87bs0q9e8l.fsf@stark.dyndns.tv> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200303051424.12351.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Archive-Number: 200303/26 X-Sequence-Number: 1324 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 f= or=20 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=3Dc.id AND a.line_id=3Di.line_id AND a.start_time BETWEEN c.cam_from AND c.cam_to AND a.line_id=3D'0912345 0004' AND i.line_id=3D'0912345 0004'; =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 QUERY PLAN ---------- Merge Join (cost=3D348.01..348.72 rows=3D1 width=3D72) (actual time=3D115= .43..116.27=20 rows=3D21 loops=3D1) Merge Cond: ("outer".id =3D "inner".cam_id) Join Filter: (("outer".line_id)::text =3D ("inner".line_id)::text) -> Sort (cost=3D245.45..245.75 rows=3D118 width=3D40) (actual time=3D8= 3.98..84.10=20 rows=3D94 loops=3D1) Sort Key: c.id -> Nested Loop (cost=3D0.00..241.40 rows=3D118 width=3D40) (actu= al=20 time=3D3.83..83.27 rows=3D94 loops=3D1) Join Filter: (("outer".start_time >=3D=20 ("inner".cam_from)::timestamp without time zone) AND ("outer".start_time <= =3D=20 ("inner".cam_to)::timestamp without time zone)) -> Seq Scan on activity a (cost=3D0.00..199.00 rows=3D11= =20 width=3D28) (actual time=3D3.06..54.14 rows=3D19 loops=3D1) Filter: ((line_id)::text =3D '0912345 0004'::text) -> Seq Scan on campaign c (cost=3D0.00..2.00 rows=3D100 wi= dth=3D12)=20 (actual time=3D0.02..0.84 rows=3D100 loops=3D19) -> Sort (cost=3D102.56..102.57 rows=3D5 width=3D32) (actual time=3D31.= 36..31.39=20 rows=3D20 loops=3D1) Sort Key: i.cam_id -> Seq Scan on campaign_items i (cost=3D0.00..102.50 rows=3D5 wi= dth=3D32)=20 (actual time=3D17.16..31.11 rows=3D6 loops=3D1) Filter: ((line_id)::text =3D '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,=20 i.prod_id, i.chg_per_min, i.rev_per_min FROM campaign_items i, campaign c, activity a WHERE i.cam_id=3Dc.id AND a.line_id=3Di.line_id AND a.start_time BETWEEN c.cam_from AND c.cam_to AND i.line_id=3D'0912345 0004'; =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 QUERY PLAN --------------------------------------- Hash Join (cost=3D2.25..1623.70 rows=3D6 width=3D72) (actual time=3D48.27= ..974.30=20 rows=3D21 loops=3D1) Hash Cond: ("outer".cam_id =3D "inner".id) Join Filter: (("outer".start_time >=3D ("inner".cam_from)::timestamp wit= hout=20 time zone) AND ("outer".start_time <=3D ("inner".cam_to)::timestamp without= =20 time zone)) -> Nested Loop (cost=3D0.00..1619.87 rows=3D53 width=3D60) (actual=20 time=3D24.49..969.33 rows=3D114 loops=3D1) Join Filter: (("inner".line_id)::text =3D ("outer".line_id)::text) -> Seq Scan on campaign_items i (cost=3D0.00..102.50 rows=3D5 wi= dth=3D32)=20 (actual time=3D15.72..28.52 rows=3D6 loops=3D1) Filter: ((line_id)::text =3D '0912345 0004'::text) -> Seq Scan on activity a (cost=3D0.00..174.00 rows=3D10000 widt= h=3D28)=20 (actual time=3D0.03..101.95 rows=3D10000 loops=3D6) -> Hash (cost=3D2.00..2.00 rows=3D100 width=3D12) (actual time=3D1.54.= .1.54=20 rows=3D0 loops=3D1) -> Seq Scan on campaign c (cost=3D0.00..2.00 rows=3D100 width=3D= 12)=20 (actual time=3D0.06..0.94 rows=3D100 loops=3D1) Total runtime: 975.13 msec (11 rows) Table campaign has 100 rows, campaign_items 5000, activity 10000. My guess = is=20 that the planner starts with "campaign" because of the low number of rows,= =20 but it still looks like filtering on "activity" would help things. Indeed,= =20 testing a.line_id instead of i.line_id does make a difference. =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 QUERY PLAN ------------------- Hash Join (cost=3D241.70..457.54 rows=3D6 width=3D72) (actual time=3D161.= 20..225.68=20 rows=3D21 loops=3D1) Hash Cond: ("outer".cam_id =3D "inner".id) Join Filter: (("inner".line_id)::text =3D ("outer".line_id)::text) -> Seq Scan on campaign_items i (cost=3D0.00..90.00 rows=3D5000 width= =3D32)=20 (actual time=3D0.03..72.00 rows=3D5000 loops=3D1) -> Hash (cost=3D241.40..241.40 rows=3D118 width=3D40) (actual time=3D8= 5.46..85.46=20 rows=3D0 loops=3D1) -> Nested Loop (cost=3D0.00..241.40 rows=3D118 width=3D40) (actu= al=20 time=3D3.80..84.66 rows=3D94 loops=3D1) Join Filter: (("outer".start_time >=3D=20 ("inner".cam_from)::timestamp without time zone) AND ("outer".start_time <= =3D=20 ("inner".cam_to)::timestamp without time zone)) -> Seq Scan on activity a (cost=3D0.00..199.00 rows=3D11= =20 width=3D28) (actual time=3D3.03..54.48 rows=3D19 loops=3D1) Filter: ((line_id)::text =3D '0912345 0004'::text) -> Seq Scan on campaign c (cost=3D0.00..2.00 rows=3D100 wi= dth=3D12)=20 (actual time=3D0.03..0.89 rows=3D100 loops=3D19) Total runtime: 226.51 msec (11 rows) --=20 Richard Huxton