[andrew@tao11.riddles.org.uk]

The following bug has been logged on the website:

Bug reference:      14057
Logged by:          Andrew Gierth
Email address:      andrew@tao11.riddles.org.uk
PostgreSQL version: 9.4.5
Operating system:   any
Description:        

This is my analysis of an issue reported via IRC by
nicolas.baccelli@gmail.com.

The original issue was bad query plans caused by strangely bad estimates,
which were traced to reltuples=0 (with relpages>0) values in pg_class. The
affected relations are very small (one page only, order of 10 to 100 rows).

Monitoring over time showed that these were being reset to 0 by autovacuum
(even though the tables involved are static). This was traced to
vacuum-for-wraparound, which is relevant since it means that the vacuum is
being performed with scan_all true. (The tables are targets of FKs, thus
many key-share locks which may require mxid cleanup.)

The problem then seems to be this:

If cleanup lock isn't acquired for the page when we try and lock it
conditionally, and scan_all is true, then we scan the page to see if it
needs freezing before blocking on the cleanup lock. If it does not, we skip
it, but scanned_pages is still incremented in this code path, even though we
did not update any of the tuple counts.

(We are assuming that the cleanup lock is frequently missed in this case
because the vulnerable tables are frequently used in joins.)

Accordingly, we get a new reltuples estimate of 0 (since scanned_pages is
not 0 the tuple counts are trusted and assumed to reflect the whole rel,
since it's only one page).

It looks like fixing this requires breaking scanned_pages out into at least
two separate counters, since we currently use it to track both whether we
can update stats, and whether we can update relfrozenxid.


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