Hi!
I am trying to understand the behaviour of the query planner regarding the push-down of the conditions "through" the join.
Lets say that I have tables a(adate date, aval text) and b(bdate date, bval text), and I create a view:
create view v as
select a.adate, a.aval, b.bval from a join b on (a.adate = b.bdate);
Now, when I do (explain select * from v where adate='2021-05-12') I can see that condition (= '2021-05-12') is used by the planned for table access to both a and b.
However, if I use range-like condition (this is probably not a correct terminology, but I am not familiar with the correct one) like BETWEEN or (>='2021-05-21'), I will see that planner will use this condition to access a, but not b. It seems that the type of join (inner or left) does not really matter.
In my experiments, I was never able to get an execution plan that "pushes down" any condition apart from (=) through to the right side of the join, which is rather surprising and leads to suboptimal planner estimates and execution plans whenever view like the above is a part of a bigger query with more joins on top.
Equally surprising is that I was unable to find documentation or past mailing list discussions of this or similar topic, which leads me to believe that I am just not familiar with the proper terminology and can't come up with the right search terms.
Can you please tell me what is the proper way to describe this behaviour/phenomenon (so that I can use it as search terms) and/or provide me with references to the parts of the source code that determines which conditions would be "pushed down" and which are not?
PS As far as I can see, this behaviour is consistent between versions 9.5, 10, 11, 12 and 13.
--