Horton

v1-0002-Don-t-copy-extended-statistics-during-MERGE-SPLIT.patch

application/octet-stream

Download
Filename: v1-0002-Don-t-copy-extended-statistics-during-MERGE-SPLIT.patch
Type: application/octet-stream
Part: 0
Message: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →
Format: format-patch
Series: patch v1-0002
Subject: Don't copy extended statistics during MERGE/SPLIT partition operations
File+−
doc/src/sgml/ref/alter_table.sgml 6 2
src/backend/commands/tablecmds.c 5 3
src/test/regress/expected/partition_merge.out 17 0
src/test/regress/expected/partition_split.out 27 0
src/test/regress/sql/partition_merge.sql 10 0
src/test/regress/sql/partition_split.sql 12 0 
From 1a0dcde924ce020d8e3ff7242a8874bc3da70bfa Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Wed, 8 May 2024 20:32:20 +0300
Subject: [PATCH v1 2/2] Don't copy extended statistics during MERGE/SPLIT
 partition operations

Currenlty MERGE/SPLIT partition operations create new partiions copying the
extended statistics from the parent table.  However, parent's table extended
statistics already covers all its children.  Also, "CREATE TABLE ... PARTITION
OF" command doesn't copy extended statistics.  This commit makes
createPartitionTable() skip copying extended statistics from the parent.

Reported-by: Justin Pryzby
Discussion: https://postgr.es/m/ZiJW1g2nbQs9ekwK%40pryzbyj2023
---
 doc/src/sgml/ref/alter_table.sgml             |  8 ++++--
 src/backend/commands/tablecmds.c              |  8 +++---
 src/test/regress/expected/partition_merge.out | 17 ++++++++++++
 src/test/regress/expected/partition_split.out | 27 +++++++++++++++++++
 src/test/regress/sql/partition_merge.sql      | 10 +++++++
 src/test/regress/sql/partition_split.sql      | 12 +++++++++
 6 files changed, 77 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 0bf11f6cb6d..a11a407b7a6 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1155,9 +1155,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      </para>
      <para>
       The new partitions will be created the same as tables created with the
-      SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_nameN</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)</literal>.
+      SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_nameN</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY EXCLUDING STATISTICS)</literal>.
       The indexes and identity are created later, after moving the data
       into the new partitions.
+      The extended statistics aren't copied from the parent table, since
+      the parent's statistics cover all children and it's typically enough.
       New partitions will have the same table access method as the parent.
       If the parent table is persistent then new partitions are created
       persistent.  If the parent table is temporary then new partitions
@@ -1225,9 +1227,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      </para>
      <para>
       The new partition will be created the same as a table created with the
-      SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_name</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)</literal>.
+      SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_name</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY EXCLUDING STATISTICS)</literal>.
       The indexes and identity are created later, after moving the data
       into the new partition.
+      The extended statistics aren't copied from the parent table, since
+      the parent's statistics cover all children and it's typically enough.
       The new partition will have the same table access method as the parent.
       If the parent table is persistent then the new partition is created
       persistent.  If the parent table is temporary then the new partition
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5bf5e69c5b8..dddf694b787 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21245,7 +21245,7 @@ moveSplitTableRows(Relation rel, Relation splitRel, List *partlist, List *newPar
  * (newPartName) like table (modelRel)
  *
  * Emulates command: CREATE [TEMP] TABLE <newPartName> (LIKE <modelRel's name>
- * INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)
+ * INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY EXCLUDING STATISTICS)
  *
  * Also, this function sets the new partition access method same as parent
  * table access methods (similarly to CREATE TABLE ... PARTITION OF).  It
