Re: ALTER TABLE ... SET DATA TYPE removes statistics
On 2021-Oct-26, nikolai.berkoff wrote: > Thank you, this reads better. > Is there a reason in the docs there is sometimes > > ANALYZE > > and sometimes only > > ANALYZE > ? I prefer the link if there is no hard rule. I pushed this on November 5th; it is commit df80f9da5c6541e744eeb20eaca919c7fc18, including this suggestion to cross-ref to ANALYZE. Thanks for reporting this -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Re: ALTER TABLE ... SET DATA TYPE removes statistics
‐‐‐ Original Message ‐‐‐ On Tuesday, October 19th, 2021 at 16:16, Alvaro Herrera wrote: > I would put it in a separate one instead, as in the attached. Thank you, this reads better. Is there a reason in the docs there is sometimes ANALYZE and sometimes only ANALYZE ? I prefer the link if there is no hard rule. Regards, Nikolai publickey - nikolai.berkoff@pm.me - 0xD189792D.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: ALTER TABLE ... SET DATA TYPE removes statistics
On Tue, Oct 19, 2021, at 12:16 PM, Alvaro Herrera wrote: > On 2021-Oct-19, Michael Paquier wrote: > > > On Mon, Oct 18, 2021 at 05:15:59PM -0300, Euler Taveira wrote: > > > I agree that it might surprise an user and it would be good to document > > > it. > > > However, it does not belong to the description. I would add it to the > > > Notes > > > section at the end of the ALTER TABLE page. > > > > No objections to the suggested addition and the location of the > > addition (paragraph of SET DATA TYPE rather than "Notes"), but I think > > that the phrasing could be better: > > "The column's statistics are removed, hence a follow-up ANALYZE is > > suited to update the statistics to the new column type." > > Dunno, putting it in the middle of the existing paragraph looks odd to > me. I would put it in a separate one instead, as in the attached. LGTM. I'm not sure if it is worth mentioning that the user needs to ANALYZE only the removed column instead of the whole table. Anyway, it is good to have statistics from the same snapshot. -- Euler Taveira EDB https://www.enterprisedb.com/
Re: ALTER TABLE ... SET DATA TYPE removes statistics
On Tue, Oct 19, 2021 at 12:16:44PM -0300, Alvaro Herrera wrote: > Dunno, putting it in the middle of the existing paragraph looks odd to > me. I would put it in a separate one instead, as in the attached. Fine by me. Thanks! -- Michael signature.asc Description: PGP signature
Re: ALTER TABLE ... SET DATA TYPE removes statistics
On 2021-Oct-19, Michael Paquier wrote: > On Mon, Oct 18, 2021 at 05:15:59PM -0300, Euler Taveira wrote: > > I agree that it might surprise an user and it would be good to document it. > > However, it does not belong to the description. I would add it to the Notes > > section at the end of the ALTER TABLE page. > > No objections to the suggested addition and the location of the > addition (paragraph of SET DATA TYPE rather than "Notes"), but I think > that the phrasing could be better: > "The column's statistics are removed, hence a follow-up ANALYZE is > suited to update the statistics to the new column type." Dunno, putting it in the middle of the existing paragraph looks odd to me. I would put it in a separate one instead, as in the attached. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 81291577f8..86fc15c8b3 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -198,6 +198,12 @@ WITH ( MODULUS numeric_literal, REM clause must be provided if there is no implicit or assignment cast from old to new type. + + + When this form is used, the column's statistics are removed, + so running ANALYZE on the table afterwards + is recommended. +
Re: ALTER TABLE ... SET DATA TYPE removes statistics
On Mon, Oct 18, 2021 at 05:15:59PM -0300, Euler Taveira wrote: > I agree that it might surprise an user and it would be good to document it. > However, it does not belong to the description. I would add it to the Notes > section at the end of the ALTER TABLE page. No objections to the suggested addition and the location of the addition (paragraph of SET DATA TYPE rather than "Notes"), but I think that the phrasing could be better: "The column's statistics are removed, hence a follow-up ANALYZE is suited to update the statistics to the new column type." -- Michael signature.asc Description: PGP signature
Re: ALTER TABLE ... SET DATA TYPE removes statistics
On Fri, Oct 8, 2021, at 6:03 AM, nikolai.berkoff wrote: > The current documentation does not mention that the column statistics are > removed which I can see they are in src/backend/commands/tablecmds.c > > ATExecAlterColumnType > /* > * Drop any pg_statistic entry for the column, since it's now wrong type > */ > RemoveStatistics(RelationGetRelid(rel), attnum); > > Although this might be obvious it tripped me up. For example renaming and > SET STATISTICS preserves statistics. Patch attached. I agree that it might surprise an user and it would be good to document it. However, it does not belong to the description. I would add it to the Notes section at the end of the ALTER TABLE page. -- Euler Taveira EDB https://www.enterprisedb.com/
