From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: How to use the BRIN index properly?
Date: Wed, 8 Feb 2023 14:27:29 -0600
Message-ID: <d0043a0b-29a1-8c83-6ea9-af0b2f1d6a25@gmail.com> (raw)
In-Reply-To: <CAPqV3pQCPas3CzMx-Syed_-Kzn_Vsq=b=H=_SgoXbFW58-mmWA@mail.gmail.com>
References: <CAPqV3pRdiDEiNiHmH82N-eWPVsKKHQ=EUn0e7Zu0+EJCW7GbQA@mail.gmail.com>
<CAPqV3pQCPas3CzMx-Syed_-Kzn_Vsq=b=H=_SgoXbFW58-mmWA@mail.gmail.com>
Is the data in your tables stored in natural correlation with those *three*
columns? I'm dubious that can even happen.
BRIN is best for *range queries* on tables who's data is added in the same
order as the key in the BRIN index (for example, a BRIN index on a timestamp
field in a log table where new records are always being appended in
"timestamp" order).
It would also be great for history tables where you can pre-sort the data
by, for example, customer_id, and then put the BRIN on customer_id.
On 2/8/23 13:58, Siddharth Jain wrote:
> our insertion order is of course != index order otherwise the question
> would have been trivial.
> we use postgres 14
>
> On Wed, Feb 8, 2023 at 11:51 AM Siddharth Jain <siddhsql@gmail.com> wrote:
>
> Hello,
>
> We have large tables with billions of rows in them and want to take
> advantage of the BRIN index on them.
>
> Issues we are facing:
>
> * as I understand, BRIN index is useful only if the data is stored
> in index order. As an example we want to create a composite BRIN
> index on 3 columns - integers and strings (varchar). How can we
> tell Postgres to store data in index order as new records are
> inserted into the database?
> * i understand that turning on autosummarize will keep the index
> fresh and up-to-date as new records are inserted. is this correct?
>
> Thanks for your help.
>
> S.
>
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: How to use the BRIN index properly?
In-Reply-To: <d0043a0b-29a1-8c83-6ea9-af0b2f1d6a25@gmail.com>
References: <CAPqV3pRdiDEiNiHmH82N-eWPVsKKHQ=EUn0e7Zu0+EJCW7GbQA@mail.gmail.com> <CAPqV3pQCPas3CzMx-Syed_-Kzn_Vsq=b=H=_SgoXbFW58-mmWA@mail.gmail.com> <d0043a0b-29a1-8c83-6ea9-af0b2f1d6a25@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox