Re: BRIN index on timestamptz

On 2021-04-26 17:23:49 +0530, Mohan Radhakrishnan wrote:
> Isn't a btree subject to these effects ? So when I update ENUMS for each
> timestamptz, btree indexes are less susceptible
> to the effects than BRIN indexes  ?

A btree index contains one entry for each record which points to that
records. If you select a small range of values via a btree index in the
worst case you will have one random seek per row. This is not ideal, but
doesn't matter much if the number of records is low.

A BRIN index contains a minimum and maximum value per range of blocks.
In the worst case (each block range contains a totally random sample of
values) the minimum for each block range will be near the minimum of the
whole table and the maximum of each block range will be near the maximum
for the whole table. So when searching, the BRIN index will exclude very
few block ranges.

So a BRIN index will work best when each block range contains only a
small range of indexed values.

If you index on a timestamp this will work nicely if you either don't
update rows at all after inserting them or only update them for a short
time relative to the total time spanned by the table. So if your table
contains say records from the last year and records are normally only
updated after one or two days after being created that would probably
still work quite well. If there is a substantial number of records which
is still updated after a year, it probably won't work at all.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"