Date: Sat, 6 Jun 2020 08:35:56 -0400
From: Stephen Frost <sfrost@snowman.net>
To: David Harrigan <dharrigan@gmail.com>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: pages_in_range for BRIN index
Message-ID: <20200606123556.GV6680@tamriel.snowman.net>
References: 
 <CAKdGhePbMSiQ_qo718oSZTu=j1yU3ne=6kYfmCDOUwzp3+TnZQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="RBdmpsAEjcqDYAXp"
Content-Disposition: inline
In-Reply-To: 
 <CAKdGhePbMSiQ_qo718oSZTu=j1yU3ne=6kYfmCDOUwzp3+TnZQ@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--RBdmpsAEjcqDYAXp
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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:
>=20
> 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

--RBdmpsAEjcqDYAXp
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJe242sAAoJEO1sijiDR2RVKL0P/3hagcHZwMm60GB22mMFEesj
hmNsTBqu84jAvVaZRDmiC/z6QwNS+2PWHkL9vj2CrWUWyHI+SbFTYn5iYOweNLcU
wNzbTguOH+IKp4AeyFe7NEKh8iKP+4Aa7ZeKBYCKDOAvSaalMuYOHZRE9xMdRnfa
MhnmKfRvCzusWEsCHgyVgbfZV+U0RVTrq16mzewRjpDKdz9phdUKu2Ds9BOJgB7h
0uATVvnTvmsjBeUgI5Ck4mQzJyKuzNcNf6iaLj963pv3HD4W/LQtOWO/0oKB4oqz
qsAkj1QI/vkXut5vTyPqUvlND8GSn2UTRZgCkAcM1OLMkFwnY/eT9yipDdSXDu+w
53M9qIHwAD0IhDLCL9nRVFxGGFYme1mEvQ+0tHXk4tmWQrhMSsrPSkzEPIj00UHo
VRD18j/lxCC65Dpa7lRuK+3r7vKAj7Bwa0kxcEeJq94RqUimxOXfrnFAj7+7hF+2
aRfl+wro4yLTwP6/9LVx6AopKRw7DK3pg9yvpb+FaPGt21zKucwIzIwrfJ7TKQke
NrkFpTceGwBrhKqxEFM6FGY3MtGB2cZhaljBZWFZfEhkdWixKXuDchPF6MOBZTuI
F4+hNbBrUCSYp/BAoDCgpEnubZnMcTJYI6HXoKyjpC1XTpNFK6MqerC92AT8jHUO
6Ghe8yCJUmsBMWLuvrX1
=QJ6V
-----END PGP SIGNATURE-----

--RBdmpsAEjcqDYAXp--