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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Craig de Stigter <craig(dot)destigter(at)koordinates(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: pg_stats.avg_width differs by a factor of 4 on different machines
Date: 2009-05-28 22:42:05
Message-ID: 2059.1243550525@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan McKay 2009-05-28 22:51:15 Re: Please remove me from the list!
Previous Message Jeff Davis 2009-05-28 22:39:57 Re: What is the right way to deal with a table with rows that are not in a random order?