Search Postgresql Archives

Re: Statistics mismatch between n_live_tup and actual row count

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



tim_wilson <tim.wilson@xxxxxxxxxxx> writes:
> When you say recent releases, does that include 8.4? 

8.4.9 and later --- anything including this commit:

Author: Tom Lane <tgl@xxxxxxxxxxxxx>
Branch: master Release: REL9_1_BR [b4b6923e0] 2011-05-30 17:06:52 -0400
Branch: REL9_0_STABLE Release: REL9_0_5 [73bd34c81] 2011-05-30 17:07:07 -0400
Branch: REL8_4_STABLE Release: REL8_4_9 [b503da135] 2011-05-30 17:07:19 -0400

    Fix VACUUM so that it always updates pg_class.reltuples/relpages.
    
    When we added the ability for vacuum to skip heap pages by consulting the
    visibility map, we made it just not update the reltuples/relpages
    statistics if it skipped any pages.  But this could leave us with extremely
    out-of-date stats for a table that contains any unchanging areas,
    especially for TOAST tables which never get processed by ANALYZE.  In
    particular this could result in autovacuum making poor decisions about when
    to process the table, as in recent report from Florian Helmberger.  And in
    general it's a bad idea to not update the stats at all.  Instead, use the
    previous values of reltuples/relpages as an estimate of the tuple density
    in unvisited pages.  This approach results in a "moving average" estimate
    of reltuples, which should converge to the correct value over multiple
    VACUUM and ANALYZE cycles even when individual measurements aren't very
    good.
    
    This new method for updating reltuples is used by both VACUUM and ANALYZE,
    with the result that we no longer need the grotty interconnections that
    caused ANALYZE to not update the stats depending on what had happened
    in the parent VACUUM command.
    
    Also, fix the logic for skipping all-visible pages during VACUUM so that it
    looks ahead rather than behind to decide what to do, as per a suggestion
    from Greg Stark.  This eliminates useless scanning of all-visible pages at
    the start of the relation or just after a not-all-visible page.  In
    particular, the first few pages of the relation will not be invariably
    included in the scanned pages, which seems to help in not overweighting
    them in the reltuples estimate.
    
    Back-patch to 8.4, where the visibility map was introduced.

			regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux