Hello,
We're curious about the current behavior in 9.5.4, and possible future enhancements, of BRIN indexes with respect to ordering.
In the docs, section 11.4. "Indexes and ORDER BY" (https://www.postgresql.org/docs/9.5/static/indexes-ordering.html) is clear that anything other than B-tree indexes have unspecified ordering:
"In addition to simply finding the rows to be returned by a query, an index may be able to deliver them in a specific sorted order. This allows a query's ORDER BY specification to be honored without a separate sorting step. Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order."
We found a pgsql-hackers thread from about a year ago about optimizing ORDER BY for BRIN indexes. Tom Lane suggested that he was working on it: https://www.postgresql.org/message-id/11881.1443393360%40sss.pgh.pa.us
Our current test shows that ordering by a BRIN indexed column still performs an unoptimized sort:
SELECT generate_series(1, 10000000) AS id INTO test;
CREATE INDEX idx_test_id ON test USING BRIN (id);
EXPLAIN SELECT id FROM test ORDER BY id DESC LIMIT 20;
Limit (cost=410344.40..410344.45 rows=20 width=4)
-> Sort (cost=410344.40..435344.40 rows=1000000 width=4)"
Sort Key: id DESC
-> Seq Scan on test (cost=0.00..144248.00 rows=10000000 width=4)
Is there anything we're missing to speed this up? Or is it still a future feature?
Thank you,
Darren Lafreniere
