Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION
Hi,
The documentation recommends running ANALYZE after the "ALTER TABLE ... SET DATA TYPE"
command since the statistics will become wrong after that.
That same thing can be applied to "ALTER TABLE ... SET EXPRESSION" commands,
but that is not described in the documentation, so I've attached a patch
to add this statement.
Regards,
Yugo Nagata
Attachments:
fix_doc_alter_table_set_expression.patchtext/x-diff; name=fix_doc_alter_table_set_expression.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1e4f26c13f6..85c50428a13 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -270,6 +270,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
+
+ <para>
+ When this form is used, the column's statistics are removed,
+ so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
+ on the table afterwards is recommended.
+ </para>
</listitem>
</varlistentry>
On Mon, Aug 4, 2025 at 3:14 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:
Hi,
The documentation recommends running ANALYZE after the "ALTER TABLE ... SET DATA TYPE"
command since the statistics will become wrong after that.That same thing can be applied to "ALTER TABLE ... SET EXPRESSION" commands,
but that is not described in the documentation, so I've attached a patch
to add this statement.
+1
+ When this form is used, the column's statistics are removed,
+ so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
+ on the table afterwards is recommended.ANALYZE is not needed for virtual generated columns, since they
don't have statistics in the first place? If so, would it be clearer to
explicitly mention that? How about rephrasing it like this?
------------------------------
When this form is used on a stored generated column, its statistics
are removed, so running <link linkend="sql-analyze"><command>ANALYZE
</command></link> on the table afterwards is recommended.
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
------------------------------
Regards,
--
Fujii Masao
On Mon, 4 Aug 2025 20:01:34 +0900
Fujii Masao <masao.fujii@gmail.com> wrote:
On Mon, Aug 4, 2025 at 3:14 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:
Hi,
The documentation recommends running ANALYZE after the "ALTER TABLE ... SET DATA TYPE"
command since the statistics will become wrong after that.That same thing can be applied to "ALTER TABLE ... SET EXPRESSION" commands,
but that is not described in the documentation, so I've attached a patch
to add this statement.+1
+ When this form is used, the column's statistics are removed, + so running <link linkend="sql-analyze"><command>ANALYZE</command></link> + on the table afterwards is recommended.ANALYZE is not needed for virtual generated columns, since they
don't have statistics in the first place? If so, would it be clearer to
explicitly mention that? How about rephrasing it like this?------------------------------
When this form is used on a stored generated column, its statistics
are removed, so running <link linkend="sql-analyze"><command>ANALYZE
</command></link> on the table afterwards is recommended.
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
------------------------------
Thank you for looking at it. I overlooked that virtual generated columns
don't have statistics currently (I'm working on this at [1]/messages/by-id/20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp, though).
Your suggestion makes sense, so I've attached an updated patch.
I also mentioned virtual generated column in the description
of SET DATA TYPE.
[1]: /messages/by-id/20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp
Regards,
Yugo Nagata
--
Yugo Nagata <nagata@sraoss.co.jp>
Attachments:
v2_fix_doc_alter_table_set_expression.patchtext/x-diff; name=v2_fix_doc_alter_table_set_expression.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1e4f26c13f6..20f0e4a3140 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -207,9 +207,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- When this form is used, the column's statistics are removed,
+ When this form is used, the column's statistics are removed if any,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
on the table afterwards is recommended.
+ For a virtual generated column, <command>ANALYZE</command>
+ is not necessary because such columns never have statistics.
</para>
</listitem>
</varlistentry>
@@ -270,6 +272,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
+
+ <para>
+ When this form is used on a stored generated column, its statistics
+ are removed, so running <link linkend="sql-analyze"><command>ANALYZE
+ </command></link> on the table afterwards is recommended.
+ For a virtual generated column, <command>ANALYZE</command>
+ is not necessary because such columns never have statistics.
+ </para>
</listitem>
</varlistentry>
On Tue, Aug 5, 2025 at 10:09 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:
Thank you for looking at it. I overlooked that virtual generated columns
don't have statistics currently (I'm working on this at [1], though).Your suggestion makes sense, so I've attached an updated patch.
I also mentioned virtual generated column in the description
of SET DATA TYPE.
Thanks for updating the patch!
As for back-patching, ANALYZE recommendation for SET EXPRESSION should
be back-patched to v17, where that feature was introduced? Similarly,
the note about virtual generated columns should go into v18,
since that's when they were added?
Regards,
--
Fujii Masao
On Wed, 6 Aug 2025 13:20:18 +0900
Fujii Masao <masao.fujii@gmail.com> wrote:
On Tue, Aug 5, 2025 at 10:09 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:
Thank you for looking at it. I overlooked that virtual generated columns
don't have statistics currently (I'm working on this at [1], though).Your suggestion makes sense, so I've attached an updated patch.
I also mentioned virtual generated column in the description
of SET DATA TYPE.
Thanks for updating the patch!
As for back-patching, ANALYZE recommendation for SET EXPRESSION should
be back-patched to v17, where that feature was introduced? Similarly,
the note about virtual generated columns should go into v18,
since that's when they were added?
Yes. I believe the back-patching would be better to both v17 and v18.
I've attached a patch for v17.
Regards,
Yugo Nagata
--
Yugo Nagata <nagata@sraoss.co.jp>
Attachments:
for17_fix_doc_alter_table_set_expression.patchtext/x-diff; name=for17_fix_doc_alter_table_set_expression.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 896a7e7baaf..029ddb391ec 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -265,6 +265,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in the column is rewritten and all the future changes will apply the new
generation expression.
</para>
+
+ <para>
+ When this form is used, the column's statistics are removed,
+ so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
+ on the table afterwards is recommended.
+ </para>
</listitem>
</varlistentry>
On Wed, Aug 6, 2025 at 3:44 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:
Yes. I believe the back-patching would be better to both v17 and v18.
I've attached a patch for v17.
Thanks for the patch for v17! I've pushed the patches.
Regards,
--
Fujii Masao