Re: need to repeat the same condition on joined tables in order to choose the proper plan

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