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@sss.pgh.pa.us> 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
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