On Thu, Oct 10, 2019 at 6:22 PM David Rowley <david.row...@2ndquadrant.com> wrote:
> The planner might be able to get a better estimate on the number of > matching rows if the now() - interval '10 days' expression was > replaced with 'now'::timestamptz - interval '10 days'. However, care > would need to be taken to ensure the plan is never prepared since > 'now' is evaluated during parse. The same care must be taken when > creating views, functions, stored procedures and the like. > > 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';
