| From: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
|---|---|
| To: | Andrey Klochkov <diggerk(at)gmail(dot)com> |
| Cc: | Francisco Olarte <folarte(at)peoplecall(dot)com>, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Do BRIN indexes support MIN/MAX? |
| Date: | 2023-03-30 04:02:44 |
| Message-ID: | CAOBaU_Y8Hs78kihL+zNEKAj5CkypwNnZO1e5tArnyUSkUa5_Pg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Thu, 30 Mar 2023, 05:03 Andrey Klochkov, <diggerk(at)gmail(dot)com> wrote:
> BRIN indexes seem to work perfectly well for our purposes, and they are so
> tiny compared to B-Tree. Selecting min/max values is very expensive though.
>
> In my case the table is ~2.5TB (530M records), while the whole BRIN index
> is 16MB. I think it'd be totally fine to scan all BRIN pages, it'd be way
> better than doing table scan.
>
brin indexes don't work the way you would hope for. the stored min/max
values per range guarantees that all values in the underlying relation
pages are contained in that range, but it doesn't mean that those min/max
values are still present in the table, so you can't deduce in which range
the current min or max value is from there.
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2023-03-30 04:18:28 | Re: Do BRIN indexes support MIN/MAX? |
| Previous Message | Vladimir Sitnikov | 2023-03-30 03:58:25 | Re: Do BRIN indexes support MIN/MAX? |