Re: Understanding BRIN index performance

On 3 October 2016 at 11:40, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On 3 October 2016 at 10:00, Ivan Voras <ivoras(at)gmail(dot)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?