On 3 October 2016 at 10:00, Ivan Voras <ivoras@gmail.com> wrote:
> My first idea is to create a default BRIN index on dateAdded since the above > query is not run frequently. To my surprise, the planner refused to use the > index and used sequential scan instead. When I forced sequential scanning > off, I got this: > > https://explain.depesz.com/s/W8oo > > The query was executing for 40+ seconds. It seems like the "index scan" on > it returns nearly 9% of the table, 25 mil rows. Since the data in dateAdded > actually is sequential and fairly selective (having now() as the default > over a long period of time), this surprises me. > > With a normal btree index, of course, it runs fine: > > https://explain.depesz.com/s/TB5
Btree retains ordering, BRIN does not.
We've discussed optimizing the sort based upon BRIN metadata, but that's not implemented yet.
I get that, my question was more about why the index scan returned 25 mil rows, when the pages are sequentially filled by timestamps? In my understading of BRIN, it should have returned a small number of pages which would have been filtered (and sorted) for the exact data, right?