On Thu, 30 Mar 2023 at 17:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Julien Rouhaud <rjuju123@gmail.com> writes:
> > brin indexes don't work the way you would hope for. the stored min/max
> > values per range guarantees that all values in the underlying relation
> > pages are contained in that range, but it doesn't mean that those min/max
> > values are still present in the table, so you can't deduce in which range
> > the current min or max value is from there.
>
> Yeah. You could for example (when looking for a MAX) skip scanning
> block ranges whose indexed MAX is less than the indexed MIN of some
> other block range. But unless the column you are interested in is
> pretty well correlated with physical storage order, that seems
> unlikely to buy much.
I might be missing something obvious here, but as I understand it,
there's not really any API to ask an index AM what the maximum or
minimum indexed value is. There's only amcanorder == true indexes that
could give you values starting with the highest or lowest depending on
if it's a forward or backward index scan.
Tomas is doing some work in https://commitfest.postgresql.org/42/3949/
which I imagined would have allowed index scans of BRIN indexes. Which
I imagined would lead to allowing the MIN/MAX aggregates to
effectively be rewritten to effectively be executed as SELECT <col>
FROM <table> ORDER BY col LIMIT 1, as is currently done in
build_minmax_path(). I'd assume the way to make this work with BRIN
would be to allow ordered scans by first splitting all ranges into
non-overlapping sets and then sorting tuples from each of those range
sets in batches. Of course, that would only be efficient when ranges
were reasonably not overlapping each other.
(Glancing at Tomas's patch, I was surprised to see it didn't set
amcanorder to true, so I'm a little unsure how that patch is adding
more usable optimisations which the planner can make use of.)
David
