Re: Use pgBufferUsage for block reporting in analyze

On Wed, Jul 31, 2024 at 12:03 AM Anthonin Bonnefoy
<anthonin(dot)bonnefoy(at)datadoghq(dot)com> wrote:
>
> On Tue, Jul 30, 2024 at 9:21 AM Anthonin Bonnefoy
> <anthonin(dot)bonnefoy(at)datadoghq(dot)com> wrote:
> > A possible change would be to pass an inh flag when an acquirefunc is
> > called from acquire_inherited_sample_rows. The acquirefunc could then
> > use an alternative log format to append to logbuf. This way, we could
> > have a more compact format for partitioned tables.
>
> I've just tested this, the result isn't great as it creates an
> inconsistent output
>
> INFO: analyze of table "postgres.public.test_partition"
> "test_partition_1": scanned 5 of 5 pages, containing 999 live tuples
> and 0 dead tuples; 999 rows in sample, 999 estimated total rows
> "test_partition_2": scanned 5 of 5 pages, containing 1000 live tuples
> and 0 dead tuples; 1000 rows in sample, 1000 estimated total rows
> avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
> ...
> INFO: analyze of table "postgres.public.test_partition_1"
> pages: 5 of 5 scanned
> tuples: 999 live tuples, 0 are dead; 999 tuples in sample, 999
> estimated total tuples
> avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
>
> Maybe the best approach is to always use the compact form?
>
> INFO: analyze of table "postgres.public.test_partition"
> "test_partition_1": scanned 5 of 5 pages, containing 999 live tuples
> and 0 dead tuples; 999 tuples in sample, 999 estimated total tuples
> "test_partition_2": scanned 5 of 5 pages, containing 1000 live tuples
> and 0 dead tuples; 1000 tuples in sample, 1000 estimated total tuples
> avg read rate: 1.953 MB/s, avg write rate: 0.000 MB/s
> ...
> INFO: analyze of table "postgres.public.test_partition_1"
> "test_partition_1": scanned 5 of 5 pages, containing 999 live tuples
> and 0 dead tuples; 999 tuples in sample, 999 estimated total tuples
> avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
>
> I've updated the patchset with those changes. 0004 introduces the
> StringInfo logbuf so we can output logs as a single log and during
> ANALYZE VERBOSE while using the compact form.
>

Fair point. I'll consider a better output format.

Meanwhile, I think we can push 0001 and 0002 patches since they are in
good shape. I've updated commit messages to them and slightly changed
0002 patch to write "finished analyzing of table \"%s.%s.%s\" instead
of "analyze of table \"%s.%s.%s\".

Also, regarding 0003 patch, what is the main reason why we want to add
WAL usage to analyze reports? I think that analyze normally does not
write WAL records much so I'm not sure it's going to provide a good
insight for users.

Regards,

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