Generalizing range-constraint detection in clauselist_selectivity - Mailing list pgsql-hackers

From Tom Lane
Subject Generalizing range-constraint detection in clauselist_selectivity
Date
Msg-id 17655.1348874742@sss.pgh.pa.us
Whole thread Raw
Responses Re: Generalizing range-constraint detection in clauselist_selectivity
List 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_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



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: embedded list v2
Next
From: Tom Lane
Date:
Subject: Re: embedded list v2