Re: Use pgBufferUsage for block reporting in analyze

On Mon, Jul 8, 2024 at 2:35 AM Anthonin Bonnefoy
<anthonin.bonnefoy@datadoghq.com> wrote:
>
> Hi,
>
> Thanks for the review, I've updated the patches with the suggestions:
> - moved renaming of misses to reads to the first patch
> - added intermediate variables for total blks usage
>

Thank you for working on this. 0001 patch looks good to me.

I like the 0002 patch idea. But with this patch, ANALYZE VERBOSE
writes something like this:

INFO:  analyzing "public.test"
INFO:  "test": scanned 443 of 443 pages, containing 100000 live rows
and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
INFO:  analyze of table "postgres.public.test"
avg read rate: 38.446 MB/s, avg write rate: 0.000 MB/s
buffer usage: 265 hits, 187 reads, 0 dirtied
WAL usage: 4 records, 0 full page images, 637 bytes
system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s

Which seems not to be consistent with what we do in VACUUM VERBOSE in
some points. For example, in VACUUM VERBOSE outputs, we write
statistics of pages, tuples, buffer usage, and WAL usage in one INFO
message:

INFO:  vacuuming "postgres.public.test"
INFO:  finished vacuuming "postgres.public.test": index scans: 0
pages: 0 removed, 443 remain, 1 scanned (0.23% of total)
tuples: 0 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 754, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
avg read rate: 23.438 MB/s, avg write rate: 0.000 MB/s
buffer usage: 5 hits, 3 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

I'd suggest writing analyze verbose messages as something like:

INFO: finished analyzing "postgres.public.test"
pages: 443 of 443 scanned
tuples: 100000 live tuples, 0 are dead; 30000 tuples in sample, 100000
estimated total tuples
avg read rate: 38.446 MB/s, avg write rate: 0.000 MB/s
buffer usage: 265 hits, 187 reads, 0 dirtied
WAL usage: 4 records, 0 full page images, 637 bytes
system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s

The first line would vary depending on whether an autovacuum worker or
not. And the above suggestion includes a change of term "row" to
"tuple" for better consistency with VACUUM VERBOSE outputs. I think it
would be great if autoanalyze also writes logs in the same format.
IIUC with the patch, autoanalyze logs don't include the page and tuple
statistics.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com