Re: Displaying accumulated autovacuum cost

I just took this for spin. Everything I tried worked, docs built and
read fine. The description of how "dirty" differs from "written" is a
bit cryptic, but I don't see an easy way to do better without a whole
new section on that topic. Once the extension upgrade questions are
sorted out, I'd say this is ready to commit. Example I have at the
bottom here shows a case where this is a big improvement over the
existing tracking. I think this is a must-have improvement if we're
going to advocate using pg_stat_statements for more things.

This works as expected in all of the EXPLAIN forms, I tried all of the
supported formats. Sample of the text one:

$ psql -d pgbench -c "EXPLAIN (ANALYZE,BUFFERS,FORMAT text) UPDATE
pgbench_accounts SET aid=aid+0 WHERE aid<1000"
QUERY PLAN
----------
Update on pgbench_accounts (cost=0.00..86.09 rows=860 width=103)
(actual time=8.587..8.587 rows=0 loops=1)
Buffers: shared hit=8315 read=70 dirtied=16
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.00..86.09 rows=860 width=103) (actual time=0.017..2.086 rows=999
loops=1)
Index Cond: (aid < 1000)
Buffers: shared hit=1828 read=28
Total runtime: 8.654 ms

Also ran just the UPDATE statement alone, then retrieved the counts from
pg_stat_statements:

$ psql -x -c "select * from pg_stat_statements"
-[ RECORD 1
]-------+-------------------------------------------------------------------------------------------
userid | 10
dbid | 16385
query | UPDATE pgbench_accounts SET aid=aid+0 WHERE aid<1000
calls | 1
total_time | 0.007475
rows | 999
shared_blks_hit | 8370
shared_blks_read | 15
shared_blks_dirtied | 15
shared_blks_written | 0
...

Note that there are no blocks shown as written there. That is also
demonstrated by the results after some pgbench "-M prepared" stress
testing against a small database. The pgbench tables are structured
such that the number of branches < tellers << accounts. On a small
scale database (I used 10 here), there might only be a single page of
branch data. That shows up clearly in the different amount of dirtied
blocks in each update:

$ psql -x -c "select
query,shared_blks_hit,shared_blks_read,shared_blks_dirtied,shared_blks_written
from pg_stat_statements order by calls desc limit 7"

...
query | UPDATE pgbench_branches SET bbalance = bbalance +
$1 WHERE bid = $2;
shared_blks_hit | 32929
shared_blks_read | 0
shared_blks_dirtied | 1
shared_blks_written | 0

query | UPDATE pgbench_tellers SET tbalance = tbalance +
$1 WHERE tid = $2;
shared_blks_hit | 19074
shared_blks_read | 0
shared_blks_dirtied | 7
shared_blks_written | 0

query | UPDATE pgbench_accounts SET abalance = abalance +
$1 WHERE aid = $2;
shared_blks_hit | 35563
shared_blks_read | 9982
shared_blks_dirtied | 4945
shared_blks_written | 2812

Note how in the branches and tellers case, the existing "written"
counter shows 0. Those hot pages stay in cache the whole time with a
high usage count, backends never get to write them out; only the
checkpointer does. Only this new "dirtied" one reflects a useful write
count for frequently used pages like that, and it does show that more
pages are being touched by pgbench_tellers than pgbench_branches.

I'd never ran into this before because I normally test against larger
databases. But once I tried to find an example of this form, it was
easy to do so. Systems where much of the database fits into
shared_buffers in particular are likely to see a deceptively small write
count.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com