From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ehab Galal <ehabgalal123@hotmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: omitting redundant join predicate
Date: Sun, 04 Nov 2007 11:35:36 -0500
Message-ID: <14450.1194194136@sss.pgh.pa.us> (raw)
In-Reply-To: <BAY138-W43A1A93DB7B84F57B84211968F0@phx.gbl>
References: <BAY138-W43A1A93DB7B84F57B84211968F0@phx.gbl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, ehabgalal123@hotmail.com
Subject: Re: omitting redundant join predicate
In-Reply-To: <14450.1194194136@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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