On Fri, 11 Oct 2019 at 17:48, Michael Lewis <mle...@entrata.com> wrote:
> On Thu, Oct 10, 2019 at 6:22 PM David Rowley <david.row...@2ndquadrant.com> 
> wrote:
>> The planner will just estimate the selectivity of now() - interval '10
>> days'  by using DEFAULT_INEQ_SEL, which is 0.3333333333333333, so it
>> thinks it'll get 1/3rd of the table.  Using 'now' will allow the
>> planner to lookup actual statistics on that column which will likely
>> give a much better estimate, which by the looks of it, likely will
>> result in one of those BRIN index being used.
> This surprised me a bit, and would have significant implications. I tested a 
> few different tables in our system and get the same row count estimate with 
> either WHERE condition. Perhaps I am missing a critical piece of what you 
> said.
> explain
> select * from charges where posted_on > now() - interval '10 days';
> explain
> select * from charges where posted_on > 'now'::timestamptz  - interval '10 
> days';

You're right. On looking more closely at the code, it uses
estimate_expression_value(), which performs additional constant
folding of expressions for selectivity purposes only. It does end up
calling the now() function and evaluating the now() - interval '10
days'; expression into a Const.

The header comment for that function reads:

* estimate_expression_value
 * This function attempts to estimate the value of an expression for
 * planning purposes.  It is in essence a more aggressive version of
 * eval_const_expressions(): we will perform constant reductions that are
 * not necessarily 100% safe, but are reasonable for estimation purposes.

So I take back what I said about using 'now'::timestamptz instead of now().

 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply via email to