PDF page 14
In PostgreSQL 11 the following features have been added to the partitioned table. #### 3.2.1 Hash Partition {#pg11-3-2-1-hash-partition} HASH was added to partitioning method of the partitioned table. This feature calculates the hash value of column value and performs partitioning by the hash value. Specify PARTITION BY HASH clause in CREATE TABLE statement that creates the partitioned table. **Example 9.** Create HASH Partitioned table ```text postgres=> CREATE TABLE hash1 (c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY HASH(c1) ; ``` ```text For each partition, use FOR VALUES WITH clause to specify the division number in MODULUS clause and the calculation result of the hash value in the REMAINDER clause. ``` **Example 10.** Create partition ```text postgres=> CREATE TABLE hash1a PARTITION OF hash1 FOR VALUES WITH (MODULUS 4, REMAINDER 0) ; CREATE TABLE postgres=> CREATE TABLE hash1b PARTITION OF hash1 FOR VALUES WITH (MODULUS 4, REMAINDER 1) ; CREATE TABLE postgres=> CREATE TABLE hash1c PARTITION OF hash1 FOR VALUES WITH (MODULUS 4, REMAINDER 2) ; CREATE TABLE postgres=> CREATE TABLE hash1d PARTITION OF hash1 FOR VALUES WITH (MODULUS 4, REMAINDER 3) ; CREATE TABLE ``` ```sql In the REMAINDER clause, specify a value smaller than MODULUS clause. If the number of partitions is smaller than the value specified in MODULES clause, there is no table storing the calculated hash value, so INSERT statement may cause an error. ```PDF page 15
**Example 11.** Confirmation of table structure ```text postgres=> \d hash1 Table "public.hash1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition key: HASH (c1) Number of partitions: 4 (Use \d+ to list them.) postgres=> \d hash1a Table "public.hash1a" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition of: hash1 FOR VALUES WITH (modulus 4, remainder 0) ``` **Example 12.** Error when there are not enough partitions ```text postgres=> INSERT INTO hash1 VALUES (102, 'data1') ; ERROR: no partition of relation "hash1" found for row DETAIL: Partition key of the failing row contains (c1) = (102). ``` ```sql When storing tuples directly on a partition, an INSERT statement that does not match the hash value will fail. ``` **Example 13.** Error in the INSERT statement ```text postgres=> INSERT INTO hash1a VALUES (100, 'data1') ; ERROR: new row for relation "hash1a" violates partition constraint DETAIL: Failing row contains (100, data1). ``` ```text Partition elimination is executed only for matching search. ```PDF page 16
**Example 14.** Partition elimination ```text postgres=> EXPLAIN SELECT * FROM hash1 WHERE c1 = 1000 ; QUERY PLAN --------------------------------------------------------------- Append (cost=0.00..20.39 rows=4 width=70) -> Seq Scan on hash1c (cost=0.00..20.38 rows=4 width=70) Filter: (c1 = '1000'::numeric) (3 rows) ``` ```text The "relpartbound" column in the pg_class catalog and the "partstrat" column in the pg_partitioned_table catalog now store the values corresponding to the hash partitions (h). ``` **Example 15.** Catalogs for Hash Partition table ```text postgres=> SELECT pg_get_expr(relpartbound, oid) FROM pg_class WHERE relname='hash1a' ; pg_get_expr ------------------------------------------ FOR VALUES WITH (modulus 4, remainder 0) (1 row) postgres=> SELECT partstrat FROM pg_partitioned_table WHERE partrelid='hash1'::regclass ; partstrat ----------- h (1 row) ``` #### 3.2.2 Default partition {#pg11-3-2-2-default-partition} The partition table automatically selects the partition where tuples are stored by column values. In PostgreSQL 11 DEFAULT partition feature that stores tuples with column values that are not stored in existing other partitions have been added. To create DEFAULT partition, specify DEFAULT clause instead of the FOR VALUES clause of CREATE TABLE statement. It is a common syntax for RANGE partitioned table and LIST partitioned table.PDF page 17
**Example 16.** Creating DEFAULT partition \(LIST partition\) ```text postgres=> CREATE TABLE plist1 (c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST (c1) ; CREATE TABLE postgres=> CREATE TABLE plist11 PARTITION OF plist1 FOR VALUES IN (100) ; CREATE TABLE postgres=> CREATE TABLE plist12 PARTITION OF plist1 FOR VALUES IN (200) ; CREATE TABLE postgres=> CREATE TABLE plist1d PARTITION OF plist1 DEFAULT ; CREATE TABLE ``` ```sql Even when attaching an existing table to a partitioned table, specify DEFAULT clause instead of FOR VALUES clause of ALTER TABLE statement. ``` **Example 17.** Attach DEFAULT partition \(LIST partition\) ```text postgres=> CREATE TABLE plist2d (c1 NUMERIC, c2 VARCHAR(10)) ; CREATE TABLE postgres=> ALTER TABLE plist2 ATTACH PARTITION plist2d DEFAULT ; ALTER TABLE ``` ```text DEFAULT partition has the following restrictions: ``` ##### Multiple DEFAULT partitions cannot be specified in the partitioned table {#pg11-p17-multiple-default-partitions-cannot-be-specified-in-the-partitioned-table} ##### Partitions with the same partition-key value as the tuples in DEFAULT partition cannot be {#pg11-p17-partitions-with-the-same-partition-key-value-as-the-tuples-in-default-partition-cannot-be} added ##### When attaching an existing table as DEFAULT partition, all tuples in the table to be attached {#pg11-p17-when-attaching-an-existing-table-as-default-partition-all-tuples-in-the-table-to-be-attached} are checked, and an error occurs if the same value is stored in the existing partition ##### DEFAULT partition cannot be specified for HASH partitioned table {#pg11-p17-default-partition-cannot-be-specified-for-hash-partitioned-table} In the example below, while trying to add a partition whose c1 column value is 200, a tuple whose value in the c1 column is 200 is already stored in the default partition, it fails.PDF page 18
**Example 18.** DEFAULT partition restriction ```text postgres=> CREATE TABLE plist1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST(c1) ; CREATE TABLE postgres=> CREATE TABLE plist11 PARTITION OF plist1 FOR VALUES IN (100) ; CREATE TABLE postgres=> CREATE TABLE plist1d PARTITION OF plist1 DEFAULT ; CREATE TABLE postgres=> INSERT INTO plist1 VALUES (100, 'v1'),(200, 'v2') ; INSERT 0 2 postgres=> CREATE TABLE plist12 PARTITION OF plist1 FOR VALUES IN (200) ; ERROR: updated partition constraint for default partition "plist1d" would be violated by some row ``` #### 3.2.3 Update partition key {#pg11-3-2-3-update-partition-key} It is now possible to execute an UPDATE statement which moves a tuple to another partition. In PostgreSQL 10, UPDATE statement that will change the value of partition-key column failed, but in PostgreSQL 11 it will move the tuple to another partition. **Example 19.** UPDATE statement to move the partition \(preparation of data\) ```text postgres=> CREATE TABLE part1(c1 INT, c2 VARCHAR(10)) PARTITION BY LIST(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (100) ; CREATE TABLE postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES IN (200) ; CREATE TABLE postgres=> INSERT INTO part1 VALUES (100, 'data100'); INSERT 0 1 postgres=> INSERT INTO part1 VALUES (200, 'data200'); INSERT 0 1 ```PDF page 19
**Example 20.** UPDATE statement to move partitions \(update and confirm data\) ```text postgres=> UPDATE part1 SET c1=100 WHERE c2='data200' ; UPDATE 1 postgres=> SELECT * FROM part1v1 ; c1 | c2 -----+--------- 100 | data100 100 | data200 (2 rows) postgres=> SELECT * FROM part1v2 ; c1 | c2 ----+---- (0 rows) ``` ##### Trigger {#pg11-p19-trigger} If an UPDATE statement is executed to move a tuple to another partition, the behavior of the trigger is complicated. For the trigger information to be executed, refer to "2.3.8 FOR EACH ROW Trigger". #### 3.2.4 Automatic index creation {#pg11-3-2-4-automatic-index-creation} When creating an index on a partitioned table, an index with the same configuration is automatically created for each partition.PDF page 20
**Example 21.** Create index ```text postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (100) ; CREATE TABLE postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES IN (200) ; CREATE TABLE postgres=> CREATE INDEX idx1_part1 ON part1(c2) ; CREATE INDEX postgres=> \d part1 Table "public.part1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition key: LIST (c1) Indexes: "idx1_part1" btree (c2) Number of partitions: 2 (Use \d+ to list them.) ``` ```text The name of the automatically generated index is "{partition name}_{column name}_idx". For indexes consisting of multiple columns, column names are concatenated by underscores (_). If an index with the same name already exists, a number will be appended to the end of the index name. It will be abbreviated if the automatically generated index name is too long. ```PDF page 21
**Example 22.** Check auto\-created index ```text postgres=> \d part1v1 Table "public.part1v1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition of: part1 FOR VALUES IN ('100') Indexes: "part1v1_c2_idx" btree (c2) <- Automatically generated index postgres=> \d part1v2 Table "public.part1v2" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition of: part1 FOR VALUES IN ('200') Indexes: "part1v2_c2_idx" btree (c2) <- Automatically generated index ``` ```text An index is automatically created even if a table is attached as a partition. ```PDF page 22
**Example 23.** Attaching a table ```text postgres=> CREATE TABLE part1v3 (LIKE part1) ; CREATE TABLE postgres=> ALTER TABLE part1 ATTACH PARTITION part1v3 FOR VALUES IN (300) ; ALTER TABLE postgres=> \d part1v3 Table "public.part1v3" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition of: part1 FOR VALUES IN ('300') Indexes: "part1v3_c2_idx" btree (c2) <- Automatically generated index ``` ```text Automatically generated indexes cannot be deleted individually. ``` **Example 24.** Individual deletion of automatically created index ```text postgres=> DROP INDEX part1v1_c2_idx ; ERROR: cannot drop index part1v1_c2_idx because index idx1_part1 requires it HINT: You can drop index idx1_part1 instead. ``` #### 3.2.5 Create unique constraint {#pg11-3-2-5-create-unique-constraint} Unique constraints (PRIMARY KEY and UNIQUE KEY) can now be specified for the partitioned table.PDF page 23
**Example 25.** Create the primary key in the partitioned table ```text postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> ALTER TABLE part1 ADD CONSTRAINT pk_part1 PRIMARY KEY (c1) ; ALTER TABLE postgres=> \d part1 Table "public.part1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | not null | c2 | character varying(10) | | | Partition key: RANGE (c1) Indexes: "pk_part1" PRIMARY KEY, btree (c1) Number of partitions: 0 ``` ```text The primary key setting is automatically added to each partition. ``` **Example 26.** Attached table and primary key ```text postgres=> CREATE TABLE part1v1 (LIKE part1) ; CREATE TABLE postgres=> ALTER TABLE part1 ATTACH PARTITION part1v1 FOR VALUES FROM (100) TO (200) ; ALTER TABLE postgres=> \d part1v1 Table "public.part1v1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | not null | c2 | character varying(10) | | | Partition of: part1 FOR VALUES FROM ('100') TO ('200') Indexes: "part1v1_pkey" PRIMARY KEY, btree (c1) ```PDF page 24
A primary key constraint must include a partition key column. Attempting to create a primary key constraint that does not contain partitioned columns results in an error. **Example 27.** Create primary key without partition key column ```text postgres=> CREATE TABLE part2(c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> ALTER TABLE part2 ADD CONSTRAINT pk_part2 PRIMARY KEY (c2) ; ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "part2" lacks column "c1" which is part of the partition key. ``` ```text Attempting to attach a table whose primary key constraint is specified in a column different from the partition table results in an error. ``` **Example 28.** Attach a table with a different primary key ```text postgres=> CREATE TABLE part3(c1 NUMERIC PRIMARY KEY, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> CREATE TABLE part3v1 (LIKE part3) ; CREATE TABLE postgres=> ALTER TABLE part3v1 ADD CONSTRAINT part3v1_pkey PRIMARY KEY (c1, c2); ALTER TABLE postgres=> ALTER TABLE part3 ATTACH PARTITION part3v1 FOR VALUES FROM (100) TO (200) ; ERROR: multiple primary keys for table "part3v1" are not allowed ``` ```sql If the partition is a FOREIGN TABLE, creating the partition fails because it cannot create an index on the partition. ```PDF page 25
**Example 29.** Partitions and Unique Constraints with FOREIGN TABLE ```text postgres=> CREATE TABLE part1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> CREATE FOREIGN TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (1000000) SERVER remhost1 ; ERROR: cannot create index on foreign table "part1v1" ``` #### 3.2.6 INSERT ON CONFLICT statement {#pg11-3-2-6-insert-on-conflict-statement} INSERT ON CONFLICT statement to the partitioned table can now be executed. Both DO NOTHING syntax and DO UPDATE syntax are executable. **Example 30.** Partitioned table and INSERT ON CONFLICT statement ```text postgres=> CREATE TABLE part1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (1000) ; CREATE TABLE postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES FROM (1000) TO (2000) ; CREATE TABLE postgres=> INSERT INTO part1 VALUES (100, 'data1') ON CONFLICT DO NOTHING ; INSERT 0 1 postgres=> INSERT INTO part1 VALUES (100, 'update') ON CONFLICT ON CONSTRAINT part1_pkey DO UPDATE SET c2='update' ; INSERT 0 1 ``` ```text However, updating across partitions is not accepted. ```PDF page 26
**Example 31.** INSERT ON CONFLICT statement across partitions ```text postgres=> INSERT INTO part1 VALUES (100, 'update') ON CONFLICT ON CONSTRAINT part1_pkey DO UPDATE SET c1=1500 ; ERROR: invalid ON UPDATE specification DETAIL: The result tuple would appear in a different partition than the original tuple. ``` #### 3.2.7 Partition-Wise Join / Partition-Wise Aggregate {#pg11-3-2-7-partition-wise-join-partition-wise-aggregate} Partition-Wise Join which joins partition when joining tables and Partition-Wise Aggregate which performs aggregation is supported. These features are off by default but can be enabled by setting the following parameter to "on". **Table 4.** Related parameter name | Features | Parameter name | Default value | | --- | --- | --- | | Partition-Wise Join | enable partitionwise join__ | off | | Partition-Wise Aggregate | enable partitionwise aggregate__ | off | The following is an execution plan of the SQL statement that performs the join between the partitioned table with the c1 column as the key. In the default state, each partition is integrated by Append and then joined using Parallel Hash Join.PDF page 27
**Example 32.** Execution plan in the default state ```text postgres=> EXPLAIN SELECT COUNT(*) FROM pjoin1 p1 INNER JOIN pjoin2 p2 ON p1.c1 = p2.c1 ; QUERY PLAN ------------------------------------------------------------------------------------ Finalize Aggregate (cost=79745.46..79745.47 rows=1 width=8) -> Gather (cost=79745.25..79745.46 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=78745.25..78745.26 rows=1 width=8) -> Parallel Hash Join (cost=36984.68..76661.91 rows=833333 width=0) Hash Cond: (p1.c1 = p2.c1) -> Parallel Append (cost=0.00..23312.00 rows=833334 width=6) -> Parallel Seq Scan on pjoin1v1 p1 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Seq Scan on pjoin1v2 p1_1 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Hash (cost=23312.00..23312.00 rows=833334 width=6) -> Parallel Append (cost=0.00..23312.00 rows=833334 width=6) -> Parallel Seq Scan on pjoin2v1 p2 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Seq Scan on pjoin2v2 p2_1 (cost=0.00..9572.67 rows=416667 width=7) (13 rows) ``` ```text When Partition-Wise Join feature is enabled, it can be confirmed that coupling is performed first between partitions of two tables. ```PDF page 28
**Example 33.** Execution plan with Partition\-Wise Join enabled ```text postgres=> SET enable_partitionwise_join = on ; SET postgres=> EXPLAIN SELECT COUNT(*) FROM pjoin1 p1 INNER JOIN pjoin2 p2 ON p1.c1 = p2.c1 ; QUERY PLAN ------------------------------------------------------------------------------------ Finalize Aggregate (cost=75578.78..75578.79 rows=1 width=8) -> Gather (cost=75578.57..75578.78 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=74578.57..74578.58 rows=1 width=8) -> Parallel Append (cost=16409.00..72495.23 rows=833334 width=0) -> Parallel Hash Join (cost=16409.00..34164.28 rows=416667 width=0) Hash Cond: (p1.c1 = p2.c1) -> Parallel Seq Scan on pjoin1v1 p1 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Hash (cost=9572.67..9572.67 rows=416667 width=6) -> Parallel Seq Scan on pjoin2v1 p2 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Hash Join (cost=16409.00..34164.28 rows=416667 width=0) Hash Cond: (p1_1.c1 = p2_1.c1) -> Parallel Seq Scan on pjoin1v2 p1_1 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Hash (cost=9572.67..9572.67 rows=416667 width=7) -> Parallel Seq Scan on pjoin2v2 p2_1 (cost=0.00..9572.67 rows=416667 width=7) (15 rows) ``` ```text Furthermore, if the Partition-Wise Aggregate feature is enabled, an execution plan is created for each summary process for each partition. ```PDF page 29
**Example 34.** Execution plan with Partition\-Wise Aggregate enabled ```text postgres=> SET enable_partitionwise_aggregate = on ; SET postgres=> EXPLAIN SELECT COUNT(*) FROM pjoin1 p1 INNER JOIN pjoin2 p2 ON p1.c1 = p2.c1 ; QUERY PLAN ------------------------------------------------------------------------------------ Finalize Aggregate (cost=71412.34..71412.35 rows=1 width=8) -> Gather (cost=36205.95..71412.33 rows=4 width=8) Workers Planned: 2 -> Parallel Append (cost=35205.95..70411.93 rows=2 width=8) -> Partial Aggregate (cost=35205.95..35205.96 rows=1 width=8) -> Parallel Hash Join (cost=16409.00..34164.28 rows=416667 width=0) Hash Cond: (p1.c1 = p2.c1) -> Parallel Seq Scan on pjoin1v1 p1 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Hash (cost=9572.67..9572.67 rows=416667 width=6) -> Parallel Seq Scan on pjoin2v1 p2 (cost=0.00..9572.67 rows=416667 width=6) -> Partial Aggregate (cost=35205.95..35205.96 rows=1 width=8) -> Parallel Hash Join (cost=16409.00..34164.28 rows=416667 width=0) Hash Cond: (p1_1.c1 = p2_1.c1) -> Parallel Seq Scan on pjoin1v2 p1_1 (cost=0.00..9572.67 rows=416667 width=6) -> Parallel Hash (cost=9572.67..9572.67 rows=416667 width=7) -> Parallel Seq Scan on pjoin2v2 p2_1 (cost=0.00..9572.67 rows=416667 width=7) (16 rows) ```PDF page 30
#### 3.2.8 FOR EACH ROW trigger {#pg11-3-2-8-for-each-row-trigger} FOR EACH ROW trigger for the partitioned table can now be created. However, BEFORE trigger cannot be created, only AFTER trigger can be created. Also, WHEN clause cannot be specified. When the FOR EACH ROW trigger is executed, TG_TABLE_NAME variable is changed to the partition name where the tuple is actually stored, not the partitioned table name. As a result of the verification, each trigger is executed in the following order. **Table 5.** Simple INSERT statement | Order | Target table | TG_WHEN | TG_OP | TG_LEVEL | TG_TABLE_NAME | | --- | --- | --- | --- | --- | --- | | 1 | Partitioned table | BEFORE | INSERT | STATEMENT | Partitioned table | | 2 | Partition | BEFORE | INSERT | ROW | Partition | | 3 | Partitioned table | AFTER | INSERT | ROW | Partition | | 4 | Partition | AFTER | INSERT | ROW | Partition | | 5 | Partitioned table | AFTER | INSERT | STATEMENT | Partitioned table | **Table 6.** Simple UPDATE statement \(No tuple movement between partitions\) | Order | Target table | TG_WHEN | TG_OP | TG_LEVEL | TG_TABLE_NAME | | --- | --- | --- | --- | --- | --- | | 1 | Partitioned table | BEFORE | UPDATE | STATEMENT | Partitioned table | | 2 | Partition | BEFORE | UPDATE | ROW | Partition | | 3 | Partitioned table | AFTER | UPDATE | ROW | Partition | | 4 | Partition | AFTER | UPDATE | ROW | Partition | | 5 | Partitioned table | AFTER | UPDATE | STATEMENT | Partitioned table | **Table 7.** Simple DELETE statement | Order | Target table | TG_WHEN | TG_OP | TG_LEVEL | TG_TABLE_NAME | | --- | --- | --- | --- | --- | --- | | 1 | Partitioned table | BEFORE | DELETE | STATEMENT | Partitioned table | | 2 | Partition | BEFORE | DELETE | ROW | Partition | | 3 | Partitioned table | AFTER | DELETE | ROW | Partition | | 4 | Partition | AFTER | DELETE | ROW | Partition | | 5 | Partitioned table | AFTER | DELETE | STATEMENT | Partitioned table |PDF page 31
**Table 8.** TRUNCATE statement | Order | Target table | TG_WHEN | TG_OP | TG_LEVEL | TG_TABLE_NAME | | --- | --- | --- | --- | --- | --- | | 1 | Partitioned table | BEFORE | TRUNCATE | STATEMENT | Partitioned table | | 2 | Partition | BEFORE | TRUNCATE | STATEMENT | Partition | | 3 | Partitioned table | AFTER | TRUNCATE | STATEMENT | Partitioned table | | 4 | Partition | BEFORE | TRUNCATE | STATEMENT | Partitioned table | **Table 9.** When a tuple is moved between partitions by executing an UPDATE statement | Order | Target table | TG_WHEN | TG_OP | TG_LEVEL | TG_TABLE_NAME | | --- | --- | --- | --- | --- | --- | | 1 | Partitioned table | BEFORE | UPDATE | STATEMENT | Partitioned table | | 2 | Source partition | BEFORE | UPDATE | ROW | Source partition | | 3 | Source partition | BEFORE | DELETE | ROW | Source partition | | 4 | Target partition | BEFORE | INSERT | ROW | Target partition | | 5 | Partitioned table | AFTER | DELETE | ROW | Source partition | | 6 | Source partition | AFTER | DELETE | ROW | Source partition | | 7 | Partitioned table | AFTER | INSERT | ROW | Target partition | | 8 | Target partition | AFTER | INSERT | ROW | Target partition | | 9 | Partitioned table | AFTER | UPDATE | STATEMENT | Partitioned table | **Table 10.** INSERT ON CONFLICT DO NOTHING \(With conflict\) | Order | Target table | TG_WHEN | TG_OP | TG_LEVEL | TG_TABLE_NAME | | --- | --- | --- | --- | --- | --- | | 1 | Partitioned table | BEFORE | INSERT | STATEMENT | Partitioned table | | 2 | Partition | BEFORE | INSERT | ROW | Partition | | 3 | Partitioned table | AFTER | INSERT | STATEMENT | Partitioned table | #### 3.2.9 FOREIGN KEY support {#pg11-3-2-9-foreign-key-support} A Foreign key could not be created in PostgreSQL 10 partitioned table. In PostgreSQL 11 this restriction has been resolved.PDF page 32
**Example 35.** Partitioned table and Foreign key ```text postgres=> CREATE TABLE cities (city VARCHAR(80) PRIMARY KEY, location point) ; CREATE TABLE postgres=> CREATE TABLE weather ( city VARCHAR(80) REFERENCES cities(city), temp_lo INT, temp_hi INT, prcp REAL, date DATE) PARTITION BY LIST (city) ; CREATE TABLE ``` #### 3.2.10 Dynamic Partition Elimination {#pg11-3-2-10-dynamic-partition-elimination} Partition elimination is now executed even when the partition key is specified as a parameter. However, dynamic partition elimination is available only when "generic plan" by PREPARE and EXECUTE statements are used. **Example 36.** Executed SQL statement for test ```sql CREATE TABLE part4 (c1 INT NOT NULL, c2 INT NOT NULL) PARTITION BY LIST (c1) ; CREATE TABLE part4v1 PARTITION OF part4 FOR VALUES IN (1) ; CREATE TABLE part4v2 PARTITION OF part4 FOR VALUES IN (2) ; CREATE TABLE part4v3 PARTITION OF part4 FOR VALUES IN (3) ; PREPARE part4_pl (INT, INT) AS SELECT c1 FROM part4 WHERE c1 BETWEEN $1 and $2 and c2 < 3 ; EXECUTE part4_pl (1, 8) ; EXECUTE part4_pl (1, 8) ; EXECUTE part4_pl (1, 8) ; EXECUTE part4_pl (1, 8) ; EXECUTE part4_pl (1, 8) ; EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) EXECUTE part4_pl (2, 2) ; ```PDF page 33
**Example 37.** Execution plan on PostgreSQL 10 ```text postgres=> EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) EXECUTE part4_pl (2, 2) ; QUERY PLAN ---------------------------------------------------------- Append (actual rows=0 loops=1) -> Seq Scan on part4v1 (actual rows=0 loops=1) Filter: ((c1 >= $1) AND (c1 <= $2) AND (c2 < 3)) -> Seq Scan on part4v2 (actual rows=0 loops=1) Filter: ((c1 >= $1) AND (c1 <= $2) AND (c2 < 3)) -> Seq Scan on part4v3 (actual rows=0 loops=1) Filter: ((c1 >= $1) AND (c1 <= $2) AND (c2 < 3)) (7 rows) ``` **Example 38.** Execution plan on PostgreSQL 11 ```text postgres=> EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) EXECUTE part4_pl (2, 2) ; QUERY PLAN ---------------------------------------------------------- Append (actual rows=0 loops=1) Subplans Removed: 2 -> Seq Scan on part4v2 (actual rows=0 loops=1) Filter: ((c1 >= $1) AND (c1 <= $2) AND (c2 < 3)) (4 rows) ``` #### 3.2.11 Control Partition Pruning {#pg11-3-2-11-control-partition-pruning} The partition elimination feature can be controlled by setting the parameter enable_partition_pruning. Setting this parameter to "off" will disable partition pruning in the search process.PDF page 34
**Example 39.** Control Partition Pruning ```text postgres=> SHOW enable_partition_pruning ; enable_partition_pruning -------------------------- on (1 row) postgres=> EXPLAIN SELECT * FROM part1 WHERE c1=1000 ; QUERY PLAN ----------------------------------------------------------------------------------- Append (cost=0.42..8.45 rows=1 width=10) -> Index Scan using part1v1_pkey on part1v1 (cost=0.42..8.44 rows=1 width=10) Index Cond: (c1 = 1000) (3 rows) postgres=> SET enable_partition_pruning = off ; SET postgres=> EXPLAIN SELECT * FROM part1 WHERE c1=1000 ; QUERY PLAN ----------------------------------------------------------------------------------- Append (cost=0.42..16.90 rows=2 width=10) -> Index Scan using part1v1_pkey on part1v1 (cost=0.42..8.44 rows=1 width=10) Index Cond: (c1 = 1000) -> Index Scan using part1v2_pkey on part1v2 (cost=0.42..8.44 rows=1 width=10) Index Cond: (c1 = 1000) (5 rows) ```