Aggregate not using BRIN index on timestamp
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
Jeremy Finzel <finzelj@gmail.com> writes:
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.
I can provide more info. But first - am I missing something obvious?
Yes: BRIN indexes don't provide any ordering information. A btree
index on created_at could be used to optimize this query, but without
one of those, seqscanning the whole table is the only possibility.
regards, tom lane
Yes: BRIN indexes don't provide any ordering information. A btree
index on created_at could be used to optimize this query, but without
one of those, seqscanning the whole table is the only possibility.
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"?
Thanks!
Jeremy
Jeremy Finzel <finzelj@gmail.com> writes:
Thanks Tom. So, this is a very general question, but would it be possible
to develop that feature into BRIN, given what it stores?
You'd need somebody who knows more about BRIN than me to opine on that.
regards, tom lane
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
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
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.
FWIW, I suspect the hard part would be dealing with cases where the
extremal ranges (according to the index) contain no live tuples
(according to the query's snapshot). The btree case handles the
invisible-tuples problem by continuing a scan started at the index
endpoint until it finds a visible tuple --- which, in the worst case,
can take a long time. It's not obvious to me what you'd do with
BRIN.
regards, tom lane
On 2019-Aug-05, Tom Lane wrote:
FWIW, I suspect the hard part would be dealing with cases where the
extremal ranges (according to the index) contain no live tuples
(according to the query's snapshot). The btree case handles the
invisible-tuples problem by continuing a scan started at the index
endpoint until it finds a visible tuple --- which, in the worst case,
can take a long time. It's not obvious to me what you'd do with
BRIN.
Hmm, yeah, that's a tough problem -- hadn't thought about that.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services