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"
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
         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    g...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to