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
