Re: log_autovacuum in Postgres 14 -- ordering issue - Mailing list pgsql-hackers

On 2021-Aug-25, Peter Geoghegan wrote:

> That way the overall structure starts with details of the physical
> data structures (the table and its indexes), then goes into buffers
> 
> 1. Heap pages
> 2. Heap tuples
> 3. Index stuff
> 4. I/O timings (only when track_io_timing is on)
> 5. avg read rate (always)
> 6. buffer usage
> 7. WAL usage.
> 8. system usage
> 
> This would mean that I'd be flipping the order of 7 and 8 relative to
> Postgres 13 -- meaning there'd be one difference between Postgres 14
> and some existing stable release. But I think that putting WAL usage
> last of all (after system usage) makes little sense -- commit
> b7ce6de93b shouldn't have done it that way. I always expect to see the
> getrusage() stuff last.

You mean:

LOG:  automatic vacuum of table "regression.public.bmsql_order_line": index scans: 1
pages: 0 removed, 8810377 remain, 0 skipped due to pins, 3044924 frozen
tuples: 16819838 removed, 576364686 remain, 2207444 are dead but not yet removable, oldest xmin: 88197949
index scan needed: 1959301 pages from table (22.24% of total) had 11745226 dead item identifiers removed
index "bmsql_order_line_pkey": pages: 2380261 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 65813.666 ms, write: 11310.689 ms
avg read rate: 65.621 MB/s, avg write rate: 48.403 MB/s
buffer usage: 174505 hits, 7630386 misses, 5628271 dirtied
WAL usage: 7387358 records, 4051205 full page images, 28472185998 bytes
system usage: CPU: user: 72.55 s, system: 52.07 s, elapsed: 908.42 s

I like it better than the current layout, so +1.


I think the "index scan needed" line (introduced very late in the 14
cycle, commit 5100010ee4d5 dated April 7 2021) is a bit odd.  It is
telling us stuff about the table -- how many pages had TIDs removed, am
I reading that right? -- and it is also telling us whether indexes were
scanned. But the fact that it starts with "index scan needed" suggests
that it's talking about indexes.  I think we should reword this line.  I
don't have any great ideas; what do you think of this?

dead items: 1959301 pages from table (22.24% of total) had 11745226 dead item identifiers removed; index scan {needed,
notneeded, bypassed, bypassed by failsafe}
 

I have to say that I am a bit bothered about the coding pattern used to
build this sentence from two parts.  I'm not sure it'll work okay in
languages that build sentences in different ways.  Maybe we should split
this in two lines, one to give the numbers and the other to talk about
the decision taken about indexes.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"This is what I like so much about PostgreSQL.  Most of the surprises
are of the "oh wow!  That's cool" Not the "oh shit!" kind.  :)"
Scott Marlowe, http://archives.postgresql.org/pgsql-admin/2008-10/msg00152.php