I don't think a BRIN index would help in either case.
BRIN just marks each page with a max and min boundaries which are helpful in where clauses and has nothing to do with ordering.
For the first operation i.e Max a btree index would do an index scan backward which is just an index lookup in reverse and for order by it can use the index as well since a btree index is ordered by default.
That is the reason why it switches to a sequential scan since there is no way for a BRIN index to be used in the case of a max / order by.
On Mon, Oct 3, 2016 at 2:30 PM, Ivan Voras <ivoras@gmail.com> wrote:
Hi,
I have a table of around 20 G, more than 220 million records, and I'm running this query on it:
explain analyze SELECT MAX(id) - (SELECT id FROM expl_transactions WHERE dateAdded < (now() - INTERVAL '10 MINUTES') ORDER BY dateAdded DESC LIMIT 1) FROM expl_transactions;
"id" is SERIAL, "dateAdded" is timestamp without timezone
The "dateAdded" field also has a "default now()" applied to it some time after its creation, and a fair amount of null values in the records (which I don't think matters for this query, but maybe I'm wrong).
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:
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:
