Skip to content

4 Enhancements of Partitioned Table

Source chapter 3.2 for PostgreSQL 11.
GASource pages: PDF 14-34Original PDF on GitHub

PDF page 14

In PostgreSQL 11 the following features have been added to the partitioned table.

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
postgres=> CREATE TABLE hash1 (c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY
HASH(c1) ;
 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
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
 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
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
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).
 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
postgres=> INSERT INTO hash1a VALUES (100, 'data1') ;
ERROR:  new row for relation "hash1a" violates partition constraint
DETAIL:  Failing row contains (100, data1).
 Partition elimination is executed only for matching search.

PDF page 16

Example 14 Partition elimination
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)
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
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

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)
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
 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)
postgres=> CREATE TABLE plist2d (c1 NUMERIC, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> ALTER TABLE plist2 ATTACH PARTITION plist2d DEFAULT ;
ALTER TABLE
 DEFAULT partition has the following restrictions:
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

added

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

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
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

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)
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)
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

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

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
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.)
 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
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
 An index is automatically created even if a table is attached as a partition.

PDF page 22

Example 23 Attaching a table
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
 Automatically generated indexes cannot be deleted individually.
Example 24 Individual deletion of automatically created index
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

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
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
 The primary key setting is automatically added to each partition.
Example 26 Attached table and primary key
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
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.
 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
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
 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
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

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
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
 However, updating across partitions is not accepted.

PDF page 26

Example 31 INSERT ON CONFLICT statement across partitions
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

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”.

FeaturesParameter nameDefault value
Partition-Wise Joinenable partitionwise join__off
Partition-Wise Aggregateenable partitionwise aggregate__off
Table 4 Related parameter name

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
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)
 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
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)
 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
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

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.

OrderTarget tableTG_WHENTG_OPTG_LEVELTG_TABLE_NAME
1Partitioned tableBEFOREINSERTSTATEMENTPartitioned table
2PartitionBEFOREINSERTROWPartition
3Partitioned tableAFTERINSERTROWPartition
4PartitionAFTERINSERTROWPartition
5Partitioned tableAFTERINSERTSTATEMENTPartitioned table
Table 5 Simple INSERT statement
OrderTarget tableTG_WHENTG_OPTG_LEVELTG_TABLE_NAME
1Partitioned tableBEFOREUPDATESTATEMENTPartitioned table
2PartitionBEFOREUPDATEROWPartition
3Partitioned tableAFTERUPDATEROWPartition
4PartitionAFTERUPDATEROWPartition
5Partitioned tableAFTERUPDATESTATEMENTPartitioned table
Table 6 Simple UPDATE statement (No tuple movement between partitions)
OrderTarget tableTG_WHENTG_OPTG_LEVELTG_TABLE_NAME
1Partitioned tableBEFOREDELETESTATEMENTPartitioned table
2PartitionBEFOREDELETEROWPartition
3Partitioned tableAFTERDELETEROWPartition
4PartitionAFTERDELETEROWPartition
5Partitioned tableAFTERDELETESTATEMENTPartitioned table
Table 7 Simple DELETE statement

PDF page 31

OrderTarget tableTG_WHENTG_OPTG_LEVELTG_TABLE_NAME
1Partitioned tableBEFORETRUNCATESTATEMENTPartitioned table
2PartitionBEFORETRUNCATESTATEMENTPartition
3Partitioned tableAFTERTRUNCATESTATEMENTPartitioned table
4PartitionBEFORETRUNCATESTATEMENTPartitioned table
Table 8 TRUNCATE statement
OrderTarget tableTG_WHENTG_OPTG_LEVELTG_TABLE_NAME
1Partitioned tableBEFOREUPDATESTATEMENTPartitioned table
2Source partitionBEFOREUPDATEROWSource partition
3Source partitionBEFOREDELETEROWSource partition
4Target partitionBEFOREINSERTROWTarget partition
5Partitioned tableAFTERDELETEROWSource partition
6Source partitionAFTERDELETEROWSource partition
7Partitioned tableAFTERINSERTROWTarget partition
8Target partitionAFTERINSERTROWTarget partition
9Partitioned tableAFTERUPDATESTATEMENTPartitioned table
Table 9 When a tuple is moved between partitions by executing an UPDATE statement
OrderTarget tableTG_WHENTG_OPTG_LEVELTG_TABLE_NAME
1Partitioned tableBEFOREINSERTSTATEMENTPartitioned table
2PartitionBEFOREINSERTROWPartition
3Partitioned tableAFTERINSERTSTATEMENTPartitioned table
Table 10 INSERT ON CONFLICT DO NOTHING (With conflict)

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
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

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
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
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
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

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
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)