pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedomitting redundant join predicate
4+ messages / 2 participants
[nested] [flat]
* omitting redundant join predicate
@ 2007-11-04 13:14 Ehab Galal <ehabgalal123@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ehab Galal @ 2007-11-04 13:14 UTC (permalink / raw)
To: pgsql-sql
I tried the following query
explain select *
from t1, t2, t3
where t1.f <= t2.f
and t2.f <= t3.f
and t1.f <= t3.f;
And that's what I got:
Nested Loop (cost=0.00..3.15 rows=1 width=368)
Join Filter:
(("outer".f <= "inner".f) AND ("inner".f <=
"outer".f))
-> Nested Loop (cost=0.00..2.10 rows=1 width=218)
Join Filter:
("outer".f <= "inner".f)
-> Seq Scan on t1 (cost=0.00..1.01 rows=1 width=146)
-> Seq Scan on t3 (cost=0.00..1.04 rows=4 width=72)
-> Seq Scan on t2 (cost=0.00..1.02 rows=2 width=150)
I was wondering if there is a
way to omit the redundant join predicate.
Thanks,
--h
_________________________________________________________________
Windows Live Hotmail and Microsoft Office Outlook – together at last. Get it now.
http://office.microsoft.com/en-us/outlook/HA102225181033.aspx?pid=CL100626971033=
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: omitting redundant join predicate
@ 2007-11-04 16:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ehab Galal <ehabgalal123@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2007-11-04 16:35 UTC (permalink / raw)
To: Ehab Galal <ehabgalal123@hotmail.com>; +Cc: pgsql-sql
Ehab Galal <ehabgalal123@hotmail.com> writes:
> explain select *
> from t1, t2, t3
> where t1.f <= t2.f
> and t2.f <= t3.f
> and t1.f <= t3.f;
> I was wondering if there is a
> way to omit the redundant join predicate.
You're not being very clear here. Do you mean will you get the same
answer if you omit "t1.f <= t3.f"? Yes, of course (ignoring possibly
different output ordering). Do you mean you think the system should
discard it as redundant? I disagree --- the more join clauses the
better, as a rule. Do you mean that the EXPLAIN output looks like
the same comparison is being applied twice? It isn't --- in a more
modern PG release the output looks like this:
QUERY PLAN
------------------------------------------------------------------
Nested Loop (cost=33.54..81794021.44 rows=362975624 width=12)
Join Filter: ((t1.f <= t2.f) AND (t2.f <= t3.f))
-> Nested Loop (cost=0.00..124472.40 rows=1526533 width=8)
Join Filter: (t1.f <= t3.f)
-> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)
-> Seq Scan on t3 (cost=0.00..31.40 rows=2140 width=4)
-> Materialize (cost=33.54..54.94 rows=2140 width=4)
-> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=4)
(8 rows)
This is of course the stupidest possible join plan, but it's hard to do
much better --- both hash and merge joins work only on equality
conditions. You can do a bit better with an index on t2.f:
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=0.00..13222230.60 rows=362975624 width=12)
-> Nested Loop (cost=0.00..124472.40 rows=1526533 width=8)
Join Filter: (t1.f <= t3.f)
-> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)
-> Seq Scan on t3 (cost=0.00..31.40 rows=2140 width=4)
-> Index Scan using t2i on t2 (cost=0.00..5.01 rows=238 width=4)
Index Cond: ((t1.f <= t2.f) AND (t2.f <= t3.f))
(7 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: omitting redundant join predicate
@ 2007-11-05 15:15 Ehab Galal <ehabgalal123@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Ehab Galal @ 2007-11-05 15:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Sorry for not being clear enough. What i meant is how aware the optimizer is about the transitivity of operators.
I agree that the more join clauses a query gets, the more flexibility the optimizer gets to pick an optimal plan.
what i expected is that the optimizer will use the redundant predicates
to create the plan, but the execution plan itself will not execute a
redundant predicate.
O! I see, it's my mistake. The example i mentioned was not a good example. I tried the equality and it is working well :)
Nested Loop (cost=0.00..3.14 rows=1 width=368)
Join Filter: ("outer".username = "inner".username)
-> Nested Loop (cost=0.00..2.10 rows=1 width=218)
Join Filter: ("outer".username = "inner".username)
-> Seq Scan on t1 (cost=0.00..1.01 rows=1 width=146)
-> Seq Scan on t3 (cost=0.00..1.04 rows=4 width=72)
-> Seq Scan on t2 (cost=0.00..1.02 rows=2 width=150)
I am using postgresql 8.5.1, I am wondering is there is any patch that
i can run to enable it to put the actual table names instead of
inner/outer. Should i post this to the hackers mailing list?
Thanks a lot.
> To: ehabgalal123@hotmail.com
> CC: pgsql-sql@postgresql.org
> Subject: Re: [SQL] omitting redundant join predicate
> Date: Sun, 4 Nov 2007 11:35:36 -0500
> From: tgl@sss.pgh.pa.us
>
> Ehab Galal <ehabgalal123@hotmail.com> writes:
> > explain select *
> > from t1, t2, t3
> > where t1.f <= t2.f
> > and t2.f <= t3.f
> > and t1.f <= t3.f;
>
> > I was wondering if there is a
> > way to omit the redundant join predicate.
>
> You're not being very clear here. Do you mean will you get the same
> answer if you omit "t1.f <= t3.f"? Yes, of course (ignoring possibly
> different output ordering). Do you mean you think the system should
> discard it as redundant? I disagree --- the more join clauses the
> better, as a rule. Do you mean that the EXPLAIN output looks like
> the same comparison is being applied twice? It isn't --- in a more
> modern PG release the output looks like this:
>
> QUERY PLAN
> ------------------------------------------------------------------
> Nested Loop (cost=33.54..81794021.44 rows=362975624 width=12)
> Join Filter: ((t1.f <= t2.f) AND (t2.f <= t3.f))
> -> Nested Loop (cost=0.00..124472.40 rows=1526533 width=8)
> Join Filter: (t1.f <= t3.f)
> -> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)
> -> Seq Scan on t3 (cost=0.00..31.40 rows=2140 width=4)
> -> Materialize (cost=33.54..54.94 rows=2140 width=4)
> -> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=4)
> (8 rows)
>
> This is of course the stupidest possible join plan, but it's hard to do
> much better --- both hash and merge joins work only on equality
> conditions. You can do a bit better with an index on t2.f:
>
> QUERY PLAN
> ----------------------------------------------------------------------
> Nested Loop (cost=0.00..13222230.60 rows=362975624 width=12)
> -> Nested Loop (cost=0.00..124472.40 rows=1526533 width=8)
> Join Filter: (t1.f <= t3.f)
> -> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)
> -> Seq Scan on t3 (cost=0.00..31.40 rows=2140 width=4)
> -> Index Scan using t2i on t2 (cost=0.00..5.01 rows=238 width=4)
> Index Cond: ((t1.f <= t2.f) AND (t2.f <= t3.f))
> (7 rows)
>
> regards, tom lane
_________________________________________________________________
Help yourself to FREE treats served up daily at the Messenger Café. Stop by today.
http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid=TXT_TAGLM_OctWLtagline=
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: omitting redundant join predicate
@ 2007-11-05 16:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ehab Galal <ehabgalal123@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-11-05 16:08 UTC (permalink / raw)
To: Ehab Galal <ehabgalal123@hotmail.com>; +Cc: pgsql-sql
Ehab Galal <ehabgalal123@hotmail.com> writes:
> what i expected is that the optimizer will use the redundant predicates
> to create the plan, but the execution plan itself will not execute a
> redundant predicate.
> O! I see, it's my mistake. The example i mentioned was not a good example. I tried the equality and it is working well :)
The planner has a great deal more smarts about equality conditions than
inequality conditions. There's more you can do with equalities, and
it's more useful for typical queries.
> I am using postgresql 8.5.1, I am wondering is there is any patch that
> i can run to enable it to put the actual table names instead of
> inner/outer.
Update to 8.2.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-11-05 16:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-04 13:14 omitting redundant join predicate Ehab Galal <ehabgalal123@hotmail.com>
2007-11-04 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-05 15:15 ` Ehab Galal <ehabgalal123@hotmail.com>
2007-11-05 16: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