Re: Proposal: Global Index

On 19/12/19 4:03, Bruce Momjian wrote:
> On Mon, Nov 25, 2019 at 03:44:39PM -0800, Jeremy Schneider wrote:
>> On 11/25/19 15:05, Jeremy Schneider wrote:
>>> ... the cost of doing the individual index lookups across 180
>>> partitions (and 180 indexes) was very high, so they stored max and min
>>> txn id per partition and would generate a query with all the dates that
>>> a txn id could have been in so that only a small number of partition
>>> indexes would be accessed.
>>>
>>> .. If we are looking for higher concurrency, we can usually
>>> add a hack/workaround that filters on a partition key to provide “pretty
>>> good” pruning. The net result is that you get 2-3x the IO due to the
>>> lack of global index (same workaround as first story above).
>> Is that basically like a global BRIN index with granularity at the
>> partition level?
> Exactly! :-)

Actually, one "kind of" BRIN index *per partitioned table* mapping (key
range) -> (partition oid)... and so concurrency doesn't need to be very
affected.

(we don't need to do things just like other RDBMS do, ya know... ;)

IIRC, this precise approach was suggested around 2016 when initially
discussing the "declarative partitioning" which originated Postgres'
current partitioning scheme, in order to optimize partition pruning.

Just my .02€

    / J.L.