Thread: BUG #17123: ALTER TABLE ... NO INHERIT leaves orphaned statistics behind
BUG #17123: ALTER TABLE ... NO INHERIT leaves orphaned statistics behind
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 17123 Logged by: Christoph Berg Email address: christoph.berg@credativ.de PostgreSQL version: 10.16 Operating system: Any Description: Removing the last/only inheritance child from a table using ALTER TABLE NO INHERIT leaves orphaned statistics behind that ANALYZE doesn't remove. Original customer complaint on 10.16, but 15devel has the same issue. psql -ef inherit.sql create table log (ts timestamptz, data text); CREATE TABLE Zeit: 2,378 ms insert into log values ('2021-07-01', '1'); INSERT 0 1 Zeit: 0,457 ms insert into log values ('2021-08-01', '2'); INSERT 0 1 Zeit: 0,197 ms create table log2 (like log) inherits (log); psql:inherit.sql:4: HINWEIS: 00000: Spalte »ts« wird mit geerbter Definition zusammengeführt ORT: MergeAttributes, tablecmds.c:2837 psql:inherit.sql:4: HINWEIS: 00000: Spalte »data« wird mit geerbter Definition zusammengeführt ORT: MergeAttributes, tablecmds.c:2837 CREATE TABLE Zeit: 1,396 ms insert into log2 values ('2021-01-01', '3'); INSERT 0 1 Zeit: 0,351 ms insert into log2 values ('2021-02-01', '4'); INSERT 0 1 Zeit: 0,174 ms analyze log; ANALYZE Zeit: 1,093 ms select * from pg_stats where tablename = 'log' and attname = 'ts' ─[ RECORD 1 ]──────────┬────────────────────────────────────────────────────────────────────────────────────────────────────── schemaname │ public tablename │ log attname │ ts inherited │ f null_frac │ 0 avg_width │ 8 n_distinct │ -1 most_common_vals │ ∅ most_common_freqs │ ∅ histogram_bounds │ {"2021-07-01 00:00:00+02","2021-08-01 00:00:00+02"} correlation │ 1 most_common_elems │ ∅ most_common_elem_freqs │ ∅ elem_count_histogram │ ∅ ─[ RECORD 2 ]──────────┼────────────────────────────────────────────────────────────────────────────────────────────────────── schemaname │ public tablename │ log attname │ ts inherited │ t null_frac │ 0 avg_width │ 8 n_distinct │ -1 most_common_vals │ ∅ most_common_freqs │ ∅ histogram_bounds │ {"2021-01-01 00:00:00+01","2021-02-01 00:00:00+01","2021-07-01 00:00:00+02","2021-08-01 00:00:00+02"} correlation │ -0.6 most_common_elems │ ∅ most_common_elem_freqs │ ∅ elem_count_histogram │ ∅ Zeit: 4,616 ms select ctid, stainherit from pg_statistic where starelid = 'log'::regclass and staattnum = 1; ctid │ stainherit ─────────┼──────────── (18,17) │ f (18,19) │ t (2 Zeilen) Zeit: 0,856 ms alter table log2 no inherit log; ALTER TABLE Zeit: 0,449 ms analyze log; ANALYZE Zeit: 0,394 ms select * from pg_stats where tablename = 'log' and attname = 'ts' ─[ RECORD 1 ]──────────┬────────────────────────────────────────────────────────────────────────────────────────────────────── schemaname │ public tablename │ log attname │ ts inherited │ f null_frac │ 0 avg_width │ 8 n_distinct │ -1 most_common_vals │ ∅ most_common_freqs │ ∅ histogram_bounds │ {"2021-07-01 00:00:00+02","2021-08-01 00:00:00+02"} correlation │ 1 most_common_elems │ ∅ most_common_elem_freqs │ ∅ elem_count_histogram │ ∅ ─[ RECORD 2 ]──────────┼────────────────────────────────────────────────────────────────────────────────────────────────────── schemaname │ public tablename │ log attname │ ts inherited │ t <-- not removed null_frac │ 0 avg_width │ 8 n_distinct │ -1 most_common_vals │ ∅ most_common_freqs │ ∅ histogram_bounds │ {"2021-01-01 00:00:00+01","2021-02-01 00:00:00+01","2021-07-01 00:00:00+02","2021-08-01 00:00:00+02"} correlation │ -0.6 most_common_elems │ ∅ most_common_elem_freqs │ ∅ elem_count_histogram │ ∅ Zeit: 1,526 ms select ctid, stainherit from pg_statistic where starelid = 'log'::regclass and staattnum = 1; ctid │ stainherit ─────────┼──────────── (18,21) │ f (18,19) │ t <-- not touched (2 Zeilen) Zeit: 0,373 ms cat inherit.sql create table log (ts timestamptz, data text); insert into log values ('2021-07-01', '1'); insert into log values ('2021-08-01', '2'); create table log2 (like log) inherits (log); insert into log2 values ('2021-01-01', '3'); insert into log2 values ('2021-02-01', '4'); analyze log; select * from pg_stats where tablename = 'log' and attname = 'ts' \gx select ctid, stainherit from pg_statistic where starelid = 'log'::regclass and staattnum = 1; alter table log2 no inherit log; analyze log; select * from pg_stats where tablename = 'log' and attname = 'ts' \gx select ctid, stainherit from pg_statistic where starelid = 'log'::regclass and staattnum = 1;
PG Bug reporting form <noreply@postgresql.org> writes: > Removing the last/only inheritance child from a table using ALTER TABLE NO > INHERIT leaves orphaned statistics behind that ANALYZE doesn't remove. > Original customer complaint on 10.16, but 15devel has the same issue. Good catch. I imagine DETACH PARTITION has related issues? It seems like there are basically two ways to fix this: 1. Make ALTER TABLE NO INHERIT/DETACH PARTITION get rid of no-longer-wanted stats. 2. Make ANALYZE check for and remove any inappropriate stats. I'm kind of inclined to prefer #1, on the grounds that #2 would add almost-always-useless cycles to every ANALYZE run forevermore. On the other hand, #2 seems a bit more robust, in that we'd not have to remember to consider this issue every time somebody invents a new flavor of DDL that affects these things. Thoughts? regards, tom lane
Re: BUG #17123: ALTER TABLE ... NO INHERIT leaves orphaned statistics behind
From
Christoph Berg
Date:
Re: Tom Lane > Good catch. I imagine DETACH PARTITION has related issues? It has, but for partitioned tables I think the case is different. Partitioned tables have inherited=true stats only, and in practice, as the partition parent table is always empty, stale statistics on a partitioned table without any partitions aren't hurting. Maybe one could even argue that keeping the stats around in that case makes sense since they might help with the next partition re-added, like TRUNCATE doesn't reset stats, or like ANALYZE on an empty table doesn't wipe the existing stats. create table log (ts timestamptz) partition by range (ts); create table log2 partition of log for values from ('2021-07-01') to ('2021-08-01'); insert into log values ('2021-07-01'); analyze log; select * from pg_stats where tablename = 'log' and attname = 'ts' \gx alter table log detach partition log2; analyze log; select * from pg_stats where tablename = 'log' and attname = 'ts' \gx Christoph -- Senior Berater, Tel.: +49 2166 9901 187 credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Sascha Heuer, Geoff Richardson, Peter Lilley; Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz