public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Dmitry Koval <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: [email protected]
Cc: Tomas Vondra <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Date: Tue, 14 May 2024 09:49:53 -0500
Message-ID: <ZkN6EfjQGz8TOhbL@pryzbyj2023> (raw)
In-Reply-To: <CAPpHfdvh_XYHahtpoxfzDkvYOFUH1F5tm3JTV=sOZt8M4CTBcA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<ZjFRSVnvvGrsdndU@pryzbyj2023>
<[email protected]>
<ZjTlQiBPYm707Rxv@pryzbyj2023>
<CAPpHfduhHm+4yeObsj9wK4iZvV2Ve5xcr6BwZDFiNBYbE1PfLg@mail.gmail.com>
<CAPpHfdtgLiT2d++-qLircLB8ATUfzgR=aTmRDdF7w=2E9yDHbQ@mail.gmail.com>
<Zjvwqs-GV0MjWIhU@pryzbyj2023>
<CAPpHfdvh_XYHahtpoxfzDkvYOFUH1F5tm3JTV=sOZt8M4CTBcA@mail.gmail.com>
On Thu, May 09, 2024 at 12:51:32AM +0300, Alexander Korotkov wrote:
> > > However, parent's table extended statistics already covers all its
> > > children.
> >
> > => That's the wrong explanation. It's not that "stats on the parent
> > table cover its children". It's that there are two types of stats:
> > stats for the "table hierarchy" and stats for the individual table.
> > That's true for single-column stats as well as for extended stats.
> > In both cases, that's indicated by the inh flag in the code and in the
> > catalog.
> >
> > The right explanation is that extended stats on partitioned tables are
> > not similar to indexes. Indexes on parent table are nothing other than
> > a mechanism to create indexes on the child tables. That's not true for
> > stats.
> >
> > See also my prior messages
> > ZiJW1g2nbQs9ekwK@pryzbyj2023
> > Zi5Msg74C61DjJKW@pryzbyj2023
>
> Yes, I understand that parents pg_statistic entry with stainherit ==
> true includes statistics for the children. I tried to express this by
> word "covers". But you're right, this is the wrong explanation.
>
> Can I, please, ask you to revise the patch?
I tried to make this clear but it'd be nice if someone (Tomas/Alvaro?)
would check that this says what's wanted.
--
Justin
Attachments:
[text/x-diff] 0001-Don-t-copy-extended-statistics-during-MERGE-SPLIT-pa.patch (4.8K, ../ZkN6EfjQGz8TOhbL@pryzbyj2023/2-0001-Don-t-copy-extended-statistics-during-MERGE-SPLIT-pa.patch)
download | inline diff:
From 265207e5bdb215600ce5d7b45f627bc41fc2bc26 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Wed, 8 May 2024 20:32:20 +0300
Subject: [PATCH] Don't copy extended statistics during MERGE/SPLIT partition
operations
When MERGE/SPLIT created new partitions, it was cloning the extended
statistics of the parent table.
However, extended stats on partitioned tables are not analgous to
indexes on partitioned tables (which exist only to create physical
indexes on child tables). Rather, extended stats on a parent 1) cause
extended stats to be collected and computed across the whole partition
heirarchy, and 2) do not cause extended stats to be computed for the
individual partitions.
"CREATE TABLE ... PARTITION OF" command doesn't copy extended
statistics. This commit makes createPartitionTable() behave
consistently.
Reported-by: Justin Pryzby
Discussion: https://postgr.es/m/ZiJW1g2nbQs9ekwK%40pryzbyj2023
---
doc/src/sgml/ref/alter_table.sgml | 9 +++++++--
src/backend/commands/tablecmds.c | 8 +++++---
2 files changed, 12 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 891fa4a7a04..313c722ee7f 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1154,9 +1154,12 @@ 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.
+ Extended statistics aren't copied from the parent table, for consistency with
+ <command>CREATE TABLE PARTITION OF</command>.
+
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
@@ -1224,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.
+ Extended statistics aren't copied from the parent table, for consistency with
+ <command>CREATE TABLE PARTITION OF</command>.
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 79c9c031833..50fc54cb309 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20419,7 +20419,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
@@ -20463,9 +20463,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 for consistency
+ * with CREATE TABLE PARTITION OF.
*/
- 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);
--
2.42.0
view thread (33+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
In-Reply-To: <ZkN6EfjQGz8TOhbL@pryzbyj2023>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox