| From: | Jeremy Finzel <finzelj(at)gmail(dot)com> | 
|---|---|
| To: | Postgres General <pgsql-general(at)postgresql(dot)org> | 
| Subject: | Aggregate not using BRIN index on timestamp | 
| Date: | 2019-08-05 13:45:47 | 
| Message-ID: | CAMa1XUhGWTdDEx51RGZU7pU6T77QUtY-fn1HjYB9cGb2B=yrTQ@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hello -
I have started to make much more use of BRIN indexes on timestamp fields on
tables which are insert-only.  I have seen great performance with these and
of course far less overhead.
However, I am noticing that a simple aggregate is not using the index.  I
don't find anything obvious in the docs as to why, and I am not sure if the
operator is not actually supported, or for some reason it is not choosing
it because of the estimate.
I have a very large table with 4 billion rows and a BRIN index on timestamp
spanning from 2013 to present.  I am running this simple query:
SELECT MIN(created_at) FROM table;
It is choosing a parallel seq scan as opposed to a BRIN bitmap scan.
Please note also that the following queries that I am using are using the
index with great performance:
SELECT * FROM table WHERE created_at > '2013-04-01' AND created_at <=
'2013-04-08';
I can provide more info. But first - am I missing something obvious?
Thanks,
Jeremy
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2019-08-05 13:51:38 | Re: why toasted is created when domain is used ? | 
| Previous Message | Kenneth Marshall | 2019-08-05 13:27:18 | Re: Compression In Postgresql 9.6 |