pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (9+ messages)  latest in thread

Message-ID: <d0043a0b-29a1-8c83-6ea9-af0b2f1d6a25@gmail.com>
Permalink:  ../d0043a0b-29a1-8c83-6ea9-af0b2f1d6a25@gmail.com/
Also on:    postgresql.org/message-id/d0043a0b-29a1-8c83-6ea9-af0b2f1d6a25@gmail.com

 · 

reply

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