Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D05589F959C for ; Mon, 5 Nov 2007 11:15:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 70407-10 for ; Mon, 5 Nov 2007 11:15:29 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from bay0-omc3-s22.bay0.hotmail.com (bay0-omc3-s22.bay0.hotmail.com [65.54.246.222]) by postgresql.org (Postfix) with ESMTP id C67DC9F958B for ; Mon, 5 Nov 2007 11:15:37 -0400 (AST) Received: from BAY138-W45 ([64.4.49.80]) by bay0-omc3-s22.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Mon, 5 Nov 2007 07:15:36 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_cd071b5c-57f6-49b9-8078-7efd18b32fc8_" X-Originating-IP: [128.10.9.112] From: Ehab Galal To: Tom Lane CC: "pgsql-sql@postgresql.org" Subject: Re: omitting redundant join predicate Date: Mon, 5 Nov 2007 10:15:36 -0500 Importance: Normal In-Reply-To: <14450.1194194136@sss.pgh.pa.us> References: <14450.1194194136@sss.pgh.pa.us> MIME-Version: 1.0 X-OriginalArrivalTime: 05 Nov 2007 15:15:36.0405 (UTC) FILETIME=[B7912850:01C81FBE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/15 X-Sequence-Number: 29543 --_cd071b5c-57f6-49b9-8078-7efd18b32fc8_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Sorry for not being clear enough. What i meant is how aware the optimizer i= s about the transitivity of operators.=20 =0A= =0A= I agree that the more join clauses a query gets, the more flexibility the o= ptimizer gets to pick an optimal plan. =0A= =0A= what i expected is that the optimizer will use the redundant predicates=0A= to create the plan, but the execution plan itself will not execute a=0A= redundant predicate.=20 =0A= =0A= 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 :) =0A= =0A= =0A= Nested Loop (cost=3D0.00..3.14 rows=3D1 width=3D368) =0A= Join Filter: ("outer".username =3D "inner".username) =0A= -> Nested Loop (cost=3D0.00..2.10 rows=3D1 width=3D218) =0A= Join Filter: ("outer".username =3D "inner".username) =0A= -> Seq Scan on t1 (cost=3D0.00..1.01 rows=3D1 width=3D146) =0A= -> Seq Scan on t3 (cost=3D0.00..1.04 rows=3D4 width=3D72) =0A= -> Seq Scan on t2 (cost=3D0.00..1.02 rows=3D2 width=3D150) =0A= =0A= I am using postgresql 8.5.1, I am wondering is there is any patch that=0A= i can run to enable it to put the actual table names instead of=0A= inner/outer. Should i post this to the hackers mailing list? =0A= =0A= Thanks a lot. =0A= > To: ehabgalal123@hotmail.com > CC: pgsql-sql@postgresql.org > Subject: Re: [SQL] omitting redundant join predicate=20 > Date: Sun, 4 Nov 2007 11:35:36 -0500 > From: tgl@sss.pgh.pa.us >=20 > Ehab Galal writes: > > explain select *=20 > > from t1, t2, t3=20 > > where t1.f <=3D t2.f > > and t2.f <=3D t3.f > > and t1.f <=3D t3.f; >=20 > > I was wondering if there is a > > way to omit the redundant join predicate. >=20 > You're not being very clear here. Do you mean will you get the same > answer if you omit "t1.f <=3D 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: >=20 > QUERY PLAN =20 > ------------------------------------------------------------------ > Nested Loop (cost=3D33.54..81794021.44 rows=3D362975624 width=3D12) > Join Filter: ((t1.f <=3D t2.f) AND (t2.f <=3D t3.f)) > -> Nested Loop (cost=3D0.00..124472.40 rows=3D1526533 width=3D8) > Join Filter: (t1.f <=3D t3.f) > -> Seq Scan on t1 (cost=3D0.00..31.40 rows=3D2140 width=3D4) > -> Seq Scan on t3 (cost=3D0.00..31.40 rows=3D2140 width=3D4) > -> Materialize (cost=3D33.54..54.94 rows=3D2140 width=3D4) > -> Seq Scan on t2 (cost=3D0.00..31.40 rows=3D2140 width=3D4) > (8 rows) >=20 > 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: >=20 > QUERY PLAN =20 > ---------------------------------------------------------------------- > Nested Loop (cost=3D0.00..13222230.60 rows=3D362975624 width=3D12) > -> Nested Loop (cost=3D0.00..124472.40 rows=3D1526533 width=3D8) > Join Filter: (t1.f <=3D t3.f) > -> Seq Scan on t1 (cost=3D0.00..31.40 rows=3D2140 width=3D4) > -> Seq Scan on t3 (cost=3D0.00..31.40 rows=3D2140 width=3D4) > -> Index Scan using t2i on t2 (cost=3D0.00..5.01 rows=3D238 width=3D= 4) > Index Cond: ((t1.f <=3D t2.f) AND (t2.f <=3D t3.f)) > (7 rows) >=20 > regards, tom lane _________________________________________________________________ Help yourself to FREE treats served up daily at the Messenger Caf=E9. Stop = by today. http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid=3DTXT_TAGLM_Oc= tWLtagline= --_cd071b5c-57f6-49b9-8078-7efd18b32fc8_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Sorry for not being clear enough. What i meant is how awar= e the optimizer is about the transitivity of operators.
=0A=
=0A= I agree that the more join clauses a query gets, the more flexibility the o= ptimizer gets to pick an optimal plan.
=0A=
=0A= what i expected is that the optimizer will use the redundant predicates=0A= to create the plan, but the execution plan itself will not execute a=0A= redundant predicate.
=0A=
=0A= 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 :)
=0A=
=0A=
=0A= Nested Loop  (cost=3D0.00..3.14 rows=3D1 width=3D368)
=0A=    Join Filter: ("outer".username =3D "inner".username)
=0A=    ->  Nested Loop  (cost=3D0.00..2.10 rows=3D1 widt= h=3D218)
=0A=          Join Filter: ("outer".user= name =3D "inner".username)
=0A=          ->  Seq Scan on t1=   (cost=3D0.00..1.01 rows=3D1 width=3D146)
=0A=          ->  Seq Scan on t3=   (cost=3D0.00..1.04 rows=3D4 width=3D72)
=0A=    ->  Seq Scan on t2  (cost=3D0.00..1.02 rows=3D2 w= idth=3D150)
=0A=
=0A= I am using postgresql 8.5.1, I am wondering is there is any patch that=0A= i can run to enable it to put the actual table names instead of=0A= inner/outer. Should i post this to the hackers mailing list?
=0A=
=0A= Thanks a lot.
=0A=



