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

Thanks Tom, this explain the behavior. But is there a more elegant way to
achieve the only acceptable plan (index scan on both tables) without that
ugly syntax? It does seem to me like a specific syntax to help the current
postgressql planner make the right decision. ( I am aware about the radical
solutions which impact the rest of the connection or the entire DB )

As it comes to the generic case, I do understand deriving inequalities may
be inefficient. I just want to point out that this is the case of joining
and filtering on a field, which is the foreign key in one table and the
primary key in the other. That should be massively common in every non
trivial DB application. Maybe it does make sense to consider that specific
case in the planner, doesn't it?

Thank you,
Svetlin Manavski

On Tue, Jun 14, 2011 at 5:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.)
>
> regards, tom lane
>