Re: Proposal: Global Index

On Thu, Dec 19, 2019 at 11:28:55AM -0800, Jeremy Schneider wrote:
> 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.

No, that is very interesting --- having secondary indexes for
partitioned tables that trim most partitions. Would index lookups on
each partition index be very slow? BRIN indexes? I am assuming global
indexes would only avoid such lookups.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +