pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanner matching constants across tables in a join
9+ messages / 4 participants
[nested] [flat]
* Planner matching constants across tables in a join
@ 2003-03-05 11:13 Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2003-03-05 11:13 UTC (permalink / raw)
To: pgsql-performance
I know this has been covered on one of the lists in the past, but I'm damned
if I can find the keywords to locate it.
If I join two tables with a comparison to a constant on one, why can't the
planner see that the comparison applies to both tables:
SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1;
runs much slower than
SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1 AND b.id=1;
It's not a real problem since it's easy to work around, but I was wondering
what the difficulties are for the planner in seeing that query 1 is the same
as query 2. Note that it doesn't seem related to JOIN forcing the planner's
hand, the same applies just using WHERE a.id=b.id
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a join
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
@ 2003-03-05 12:42 ` Greg Stark <gsstark@mit.edu>
2003-03-05 14:24 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 15:08 ` Re: Planner matching constants across tables in a join Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Greg Stark @ 2003-03-05 12:42 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> I know this has been covered on one of the lists in the past, but I'm damned
> if I can find the keywords to locate it.
>
> If I join two tables with a comparison to a constant on one, why can't the
> planner see that the comparison applies to both tables:
It sure does. Postgres does an impressive job of tracing equality clauses
around for just this purpose.
> SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1;
>
> runs much slower than
>
> SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1 AND b.id=1;
Really? They produce virtually the same plan for me.
Why do you think it'll run slower?
What query are you actually finding slow?
--
greg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a join
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
@ 2003-03-05 14:24 ` Richard Huxton <dev@archonet.com>
2003-03-05 15:02 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2003-03-05 14:24 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a join
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 14:24 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
@ 2003-03-05 15:02 ` Greg Stark <gsstark@mit.edu>
2003-03-05 16:12 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Greg Stark @ 2003-03-05 15:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Greg Stark <gsstark@mit.edu>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> Filter: ((line_id)::text = '0912345 0004'::text)
So I think this means that line_id is being casted to "text". Though I'm not
clear why it would be choosing "text" for the constant if line_id wasn't text
to begin with.
In any case my plans here look like:
> Filter: (aa = 'x'::text)
so it looks like there's something extra going on in your plan.
what does your table definition look like?
--
greg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a join
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 14:24 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 15:02 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
@ 2003-03-05 16:12 ` Richard Huxton <dev@archonet.com>
2003-03-05 19:00 ` Re: Planner matching constants across tables in a Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2003-03-05 16:12 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Greg Stark <gsstark@mit.edu>; pgsql-performance
On Wednesday 05 Mar 2003 3:02 pm, Greg Stark wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Filter: ((line_id)::text = '0912345 0004'::text)
>
> So I think this means that line_id is being casted to "text". Though I'm
> not clear why it would be choosing "text" for the constant if line_id
> wasn't text to begin with.
A domain defined as varchar() actually - which is why it's not using an index,
but that's neither here nor there regarding the constant issue.
> In any case my plans here look like:
> > Filter: (aa = 'x'::text)
>
> so it looks like there's something extra going on in your plan.
>
> what does your table definition look like?
rms=> \d campaign
Table "rms.campaign"
Column | Type | Modifiers
----------+-----------+-----------
id | integer | not null
title | item_name |
cam_from | date |
cam_to | date |
owner | integer |
Indexes: campaign_pkey primary key btree (id),
campaign_from_idx btree (cam_from),
campaign_to_idx btree (cam_to)
rms=> \d campaign_items
Table "rms.campaign_items"
Column | Type | Modifiers
-------------+---------+-----------
cam_id | integer | not null
line_id | tel_num | not null
prod_id | integer | not null
chg_per_min | integer |
rev_per_min | integer |
Indexes: campaign_items_pkey primary key btree (cam_id, line_id, prod_id),
cam_item_line_idx btree (line_id)
Foreign Key constraints: $1 FOREIGN KEY (cam_id) REFERENCES campaign(id) ON
UPDATE NO ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (line_id) REFERENCES line(telno) ON
UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (prod_id) REFERENCES product(id) ON
UPDATE NO ACTION ON DELETE NO ACTION
rms=> \d activity
Table "rms.activity"
Column | Type | Modifiers
------------+-----------------------------+-----------
line_id | tel_num | not null
start_time | timestamp without time zone | not null
call_dur | integer |
Indexes: activity_pkey primary key btree (line_id, start_time),
activity_start_idx btree (start_time)
Foreign Key constraints: $1 FOREIGN KEY (line_id) REFERENCES line(telno) ON
UPDATE NO ACTION ON DELETE NO ACTION
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 14:24 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 15:02 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 16:12 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
@ 2003-03-05 19:00 ` Josh Berkus <josh@agliodbs.com>
2003-03-05 19:25 ` Re: Planner matching constants across tables in a Richard Huxton <dev@archonet.com>
2003-03-05 19:31 ` Re: Planner matching constants across tables in a Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 9+ messages in thread
From: Josh Berkus @ 2003-03-05 19:00 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Greg Stark <gsstark@mit.edu>; +Cc: Greg Stark <gsstark@mit.edu>; pgsql-performance
Richard,
> A domain defined as varchar() actually - which is why it's not using
> an index,
> but that's neither here nor there regarding the constant issue.
You might improve your performance overall if you cast the constant to
tel_num before doing the comparison in the query. Right now, the
parser is casting the whole column to text instead, because it can't
tell that the constant you supply is a valid tel_num.
-Josh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 14:24 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 15:02 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 16:12 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 19:00 ` Re: Planner matching constants across tables in a Josh Berkus <josh@agliodbs.com>
@ 2003-03-05 19:25 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2003-03-05 19:25 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Greg Stark <gsstark@mit.edu>; +Cc: Greg Stark <gsstark@mit.edu>; pgsql-performance
On Wednesday 05 Mar 2003 7:00 pm, Josh Berkus wrote:
> Richard,
>
> > A domain defined as varchar() actually - which is why it's not using
> > an index,
> > but that's neither here nor there regarding the constant issue.
>
> You might improve your performance overall if you cast the constant to
> tel_num before doing the comparison in the query. Right now, the
> parser is casting the whole column to text instead, because it can't
> tell that the constant you supply is a valid tel_num.
That's what I thought, but...
rms=> EXPLAIN ANALYSE SELECT * FROM line WHERE telno='0912345 0004'::tel_num;
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on line (cost=0.00..20.50 rows=1 width=28) (actual time=0.10..5.28
rows=1 loops=1)
Filter: ((telno)::text = ('0912345 0004'::character varying)::text)
Total runtime: 5.43 msec
rms=> EXPLAIN ANALYSE SELECT * FROM line WHERE telno='0912345 0004'::varchar;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using line_pkey on line (cost=0.00..5.78 rows=1 width=28) (actual
time=14.03..14.03 rows=1 loops=1)
Index Cond: ((telno)::character varying = '0912345 0004'::character
varying)
Total runtime: 14.28 msec
Ignoring the times (fake data on my test box) it seems like there's an issue
in comparing against DOMAIN defined types. Or maybe it's in the index
definition, although I don't know how to find out the type of an index.
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 14:24 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 15:02 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
2003-03-05 16:12 ` Re: Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 19:00 ` Re: Planner matching constants across tables in a Josh Berkus <josh@agliodbs.com>
@ 2003-03-05 19:31 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2003-03-05 19:31 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Greg Stark <gsstark@mit.edu>; +Cc: pgsql-performance
On Wednesday 05 Mar 2003 7:00 pm, Josh Berkus wrote:
> You might improve your performance overall if you cast the constant to
> tel_num before doing the comparison in the query.
Stranger and stranger...
richardh=# CREATE DOMAIN intdom int4;
richardh=# CREATE DOMAIN textdom text;
richardh=# CREATE TABLE domtest (a intdom, b textdom);
richardh=# CREATE INDEX domtest_a_idx ON domtest (a);
richardh=# CREATE INDEX domtest_b_idx ON domtest (b);
richardh=# INSERT INTO domtest VALUES (1,'aaa');
richardh=# INSERT INTO domtest VALUES (2,'bbb');
richardh=# INSERT INTO domtest VALUES (3,'ccc');
richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE a=1::intdom;
-------------------------------------------------------------------------------------------------
Seq Scan on domtest (cost=0.00..22.50 rows=5 width=36) (actual
time=0.08..0.11 rows=1 loops=1)
Filter: ((a)::oid = 1::oid)
richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE a=1::int4;
-----------------------------------------------------------------------------------------------------------------------
Index Scan using domtest_a_idx on domtest (cost=0.00..17.07 rows=5 width=36)
(actual time=0.09..0.11 rows=1 loops=1)
Index Cond: ((a)::integer = 1)
richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE b='aaa'::textdom;
-----------------------------------------------------------------------------------------------------------------------
Index Scan using domtest_b_idx on domtest (cost=0.00..17.07 rows=5 width=36)
(actual time=0.09..0.11 rows=1 loops=1)
Index Cond: ((b)::text = 'aaa'::text)
richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE b='aaa'::text;
-----------------------------------------------------------------------------------------------------------------------
Index Scan using domtest_b_idx on domtest (cost=0.00..17.07 rows=5 width=36)
(actual time=0.10..0.12 rows=1 loops=1)
Index Cond: ((b)::text = 'aaa'::text)
Can't think why we're getting casts to type "oid" in the first example - I'd
have thought int4 would be the default. I'm guessing the text domain always
works because that's the default cast.
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner matching constants across tables in a join
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Re: Planner matching constants across tables in a join Greg Stark <gsstark@mit.edu>
@ 2003-03-05 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2003-03-05 15:08 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
Greg Stark <gsstark@mit.edu> writes:
> Richard Huxton <dev@archonet.com> writes:
>> If I join two tables with a comparison to a constant on one, why can't the
>> planner see that the comparison applies to both tables:
> It sure does. Postgres does an impressive job of tracing equality clauses
> around for just this purpose.
CVS tip does. Existing releases don't...
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-03-05 19:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-05 11:13 Planner matching constants across tables in a join Richard Huxton <dev@archonet.com>
2003-03-05 12:42 ` Greg Stark <gsstark@mit.edu>
2003-03-05 14:24 ` Richard Huxton <dev@archonet.com>
2003-03-05 15:02 ` Greg Stark <gsstark@mit.edu>
2003-03-05 16:12 ` Richard Huxton <dev@archonet.com>
2003-03-05 19:00 ` Re: Planner matching constants across tables in a Josh Berkus <josh@agliodbs.com>
2003-03-05 19:25 ` Re: Planner matching constants across tables in a Richard Huxton <dev@archonet.com>
2003-03-05 19:31 ` Re: Planner matching constants across tables in a Richard Huxton <dev@archonet.com>
2003-03-05 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
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