Re: ANALYZE estimates very wrong reltuples - Mailing list pgsql-admin

From Qingbo Zhou
Subject Re: ANALYZE estimates very wrong reltuples
Date
Msg-id CAA-FGaJLCF-iYHowFBrRxz5pCSNAMas0B=MJ6b215sfN4sE9VQ@mail.gmail.com
Whole thread Raw
In response to Re: ANALYZE estimates very wrong reltuples  (Albe Laurenz <laurenz.albe@wien.gv.at>)
List pgsql-admin

Thanks for the pointer! I think I need to read some docs about the planner statistics.

I tried 1000 and the result was 36M, closer but not enough.

Then tried 10000, it tool half an hour to scan all the pages and got the accurate number.

Thanks again!

On Wed, Feb 20, 2013 at 6:50 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Qingbo Zhou wrote:
> I have a table which has ~26M records in it. But pg_class shows ~35M in the "reltuples" column.
> last_autovacuum is null, and last_autoanalyze was just on yesterday.
>
> I tried running vacuum analyze on it, but the analyze result shows:
>
> INFO:  "[table name]": scanned 30000 of 950145 pages, containing 805842 live rows and 11109 dead rows;
> 30000 rows in sample, 42266462 estimated total rows
>
>
> Even worse estimates. I know that it's doing an estimate, but it's too wrong. I have another table for
> which pg_class estimates 3 times the actual number of rows.
>
> I'm using 9.1.3 [...]

Could you try with different settings for default_statistics_target
(1000, 10000) and see if that changes the result of
ANALYZE VERBOSE?

Yours,
Laurenz Albe

pgsql-admin by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: ANALYZE estimates very wrong reltuples
Next
From: Viktor
Date:
Subject: Pg 9.1 master-slave replication