To: pgsql-performance@postgresql.org
From: Robert Klemme <shortcutter@googlemail.com>
Subject: Re: need to repeat the same condition on joined tables in order to
	choose the proper plan
Date: Tue, 14 Jun 2011 22:21:17 +0200
Lines: 33
Message-ID: <it8fs4$vlh$1@dough.gmane.org>
References: <BANLkTikSqgoucZkO0F3v8pHCopXjR=+NYg@mail.gmail.com>
	<676.1308068945@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US;
	rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
In-Reply-To: <676.1308068945@sss.pgh.pa.us>

On 14.06.2011 18:29, Tom Lane wrote:
> Svetlin Manavski<svetlin.manavski@gmail.com>  writes:
>> I am really surprised to see that the planner needs me to explicitly specify
>> the same condition twice like this:
>
>>      SD.detectorid = SS.detectorid
>>      and SD.sessionid = SS.id
>>      and SD.detectorid = 1
>>      and SD.sessionid>= 1001000000000::INT8 and SD.sessionid<=
>> 2001000000000::INT8
>>      and SS.detectorid = 1
>>      and SS.id>= 1001000000000::INT8 and SS.id<= 2001000000000::INT8
>
> The planner does infer implied equalities, eg, given A = B and B = C
> it will figure out that A = C.  What you are asking is for it to derive
> inequalities, eg infer A<  C from A = B and B<  C.  That would be
> considerably more work for considerably less reward, since the sort of
> situation where this is helpful doesn't come up very often.  On balance
> I don't believe it's a good thing for us to do: I think it would make
> PG slower on average because on most queries it would just waste time
> looking for this sort of situation.
>
> (In this example, the SS.detectorid = 1 clause is in fact unnecessary,
> since the planner will infer it from SD.detectorid = SS.detectorid and
> SD.detectorid = 1.  But it won't infer the range conditions on SS.id
> from the range conditions on SD.sessionid or vice versa.)

Is that the same for IN?  Would it help in this particular case to use a 
and SS.id in (select ... where ... > and ... < ...) or with a CTE?

Kind regards

	robert