Re: BRIN index on timestamptz

[Mohan Radhakrishnan <radhakrishnan.mohan@xxxxxxxxx>]

>a) You need to do ANALYZE, otherwise >there are no statistics the optimizer >could use

I execute and analyze. The actual timestamps I have are not random. I will order them chronologically.

Thanks

On Saturday, April 24, 2021, Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxxx> wrote:

On 4/23/21 10:31 AM, Mohan Radhakrishnan wrote:

Hi,
          I am planning to use as I search based on timestamptz fields. There are millions of records.I refer https://www.percona.com/blog/2019/07/16/brin-index-for-postgresql-dont-forget-the-benefits <https://www.percona.com/blog/2019/07/16/brin-index-for-postgresql-dont-forget-the-benefits>

I execute this on the AWS RDS instance. Is there something in the plan I should pay attention to ? I notice the Execution Time.

Thanks,
Mohan

INSERT INTO testtab (id, date, level, msg) SELECT g, CURRENT_TIMESTAMP + ( g || 'minute' ) :: interval, random() * 6, md5(g::text) FROM generate_series(1,8000000) as g;

Two things:

a) You need to do ANALYZE, otherwise there are no statistics the optimizer could use (which is why the row estimates in the plans are entirely bogus).

b) BRIN indexes don't work on random data, because the whole idea is about eliminating large blocks of data (e.g. 1MB). But with random data that's not going to happen, because each such range will match anything. Which is why seqscan is a bit faster than when using BRIN index.


regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company