> To: ehabgalal123@hotmail.com=
> CC: pgsql-sql@postgresql.org
> Subject: Re: [SQL] omitting r= edundant join predicate
> Date: Sun, 4 Nov 2007 11:35:36 -0500
&g= t; From: tgl@sss.pgh.pa.us
>
> Ehab Galal <ehabgalal123@hot= mail.com> writes:
> > explain select *
> > from t1, t= 2, t3
> > where t1.f <=3D t2.f
> > and t2.f <=3D= t3.f
> > and t1.f <=3D t3.f;
>
> > I was won= dering if there is a
> > way to omit the redundant join predicate.=
>
> You're not being very clear here. Do you mean will you g= et the same
> answer if you omit "t1.f <=3D t3.f"? Yes, of course= (ignoring possibly
> different output ordering). Do you mean you th= ink the system should
> discard it as redundant? I disagree --- the = more join clauses the
> better, as a rule. Do you mean that the EXPL= AIN 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=3D33.54..81794021.44 rows=3D36297562= 4 width=3D12)
> Join Filter: ((t1.f <=3D t2.f) AND (t2.f <= =3D t3.f))
> -> Nested Loop (cost=3D0.00..124472.40 rows=3D15= 26533 width=3D8)
> Join Filter: (t1.f <=3D t3.f)
> = -> Seq Scan on t1 (cost=3D0.00..31.40 rows=3D2140 width=3D4)<= br>> -> Seq Scan on t3 (cost=3D0.00..31.40 rows=3D2140 wid= th=3D4)
> -> Materialize (cost=3D33.54..54.94 rows=3D2140 wid= th=3D4)
> -> Seq Scan on t2 (cost=3D0.00..31.40 rows=3D= 2140 width=3D4)
> (8 rows)
>
> This is of course the stu= pidest 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=3D0.00..13222230.60 rows=3D362975624 width=3D12)
> -> N= ested Loop (cost=3D0.00..124472.40 rows=3D1526533 width=3D8)
> = Join Filter: (t1.f <=3D t3.f)
> -> Seq Scan on t1= (cost=3D0.00..31.40 rows=3D2140 width=3D4)
> -> Seq Sc= an on t3 (cost=3D0.00..31.40 rows=3D2140 width=3D4)
> -> Inde= x Scan using t2i on t2 (cost=3D0.00..5.01 rows=3D238 width=3D4)
> = Index Cond: ((t1.f <=3D t2.f) AND (t2.f <=3D t3.f))
> (7= rows)
>
> regards, tom lane

Help yourself t= o FREE treats served up daily at the Messenger Caf=E9. Stop by today! = --_cd071b5c-57f6-49b9-8078-7efd18b32fc8_--