Re: Aggregate not using BRIN index on timestamp

On 2019-Aug-05, Jeremy Finzel wrote:

> Thanks Tom. So, this is a very general question, but would it be possible
> to develop that feature into BRIN, given what it stores? Even if it does
> not have ordering information, doesn't it know which blocks would contain
> the lowest values, so it could choose to do a "bitmap scan ordered sort" or
> something, depending on the number of rows sought? Or is the problem that
> it has no way of determining what value actually would be the "minimum"
> without the query specifying a particular date, such as less than
> "2013-04-01"?

For btrees, we have planagg.c which transforms min() and max() into
subqueries (SELECT .. WHERE ... ORDER BY .. LIMIT 1).

In a BRIN index, you could execute the search by scanning the index to
determine which ranges contain the least/greatest values, and then using
a bitmap scan to scan those. I'm not sure that this is a transformation
that can be applied cleanly, since that thing I describe doesn't look to
be a "subquery". But maybe it can -- I think you'd need a special
executor node.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services