Re: pg_stats.avg_width differs by a factor of 4 on different machines

Craig de Stigter <craig(dot)destigter(at)koordinates(dot)com> writes:
> We are using the PostgreSQL pg_stats view to estimate file sizes for some
> geodata exports. However, the following query gives us totally different
> results on different servers:

> select avg_width from pg_stats where tablename='some_geodata' and attname =
> 'GEOMETRY';

I'm afraid that query is pretty much completely useless for what you
want to do. What it should be giving you is the average width of the
field values on-disk, which is to say after compression and toasting.
It would probably be all right for narrow columns but it's likely to be
a huge underestimate of the external textual size for wide field values.

Having said that, though, these numbers make no sense to me:

> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu
> 4.3.2-1ubuntu11) 4.3.2
>> 81803

> PostgreSQL 8.2.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
> (Ubuntu 4.1.2-0ubuntu4)
>> 20450

It should be impossible to get a value larger than the block size, or
even more than about a quarter of the block size because that's where
TOAST will start doing its thing. Are you running modified source code?

regards, tom lane