Aggregate not using BRIN index on timestamp

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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