Thread: ANALYZE not working?

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

On Mon, 6 Jan 2003, Jeff Boes wrote:
>
> [...] Suddenly [...] ANALYZE isn't working properly (it is recording
> pg_class.reltuples far lower than the actual row count).

I had the same problem recently...
  http://archives.postgresql.org/pgsql-bugs/2002-08/msg00015.php
where "vacuum analyze" and "vacuum; analyze;" were giving me
three orders of magnitude differences in estimates.

Apparently "analyze" is somewhat dependant on the order in which
rows had been inserted in the table; since it just does a (small)
random sample rather than a full table scan.

The thread there has some detailed explanation from Tom about the
underlying cause of the different results and how to diagnose it.


Short summary for me was that for a short term fix, I paid
the price for the slower "vacuum analzye" more frequently;
and later I re-ordered the whole table
  create table tmp_table as select * from my_table order by foo;
  drop table my_table;
  alter table tmp_table rename to my_table;
which made "analyze;" give good estimates again.

Hope this helps.
   Ron

Jeff Boes <jboes@nexcerpt.com> writes:
> 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?)

Have you done VACUUM FULL on this table in living memory?

The current implementation of ANALYZE can get fooled if the table has
very nonuniform tuple density (eg, lots of empty or near-empty pages
near the beginning, and filled pages near the end).

I'd try "vacuum full verbose stat_fetch" and note whether it shrinks the
table a lot.  (If it does, that suggests that you need more frequent
regular vacuums, and/or larger FSM settings in postgresql.conf.)

            regards, tom lane