Re: 9.1.3 backends getting stuck in 'startup'

Jeff Frost <jeff(at)pgexperts(dot)com> writes:
> On 05/24/12 12:21, Tom Lane wrote:
>> How big is pg_attribute normally in this database? I'm suspicious that
>> what triggered this is some major bloating of pg_attribute (and maybe
>> some of the other catalogs too).

> So, the current working system's pg_attribute looks like:
> select pg_size_pretty(pg_relation_size('pg_catalog.pg_attribute'));
> pg_size_pretty
> ----------------
> 1671 MB
> (1 row)

> and on the old broken system:
> select pg_size_pretty(pg_relation_size('pg_catalog.pg_attribute'));
> pg_size_pretty
> ----------------
> 1858 MB
> (1 row)

Huh. A bit bigger, but not by that much. It doesn't seem like this
would be enough to make seqscan performance fall off a cliff, as it
apparently did. Unless maybe the slightly-bloated catalogs were just a
bit too large to fit in RAM, and so the repeated seqscans went from
finding all their data in kernel disk buffers to finding none of it?

> So, interestingly, they're both quite large, but the old broken system is
> quite a bit larger. Any other data points be helpful?

I think it would be interesting to get the pg_relation_size for pg_class
plus pg_attribute plus pg_index (which I think is everything that gets
seqscannedd in this way) on both systems, and see how those numbers
match up to total RAM on the box.

regards, tom lane