Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-06 Thread Merlin Moncure
On Wed, Oct 5, 2016 at 3:27 PM, Stephen Frost wrote: > Darren, > > * Darren Lafreniere (dlafreni...@onezero.com) wrote: >> Tom Lane wrote: >> > > Gavin Wahl wrote: >> > >> It seems trivial to accelerate a MAX or MIN query with a BRIN index. You >> > >>

Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Darren Lafreniere
Stephen Frost wrote: > For at least some of the common BRIN use-cases, where the rows are > inserted in-order and never/very-rarely modified or deleted, this > approach would work very well. > Thanks Stephen, this is exactly our use case.

Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Stephen Frost
Darren, * Darren Lafreniere (dlafreni...@onezero.com) wrote: > Tom Lane wrote: > > > Gavin Wahl wrote: > > >> It seems trivial to accelerate a MAX or MIN query with a BRIN index. You > > >> just find the page range with the largest/smallest value, and then only > > >> scan

Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Darren Lafreniere
Tom Lane wrote: > > Gavin Wahl wrote: > >> It seems trivial to accelerate a MAX or MIN query with a BRIN index. You > >> just find the page range with the largest/smallest value, and then only > >> scan that one. Would that be hard to implement? I'm interested in > working >

Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Tom Lane
Alvaro Herrera writes: > Darren Lafreniere wrote: >> 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: >>

Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Darren Lafreniere
Ahh, yes. I misread that. Thank you for the clarification. On Wed, Oct 5, 2016 at 2:27 PM, Alvaro Herrera wrote: > Darren Lafreniere wrote: > > > "In addition to simply finding the rows to be returned by a query, an > index > > may be able to deliver them in a specific

Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Alvaro Herrera
Darren Lafreniere wrote: > "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

[GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Darren Lafreniere
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