From: | Darren Lafreniere <dlafreniere(at)onezero(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | BRIN indexes and ORDER BY |
Date: | 2016-10-05 18:20:06 |
Message-ID: | CABoC1=69eJsWAgVz+ErfLmnorpqjw2ac+X59xjKcZ9gXMotdzA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2016-10-05 18:27:53 | Re: BRIN indexes and ORDER BY |
Previous Message | Aleksander Alekseev | 2016-10-05 15:58:37 | Re: ZSON, PostgreSQL extension for compressing JSONB |