Re: BRIN index on timestamptz

>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(dot)vondra(at)enterprisedb(dot)com>
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/2
>> 019/07/16/brin-index-for-postgresql-dont-forget-the-benefits <
>> https://www.percona.com/blog/2019/07/16/brin-index-for-post
>> gresql-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
>