Re: Proposal: Global Index

On 12/19/19 08:12, Bruce Momjian wrote:
> I don't see lossy BRIN indexes helping with the uniqueness use-case, and
> I am not sure they would help with the rare case either.  They would
> help for range-based partitions, but I thought our existing facilities
> worked in that case.

Correlated data.  The existing facilities work if the filtering column
is exactly the same as the partition column.  But it's not at all
uncommon to have other columns with correlated data, perhaps the most
obvious of which is timeseries tables with many date columns of various
definitions (row first update, row latest update, invoice date, payment
date, process date, ship date, etc).

What if you could use *two* indexes in a single execution plan?  Use the
global BRIN to narrow down to 2 or 3 out of a hundred or more
partitions, then use local indexes to find specific rows in the
partitions of interest?  That might work, without being too overly
complicated.

-J


-- 
Jeremy Schneider
Database Engineer
Amazon Web Services