On Mon, 4 Aug 2025 20:01:34 +0900
Fujii Masao <masao.fu...@gmail.com> wrote:
> On Mon, Aug 4, 2025 at 3:14 PM Yugo Nagata <nag...@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], 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]
https://www.postgresql.org/message-id/20250801002830.143b25971fb9594b89d96aee%40sraoss.co.jp
Regards,
Yugo Nagata
--
Yugo Nagata <nag...@sraoss.co.jp>
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>
