Re: ANALYZE not working?

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