Re: Proposal: Global Index - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Proposal: Global Index
Date
Msg-id 20191220023433.GA29807@momjian.us
Whole thread Raw
In response to Re: Proposal: Global Index  (Jeremy Schneider <schnjere@amazon.com>)
List pgsql-hackers
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@momjian.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 +



pgsql-hackers by date:

Previous
From: Craig Ringer
Date:
Subject: Re: Read Uncommitted
Next
From: "tsunakawa.takay@fujitsu.com"
Date:
Subject: RE: Hooks for session start and end, take two