From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Zhihong Yu <zyu@yugabyte.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: autovacuum: handle analyze for partitioned tables
Date: Thu, 13 May 2021 17:33:33 -0400
Message-ID: <20210513213333.GA31073@alvherre.pgsql> (raw)
In-Reply-To: <20210511215616.GA13282@alvherre.pgsql>
New version, a bit more ambitious. I think it's better to describe
behavior for partitioned tables ahead of inheritance. Also, in the
ANALYZE reference page I split the topic in two: in one single paragraph
we now describe what happens with manual analyze for partitioned tables
and inheritance hierarchies; we describe the behavior of autovacuum in
one separate paragraph for each type of hierarchy, since the differences
are stark.
I noticed that difference while verifying the behavior that I was to
document. If you look at ANALYZE VERBOSE output, it seems a bit
wasteful:
create table part (a int) partition by list (a);
create table part0 partition of part for values in (0);
create table part1 partition of part for values in (1);
create table part23 partition of part for values in (2, 3) partition by list (a);
create table part2 partition of part23 for values in (2);
create table part3 partition of part23 for values in (3);
insert into part select g%4 from generate_series(1, 50000000) g;
analyze verbose part;
INFO: analyzing "public.part" inheritance tree
INFO: "part1": scanned 7500 of 55310 pages, containing 1695000 live rows and 0 dead rows; 7500 rows in sample, 12500060 estimated total rows
INFO: "part2": scanned 7500 of 55310 pages, containing 1695000 live rows and 0 dead rows; 7500 rows in sample, 12500060 estimated total rows
INFO: "part3": scanned 7500 of 55310 pages, containing 1695000 live rows and 0 dead rows; 7500 rows in sample, 12500060 estimated total rows
INFO: "part4": scanned 7500 of 55310 pages, containing 1695000 live rows and 0 dead rows; 7500 rows in sample, 12500060 estimated total rows
INFO: analyzing "public.part1"
INFO: "part1": scanned 30000 of 55310 pages, containing 6779940 live rows and 0 dead rows; 30000 rows in sample, 12499949 estimated total rows
INFO: analyzing "public.part2"
INFO: "part2": scanned 30000 of 55310 pages, containing 6779940 live rows and 0 dead rows; 30000 rows in sample, 12499949 estimated total rows
INFO: analyzing "public.part34" inheritance tree
INFO: "part3": scanned 15000 of 55310 pages, containing 3390000 live rows and 0 dead rows; 15000 rows in sample, 12500060 estimated total rows
INFO: "part4": scanned 15000 of 55310 pages, containing 3389940 live rows and 0 dead rows; 15000 rows in sample, 12499839 estimated total rows
INFO: analyzing "public.part3"
INFO: "part3": scanned 30000 of 55310 pages, containing 6780000 live rows and 0 dead rows; 30000 rows in sample, 12500060 estimated total rows
INFO: analyzing "public.part4"
INFO: "part4": scanned 30000 of 55310 pages, containing 6780000 live rows and 0 dead rows; 30000 rows in sample, 12500060 estimated total rows
ANALYZE
--
Álvaro Herrera Valdivia, Chile
"The eagle never lost so much time, as
when he submitted to learn of the crow." (William Blake)
Attachments:
[text/x-diff] 0001-update-docs-on-analyze-on-partitioned-tables.patch (0B, ../20210513213333.GA31073@alvherre.pgsql/2-0001-update-docs-on-analyze-on-partitioned-tables.patch)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: alvherre@alvh.no-ip.org, pryzby@telsasoft.com, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, zyu@yugabyte.com, pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: autovacuum: handle analyze for partitioned tables
In-Reply-To: <20210513213333.GA31073@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox