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.
