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(dot)manavski(at)gmail(dot)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