Horton

Thread

Commits

  1. doc: Recommend ANALYZE after ALTER TABLE ... SET EXPRESSION AS.

  1. Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION

    Yugo Nagata <nagata@sraoss.co.jp> — 2025-08-04T06:14:18Z

    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

  2. Re: Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION

    Fujii Masao <masao.fujii@gmail.com> — 2025-08-04T11:01:34Z

    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

  3. Re: Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION

    Yugo Nagata <nagata@sraoss.co.jp> — 2025-08-05T01:09:06Z

    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], 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 <nagata@sraoss.co.jp>

  4. Re: Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION

    Fujii Masao <masao.fujii@gmail.com> — 2025-08-06T04:20:18Z

    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

  5. Re: Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION

    Yugo Nagata <nagata@sraoss.co.jp> — 2025-08-06T06:43:58Z

    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>

  6. Re: Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION

    Fujii Masao <masao.fujii@gmail.com> — 2025-08-06T07:51:23Z

    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