amitlan

### Keybase proof

I hereby claim:

  * I am amitlan on github.
  * I am amitlan (https://keybase.io/amitlan) on keybase.
  * I have a public key ASBrTN93IP7G3arOBGHIzS9R-5j8ZUfwzLLOscE-mmEnGgo

To claim this, I am signing this object:

amitlan

Improve RI trigger efficiency with transition tables (Corey Huinker)

Common approach in DW environments it to disable constraints before loading and validate later. Disabling is a workaround for inefficient constraint checking which occurs per-row, per-constraint. Transition tables in PG 10 for statement level triggers are promising to implementing those checks more efficiently, but benchmarks shown initially were a bit artificial. Corey is considering making RI checking efficient with statement-level triggers based on transition tables. Discussion on which offending rows are shown in the error message. Possibility of a feature to ignore failed rows and store failing rows in a separate "rejection" table. It seems it makes sense to make a PoC patch as the approach seems promising and figure out the details later, including whether to allow users to specify whether to use per-row trigger based approach or statement level triggers.

amitlan

PostgreSQL 11 release contains features to improve the performance of DML operations on partitioned tables by enhancing the planner and the executor to use partition metadata more effectively. Those features include a new implementation of partition pruning, execution-time partition pruning (also known as dynamic pruning), partition-wise join and aggregation.

Improved partition pruning

Partition pruning is the ability to skip scanning of partitions that would

amitlan

Following tables are used in the examples below:

create table foo (a int);
insert into foo values (2), (3);

create table p (a int) partition by list (a);
create index on p (a);
create table p1 partition of p for values in (1);
create table p2 partition of p for values in (2);

amitlan

create table p (a int unique, b int) partition by hash (a);
create table p1 partition of p for values with (modulus 2, remainder 0) partition by hash (a);
create table p11 (b int, a int);
alter table p1 attach partition p11 for values with (modulus 1, remainder 0);
create table p2 partition of p for values with (modulus 2, remainder 1);

select  p.*,
        pg_get_expr(relpartbound, relid) as partbound,
        pg_get_partkeydef(relid) as partkey