Statistics mismatch between n_live_tup and actual row count - Mailing list pgsql-general

From Andreas Brandl
Subject Statistics mismatch between n_live_tup and actual row count
Date
Msg-id 4260256.28.1323361122333.JavaMail.root@store1.zcs.ext.wpsrv.net
Whole thread Raw
Responses Re: Statistics mismatch between n_live_tup and actual row count  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Hi,

we're currently investigating a statistics issue on postgres. We have some tables which frequently show up with strange
valuesfor n_live_tup. If you compare those values with a count on that particular table, there is a mismatch of factor
10-30.This causes the planner to come up with very bad plans (we also have this issue on bigger table like the one
below).

db=# SELECT relname, n_live_tup, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE schemaname = 's' AND
relname= 't'; 
 relname  | n_live_tup |         last_analyze          |       last_autoanalyze
----------+------------+-------------------------------+-------------------------------
 t        |       7252 | 2011-12-08 03:00:02.556088+01 | 2011-12-01 18:29:00.536321+01

db=# SELECT COUNT(*) FROM s.t;
 count
-------
   280

The strange thing is, if we run an ANALYZE on this table, the statistic is good. 10 minutes later it's bad again. We
suspectone of our processes which might do unnecessary (i.e. blind) updates. Can this be the cause of the statistics
problem?

PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.real (Debian 4.6.2-4) 4.6.2, 64-bit

The migration to 9.1 did not fix this problem, as we already have this since 8.4.

I can provide all postgres configuration, but I don't see anything we changed (compared to the default config), which
mightbe related here. 

Any clue appreciated here!

Best regards
Andy

pgsql-general by date:

Previous
From: Marc Cousin
Date:
Subject: Re: Hope for a new PostgreSQL era?
Next
From: "Nicholson, Brad (Toronto, ON, CA)"
Date:
Subject: Re: Hope for a new PostgreSQL era?