From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | Gavin Wahl <gavinwahl(at)gmail(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: BRIN indexes for MAX, MIN, ORDER BY? |
Date: | 2015-09-28 14:28:19 |
Message-ID: | CABRT9RCzgCYTqF6cyXMQxP_DMkyN+vVv7hCpmTHhUKVwyV_GOw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Gavin
Note that Alexander Korotkov already started work in 2013 on a
somewhat similar feature called partial sort:
http://www.postgresql.org/message-id/CAPpHfdscOX5an71nHd8WSUH6GNOCf=V7wgDaTXdDd9=goN-gfA@mail.gmail.com
In particular, see the 2nd patch for KNN sort -- it uses known
bounding boxes from the GiST index for sorting, which in many ways is
similar to min/max BRIN ranges.
> *partial-knn-1.patch*
>
> KNN-GiST provides ability to get ordered results from index, but this order
> is based only on index information. For instance, GiST index contains
> bounding rectangles for polygons, and we can't get exact distance to
> polygon from index (similar situation is in PostGIS). In attached patch,
> GiST distance method can set recheck flag (similar to consistent method).
> This flag means that distance method returned lower bound of distance and
> we should recheck it from heap.
Unfortunatley this work has stalled.
Regards,
Marti
On Sun, Sep 27, 2015 at 11:58 PM, Gavin Wahl <gavinwahl(at)gmail(dot)com> 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 on it
> if someone can give me some pointers.
>
> Somewhat harder but still possible would be using BRIN indexes to accelerate
> ORDER BY. This would require a sorting algorithm that can take advantage of
> mostly-sorted inputs. You would sort the page ranges by their minimum or
> maximum value, then feed the sorting algorithm in that order.
From | Date | Subject | |
---|---|---|---|
Next Message | zhangjinyu | 2015-09-28 14:40:54 | Re: Patch: Optimize memory allocation in function 'bringetbitmap' |
Previous Message | Shulgin, Oleksandr | 2015-09-28 14:24:33 | Re: On-demand running query plans using auto_explain and signals |