From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Cc: | sthomas(at)optionshouse(dot)com |
Subject: | Generalizing range-constraint detection in clauselist_selectivity |
Date: | 2012-09-28 23:25:42 |
Message-ID: | 17655.1348874742@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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_dt
and 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 complicated than 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-09-28 23:39:03 | Re: embedded list v2 |
Previous Message | Andres Freund | 2012-09-28 22:48:43 | Re: embedded list v2 |