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
