Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 06F499F9584 for ; Sun, 4 Nov 2007 12:35:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 66183-01 for ; Sun, 4 Nov 2007 12:35:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C494B9F94A2 for ; Sun, 4 Nov 2007 12:35:37 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lA4GZaMx014451; Sun, 4 Nov 2007 11:35:36 -0500 (EST) To: Ehab Galal cc: "pgsql-sql@postgresql.org" Subject: Re: omitting redundant join predicate In-reply-to: References: Comments: In-reply-to Ehab Galal message dated "Sun, 04 Nov 2007 08:14:51 -0500" Date: Sun, 04 Nov 2007 11:35:36 -0500 Message-ID: <14450.1194194136@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/5 X-Sequence-Number: 29533 Ehab Galal 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