>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.von...@enterprisedb.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 >
