From: Aaron Birkland <apb18@cornell.edu>
To: Jonathan Blitz <jb@anykey.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: Propagating outer join conditions
Date: Sun, 3 Dec 2006 11:30:28 -0500
Message-ID: <19ab0ccd0612030830t68d9a7ddoba02f060fd9cdea4@mail.gmail.com> (raw)
In-Reply-To: <021f01c716ef$3f5701d0$647ba8c0@jonathanlaptop>
References: <19ab0ccd0612030711s70a3e095hceb69509cbd41597@mail.gmail.com>
<021f01c716ef$3f5701d0$647ba8c0@jonathanlaptop>
First, I forgot to mention - this is 8.2 RC1 I was trying on
The suggested change produces an identical 'bad' query plan. The main
issue (I think) is that the query node that processes "t1 JOIN t11 ON
..' is not aware of the join condition 't28.s = t1.s'.. even though
the value of t28.s (as determined by the inner index scan where t28.o
= 'spec') could(should?) theoretically be known to it. If it did, then
I imagine it would realize that a nested loop join starting with t1.s
= t28.s (which is very selective) would be much cheaper than doing the
big merge join.
-Aaron
On 12/3/06, Jonathan Blitz <jb@anykey.co.il> wrote:
> How about trying:
>
> Select *
> From
> (Select * from t28 where t28.0='spec') t28a
> Left out join (t1 JOIN t11 ON
> > (t11.o = '<http://example.org>'; AND t11.s = t1.o)) ON t28a.s = t1.s
>
> In this way, I think, the where clause on t28 would be performed before the
> join rather than after.
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-performance@postgresql.org
Cc: apb18@cornell.edu, jb@anykey.co.il
Subject: Re: Propagating outer join conditions
In-Reply-To: <19ab0ccd0612030830t68d9a7ddoba02f060fd9cdea4@mail.gmail.com>
* 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