public inbox for [email protected]  

help / color / mirror / Atom feed

From: Stephen Frost <[email protected]>
To: David Harrigan <[email protected]>
Cc: [email protected]
Subject: Re: pages_in_range for BRIN index
Date: Sat, 6 Jun 2020 08:35:56 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKdGhePbMSiQ_qo718oSZTu=j1yU3ne=6kYfmCDOUwzp3+TnZQ@mail.gmail.com>
References: <CAKdGhePbMSiQ_qo718oSZTu=j1yU3ne=6kYfmCDOUwzp3+TnZQ@mail.gmail.com>

Greetings,

* David Harrigan ([email protected]) wrote:
> I'm having difficulty in trying to understand the value I should use for
> `pages_in_range` when creating a BRIN index. I have data coming in, nearly
> 3-4
> rows per second with an ever increasing `created` timestamp. Perfect for a
> BRIN index to save on space :-).

Yup, that does sound like the right use-case.

> I gathered some data (at time of query) on the table I'm interested in:
> 
> Size (bytes): 16130588672
> Pages: 1969066
> Rows: 48215073
> Rows/Page: 24

Not quite there yet, but hopefully you're thinking about partitioning
too.

> At the moment, the column I'm indexing on, created, has a BTREE index on it,
> and it's over 2GB in size!

Well, ok, sure, 2G isn't *that* big tho. :)

> What would I need to do to go about choosing an appropriate value for
> pages_in_range?

Ok, so, this really depends on what queries you're running and how many
rows you want to have to scan for them.  If you're mostly doing 'bulk'
kinds of queries that hit a lot of rows, then having a larger value is
probably fine since you won't be going through that many rows that you
have to throw away.  On the other hand, if the queries that are using
this index are just getting back a couple of rows as a result, then you
probably want a smaller value, to make those queries be fast.

Of course, the tradeoff here is in the size of the index- a smaller
number means a larger index, and a larger number means a smaller index.

If you're not sure though- try the default..?

> Lastly, is it necessary to run a `vacuum analyse freeze` before/after
> index creation?

Nope.

Thanks,

Stephen


Attachments:

  [application/pgp-signature] signature.asc (819B, ../[email protected]/2-signature.asc)
  download

view thread (5+ messages)  latest in thread

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: pages_in_range for BRIN index
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox