Over in pgsql-performance, Shaun Thomas was just complaining about the
planner not picking a bitmap indexscan for a query involving a
constraint like
b.created_dt between a.created_dtand a.created_dt + interval '1 month';
At first I wrote this off as being due to inability to get a good
selectivity estimate, but on second look it seemed like even with the
default estimate for a range constraint, the planner should've made the
choice he wanted. After a bit of digging I realized that it wasn't
recognizing this as a range constraint on b.created_dt at all, because
the code in clauselist_selectivity that tries to pair up inequality
constraints punts altogether for anything involving a join --- it only
wants to look at "var >= constant" types of clauses:
* See if it looks like a restriction clause with a pseudoconstant on * one side. (Anything more
complicatedthan that might not behave in * the simple way we are expecting.)
I'm thinking that this is overly restrictive, and we could usefully
suppose that "var >= anything" and "var <= anything" should be treated
as a range constraint pair if the vars match and there are no volatile
functions in the expressions. We are only trying to get a selectivity
estimate here, so rigorous correctness is not required. However, I'm
a little worried that I might be overlooking cases where this would be
unduly optimistic. Does anyone see a situation where such a pair of
clauses *shouldn't* be thought to be a range constraint on the var?
For instance, should we still restrict the "var" side to be an
expression in columns of only one relation?
regards, tom lane