Re: BRIN index which is much faster never chosen by planner

On Fri, 11 Oct 2019 at 17:48, Michael Lewis <mlewis(at)entrata(dot)com> wrote:
>
> On Thu, Oct 10, 2019 at 6:22 PM David Rowley <david(dot)rowley(at)2ndquadrant(dot)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