Hello,
The pg_stats.avg_width field is documented [1] as "Average width in
bytes of column's entries" but it's not defined exactly what "entries"
means here with respect to STORAGE (the underlying pg_statistic
documentation doesn't clarify this either). I thought initially this
was the "logical" size of the values, but I ran an experiment that
suggests this interpretation is not right:
maciek=# create table foo(a text);
CREATE TABLE
maciek=# insert into foo(a) select string_agg(floor((random() *
10)::numeric)::text, '') from generate_series(1,1000000) g;
INSERT 0 1
maciek=# analyze foo;
ANALYZE
maciek=# select avg_width from pg_stats where tablename = 'foo' and
attname = 'a';
avg_width
-----------
18
(1 row)
maciek=# select length(a) from foo;
length
---------
1000000
(1 row)
maciek=# select reltoastrelid::regclass from pg_class where relname = 'foo';
reltoastrelid
---------------------------
pg_toast.pg_toast_6454708
(1 row)
maciek=# select sum(length(chunk_data)) from pg_toast.pg_toast_6454708;
sum
--------
724257
(1 row)
So the avg_width here appears to correspond to neither the logical
size nor the compressed toasted size. Am I missing something? Postgres
14.7 in case that matters.
Thanks,
Maciek
[1]: https://www.postgresql.org/docs/current/view-pg-stats.html