Re: Understanding BRIN index performance

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

> On 3 October 2016 at 10:58, Ivan Voras <ivoras(at)gmail(dot)com> wrote:
>
> > 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?
>
> That could be most simply explained if the distribution of your data
> is not what you think it is.
>

Something doesn't add up.
I've clustered the table, then created a BRIN index, and the number of rows
resulting from the index scan dropped only very slightly.

Hmmm, looking at your original reply about the metadata, and my query, did
you mean something like this:

SELECT id FROM expl_transactions WHERE dateAdded < (now() - INTERVAL '10
MINUTES') ORDER BY dateAdded DESC LIMIT 1

To solve this with a BRIN index, the index records (range pairs?)
themselves would need to be ordered, to be able to perform the "ORDER by
... DESC" operation with the index, and then sort it and take the single
record from this operation, and there is currently no such data being
recorded?