On Fri, 11 Oct 2019 at 12:13, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > The index scan is estimated to return 157328135 rows, i.e. about 50% of > the table (apparently it's ~10x more than the actual number).
Don't pay too much attention to the actual row counts from bitmap index scans of brin indexes. The value is entirely made up, per: /* * XXX We have an approximation of the number of *pages* that our scan * returns, but we don't have a precise idea of the number of heap tuples * involved. */ return totalpages * 10; in bringetbitmap(). (Ideally EXPLAIN would be written in such a way that it didn't even show the actual rows for node types that don't return rows. However, I'm sure that would break many explain parsing tools) 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. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