@@ -21289,9 +21289,11 @@ createPartitionTable(RangeVar *newPartName, Relation modelRel,
 
 	/*
 	 * Indexes will be inherited on "attach new partitions" stage, after data
-	 * moving.
+	 * moving.  We also don't copy the extended statistics since the parent's
+	 * statistics cover all children and it's typically enough.
 	 */
-	tlc->options = CREATE_TABLE_LIKE_ALL & ~(CREATE_TABLE_LIKE_INDEXES | CREATE_TABLE_LIKE_IDENTITY);
+	tlc->options = CREATE_TABLE_LIKE_ALL &
+		~(CREATE_TABLE_LIKE_INDEXES | CREATE_TABLE_LIKE_IDENTITY | CREATE_TABLE_LIKE_STATISTICS);
 	tlc->relationOid = InvalidOid;
 	createStmt->tableElts = lappend(createStmt->tableElts, tlc);
 
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 076264c88eb..36a60d7472c 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -910,6 +910,23 @@ RESET SESSION AUTHORIZATION;
 DROP TABLE t;
 DROP ROLE regress_partition_merge_alice;
 DROP ROLE regress_partition_merge_bob;
+-- Check extended statistics aren't copied from the parent table to the new
+-- partition.
+CREATE TABLE t (i int, j int) PARTITION BY RANGE (i);
+CREATE STATISTICS t_i_j (mcv) ON i, j FROM t;
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+\d+ tp_0_2
+                          Table "partitions_merge_schema.tp_0_2"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i      | integer |           |          |         | plain   |              | 
+ j      | integer |           |          |         | plain   |              | 
+Partition of: t FOR VALUES FROM (0) TO (2)
+Partition constraint: ((i IS NOT NULL) AND (i >= 0) AND (i < 2))
+
+DROP TABLE t;
 RESET search_path;
 --
 DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 74e19d250e9..461318db867 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1543,6 +1543,33 @@ RESET SESSION AUTHORIZATION;
 DROP TABLE t;
 DROP ROLE regress_partition_merge_alice;
 DROP ROLE regress_partition_merge_bob;
+-- Check extended statistics aren't copied from the parent table to new
+-- partitions.
+CREATE TABLE t (i int, j int) PARTITION BY RANGE (i);
+CREATE STATISTICS t_i_j (mcv) ON i, j FROM t;
+CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+\d+ tp_0_1
+                          Table "partition_split_schema.tp_0_1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i      | integer |           |          |         | plain   |              | 
+ j      | integer |           |          |         | plain   |              | 
+Partition of: t FOR VALUES FROM (0) TO (1)
+Partition constraint: ((i IS NOT NULL) AND (i >= 0) AND (i < 1))
+
+\d+ tp_1_2
+                          Table "partition_split_schema.tp_1_2"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i      | integer |           |          |         | plain   |              | 
+ j      | integer |           |          |         | plain   |              | 
+Partition of: t FOR VALUES FROM (1) TO (2)
+Partition constraint: ((i IS NOT NULL) AND (i >= 1) AND (i < 2))
+
+DROP TABLE t;
 --
 DROP SCHEMA partition_split_schema;
 DROP SCHEMA partition_split_schema2;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 9bfeb0d7ef3..203811b6e39 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -582,6 +582,16 @@ DROP TABLE t;
 DROP ROLE regress_partition_merge_alice;
 DROP ROLE regress_partition_merge_bob;
 
+-- Check extended statistics aren't copied from the parent table to the new
+-- partition.
+CREATE TABLE t (i int, j int) PARTITION BY RANGE (i);
+CREATE STATISTICS t_i_j (mcv) ON i, j FROM t;
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+\d+ tp_0_2
+DROP TABLE t;
+
 RESET search_path;
 
 --
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index bb52514b7ed..dc7424256e8 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -927,6 +927,18 @@ DROP TABLE t;
 DROP ROLE regress_partition_merge_alice;
 DROP ROLE regress_partition_merge_bob;
 
+-- Check extended statistics aren't copied from the parent table to new
+-- partitions.
+CREATE TABLE t (i int, j int) PARTITION BY RANGE (i);
+CREATE STATISTICS t_i_j (mcv) ON i, j FROM t;
+CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+\d+ tp_0_1
+\d+ tp_1_2
+DROP TABLE t;
+
 
 --
 DROP SCHEMA partition_split_schema;
-- 
2.39.3 (Apple Git-145)