Re: pages_in_range for BRIN index

Greetings,

* David Harrigan (dharrigan@gmail.com) 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