BRIN indexes and ORDER BY - Mailing list pgsql-general

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