On 3 October 2016 at 10:58, Ivan Voras <ivoras@gmail.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?