ANALYZE not working?

From: Jeff Boes <jboes(at)nexcerpt(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: ANALYZE not working?
Date: 2003-01-06 20:52:21
Message-ID: avcq6b$187s$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

At some point after we upgraded to Postgres 7.2 from 7.1, we noticed that
VACUUM ANALYZE wasn't updating pg_class.reltuples. It only happened for
systems where we'd upgraded; a fresh installation or a new database was
not affected. So we started using

# vacuum TABLENAME; analyze TABLENAME;

where appropriate. Suddenly (like within the last week or so?), ANALYZE
isn't working properly (it is recording pg_class.reltuples far lower than
the actual row count).

This is just way too weird:

$ psql

# select count(*) from stat_fetch;

count
--------
143243
(1 row)

(Big table, two indexes.)

# analyze stat_fetch;
ANALYZE

(That should update all the stats, right?)

# select relname, reltuples from pg_class
# where relname = 'stat_fetch';
relname | reltuples
------------+-----------
stat_fetch | 3419
(1 row)

(Wha? Huh?)

# vacuum stat_fetch;
VACUUM

(That should NOT impact the stats, right?)

# select relname, reltuples from pg_class
# where relname = 'stat_fetch';
relname | reltuples
------------+-----------
stat_fetch | 179955
(1 row)

(Hmm, a bit bigger this time?!? And why so different
from the count(*)??)

# analyze stat_fetch;
ANALYZE

(Just to make sure it's not a one-time thing)

# select relname, reltuples from pg_class
# where relname = 'stat_fetch';
relname | reltuples
------------+-----------
stat_fetch | 3422
(1 row)

(I give up, I need a strong beverage of some sort ...)

--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Steve Crawford 2003-01-06 20:53:56 Re: restore/dup OIDs HELP!
Previous Message Tom Lane 2003-01-06 20:45:59 Re: Vacuum explained