Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nUG9L-0005bg-K1 for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Mar 2022 23:00:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nUG9K-0001dQ-Ey for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Mar 2022 23:00:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nUG9K-0001dH-0z for pgsql-hackers@lists.postgresql.org; Tue, 15 Mar 2022 23:00:18 +0000 Received: from mail-qt1-x830.google.com ([2607:f8b0:4864:20::830]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nUG9G-0002Ir-Qu for pgsql-hackers@postgresql.org; Tue, 15 Mar 2022 23:00:17 +0000 Received: by mail-qt1-x830.google.com with SMTP id 10so492419qtz.11 for ; Tue, 15 Mar 2022 16:00:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=gS2HPHO3LznKz6ygUCfqAr+qGTfUiWYJ+xq3johbsbk=; b=0Uxuhyiby9CMaqCr/79LAFqglMKt4DAA4auMjpfrQJFRqE8Ji1J8wx1JYhQH2Jj5q8 wP31xRBmo25U6LkfX/bnd/4seILw0qRPJ1QdfXcju5Au105NvdwHAp+2kRyQ9XPq6G+P wdV5l8Hil3GXUS1BwF7hSvCNJDdG4BQvIuRu0tYeSljbSASgdZ5lMUH6v9a3anN8fOKM Eqm/Tsl4nV0jeUs11+BlnFjMiVJjt43I9jrJAESggebGt8pdfjVXAe9HkCg4q8y6Js45 iIOyu/05elatUDQYeQEJu18R3dqBJsdQJtsfzDyWqbnXwmn/Jwt0L+QTie0bZF0zTvbx 6Vrg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=gS2HPHO3LznKz6ygUCfqAr+qGTfUiWYJ+xq3johbsbk=; b=EgYngmDKXhoQV6jDHJwvGeKUD0DPBuyH1V9+IZm+9IbbXz/C/fra/EuuKuXWoRT0rQ euhAEpS//mV+PI0/mbRivbDZ12y0vAst/2t7p/cIQnMhAXRGxe5VoziDiFK0MQXb9MSB pbuaAR3aqWWOxMzBwz0TlMvXDdY131bxWg0md0sNQ3H6Zu0668SckPkt2jsbPClHPb9P zBtAKcIbJsf8eWNOacvpo2EbrKnVm0cPsV/TceiVWJba1mvYrVUR4CUUKiXQd4V2Ly0i +z952YRU4r1pTWVXJH0QBaooPP6/xLEdfHsp8yinTJWQXbbq22zgR3abN1+xP3JDjbBi D94A== X-Gm-Message-State: AOAM532Db1YJJrJqMbkyax7/ONu+DYffv5XzDMr0WNvp4wodOgBUwpsZ /S+v5a7Qzk7QiQzW6Ko0CSOq+g== X-Google-Smtp-Source: ABdhPJyz5nFA4qjHLIGiy/WusmeWOarzAVp73MATDNZUPtkNwbqYhKiVVzZTSou6pjAdpHMypDV2qA== X-Received: by 2002:ac8:590b:0:b0:2e1:e001:e851 with SMTP id 11-20020ac8590b000000b002e1e001e851mr4699034qty.176.1647385212990; Tue, 15 Mar 2022 16:00:12 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id bp9-20020a05622a1b8900b002e0e86b8ac6sm220454qtb.67.2022.03.15.16.00.12 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 15 Mar 2022 16:00:12 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 74762800A8D; Tue, 15 Mar 2022 18:00:11 -0500 (CDT) Date: Tue, 15 Mar 2022 18:00:11 -0500 From: Justin Pryzby To: Robert Haas Cc: Tomas Vondra , =?iso-8859-1?Q?=C1lvaro?= Herrera , yuzuko , pgsql-hackers@postgresql.org Subject: Re: document the need to analyze partitioned tables Message-ID: <20220315230011.GZ28503@telsasoft.com> References: <20210913035409.GA10647@telsasoft.com> <20211008125822.GJ27491@telsasoft.com> <681e76ad-3a7b-ea3c-b6b0-55946239c49b@enterprisedb.com> <20220121180200.GM23027@telsasoft.com> <19c248d9-3687-9e0f-0b1a-2adad1de6fbe@enterprisedb.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="DWg365Y4B18r8evw" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --DWg365Y4B18r8evw Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Mar 14, 2022 at 05:23:54PM -0400, Robert Haas wrote: > On Fri, Jan 21, 2022 at 1:31 PM Tomas Vondra wrote: > > [ new patch ] > > This patch is originally by Justin. The latest version is by Tomas. I > think the next step is for Justin to say whether he's OK with the > latest version that Tomas posted. If he is, then I suggest that he > also mark it Ready for Committer, and that Tomas commit it. If he's > not, he should say what he wants changed and either post a new version > himself or wait for Tomas to do that. Yes, I think it can be Ready. Done. I amended some of Tomas' changes (see 0003, attached as txt). @cfbot: the *.patch file is for your consumption, and the others are only there to show my changes. > I think the fact that is classified as a "Bug Fix" in the CommitFest > application is not particularly good. I would prefer to see it > classified under "Documentation". I'm prepared to concede that > documentation can have bugs as a general matter, but nobody's data is > getting eaten because the documentation wasn't updated. In fact, this > is the fourth patch from the "bug fix" section I've studied this > afternoon, and, well, none of them have been back-patchable code > defects. In fact, I consider this to be back-patchable back to v10. IMO it's an omission that this isn't documented. Not all bugs cause data to be eaten. If someone reads the existing documentation, they might conclude that their partitioned tables don't need to be analyzed, and they would've been better served by not reading the docs. -- Justin --DWg365Y4B18r8evw Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-documentation-deficiencies-for-ANALYZE-of-partitione.patch" From c237d91ec258ebbf24ebd3a38e139777582817f6 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Thu, 22 Jul 2021 16:06:18 -0500 Subject: [PATCH] documentation deficiencies for ANALYZE of partitioned tables This is partially extracted from 1b5617eb844cd2470a334c1d2eec66cf9b39c41a, which was reverted. --- doc/src/sgml/maintenance.sgml | 29 +++++++++++++++++++++++++++++ doc/src/sgml/ref/analyze.sgml | 32 +++++++++++++++++++++++++++++--- 2 files changed, 58 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 36f975b1e5b..34d72dba784 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -290,6 +290,15 @@ to meaningful statistical changes. + + Tuples changed in partitions and inheritance children do not trigger + analyze on the parent table. If the parent table is empty or rarely + changed, it may never be processed by autovacuum, and the statistics for + the inheritance tree as a whole won't be collected. It is necessary to + run ANALYZE on the parent table manually in order to + keep the statistics up to date. + + As with vacuuming for space recovery, frequent updates of statistics are more useful for heavily-updated tables than for seldom-updated @@ -347,6 +356,19 @@ ANALYZE commands on those tables on a suitable schedule. + + + + The autovacuum daemon does not issue ANALYZE commands + for partitioned tables. Inheritance parents will only be analyzed if the + parent itself is changed - changes to child tables do not trigger + autoanalyze on the parent table. If your queries require statistics on + parent tables for proper planning, it is necessary to periodically run + a manual ANALYZE on those tables to keep the statistics + up to date. + + + @@ -819,6 +841,13 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu since the last ANALYZE. + + Partitioned tables are not processed by autovacuum. Statistics + should be collected by running a manual ANALYZE when it is + first populated, and again whenever the distribution of data in its + partitions changes significantly. + + Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze operations should be performed via diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index c423aeeea5e..8268ba87f63 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -263,9 +263,35 @@ ANALYZE [ VERBOSE ] [ table_and_columns - If any of the child tables are foreign tables whose foreign data wrappers - do not support ANALYZE, those child tables are ignored while - gathering inheritance statistics. + For partitioned tables, ANALYZE gathers statistics by + sampling rows from all partitions; in addition, it will recurse into each + partition and update its statistics. Each leaf partition is analyzed only + once, even with multi-level partitioning. No statistics are collected for + only the parent table (without data from its partitions), because with + partitioning it's guaranteed to be empty. + + + + By constrast, if the table being analyzed has inheritance children, + ANALYZE gathers two sets of statistics: one on the rows + of the parent table only, and a second including rows of both the parent + table and all of its children. This second set of statistics is needed when + planning queries that process the inheritance tree as a whole. The child + tables themselves are not individually analyzed in this case. + + + + The autovacuum daemon does not process partitioned tables, nor does it + process inheritance parents if only the children are ever modified. + It is usually necessary to periodically run a manual + ANALYZE to keep the statistics of the table hierarchy + up to date. + + + + If any child tables or partitions are foreign tables whose foreign + data wrappers do not support ANALYZE, those tables are + ignored while gathering inheritance statistics. -- 2.17.1 --DWg365Y4B18r8evw Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="0001-documentation-deficiencies-for-ANALYZE-of-partitione.patch.txt" From ed314a6b257d22fe21b2d13fc52a5d3fe16137b0 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Thu, 22 Jul 2021 16:06:18 -0500 Subject: [PATCH 1/3] documentation deficiencies for ANALYZE of partitioned tables This is partially extracted from 1b5617eb844cd2470a334c1d2eec66cf9b39c41a, which was reverted. --- doc/src/sgml/maintenance.sgml | 27 ++++++++++++++++++++++++++ doc/src/sgml/ref/analyze.sgml | 36 ++++++++++++++++++++++------------- 2 files changed, 50 insertions(+), 13 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 36f975b1e5b..b7c806cc906 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -290,6 +290,14 @@ to meaningful statistical changes. + + Tuples changed in partitions and inheritance children do not count towards + analyze on the parent table. If the parent table is empty or rarely + changed, it may never be processed by autovacuum. It is necessary to + periodically manual run ANALYZE on the parent table to + keep the statistics of its table hierarchy up to date. + + As with vacuuming for space recovery, frequent updates of statistics are more useful for heavily-updated tables than for seldom-updated @@ -347,6 +355,18 @@ ANALYZE commands on those tables on a suitable schedule. + + + + The autovacuum daemon does not issue ANALYZE commands for + partitioned tables. Inheritance parents will only be analyzed if the + parent itself is changed - changes to child tables do not trigger + autoanalyze on the parent table. It is necessary to periodically run a + manual ANALYZE to keep the statistics of the table + hierarchy up to date. + + + @@ -819,6 +839,13 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu since the last ANALYZE. + + Partitioned tables are not processed by autovacuum. Statistics + should be collected by running a manual ANALYZE when it is + first populated, and updated whenever the distribution of data in its + partitions changes significantly. + + Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze operations should be performed via diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index c423aeeea5e..3bf904e36f9 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -250,22 +250,32 @@ ANALYZE [ VERBOSE ] [ table_and_columns - If the table being analyzed has one or more children, - ANALYZE will gather statistics twice: once on the - rows of the parent table only, and a second time on the rows of the - parent table with all of its children. This second set of statistics - is needed when planning queries that traverse the entire inheritance - tree. The autovacuum daemon, however, will only consider inserts or - updates on the parent table itself when deciding whether to trigger an - automatic analyze for that table. If that table is rarely inserted into - or updated, the inheritance statistics will not be up to date unless you - run ANALYZE manually. + If the table being analyzed is partitioned, ANALYZE + will gather statistics by sampling blocks randomly from its partitions; + in addition, it will recurse into each partition and update its statistics. + (However, in multi-level partitioning scenarios, each leaf partition + will only be analyzed once.) + By constrast, if the table being analyzed has inheritance children, + ANALYZE will gather statistics for it twice: + once on the rows of the parent table only, and a second time on the + rows of the parent table with all of its children. This second set of + statistics is needed when planning queries that traverse the entire + inheritance tree. The child tables themselves are not individually + analyzed in this case. - If any of the child tables are foreign tables whose foreign data wrappers - do not support ANALYZE, those child tables are ignored while - gathering inheritance statistics. + The autovacuum daemon does not process partitioned tables, nor does it + process inheritance parents due to modifications to its child tables. + It is usually necessary to periodically run a manual + ANALYZE to keep the statistics of the table hierarchy + up to date. + + + + If any child tables or partitions are foreign tables whose foreign + data wrappers do not support ANALYZE, those tables are + ignored while gathering inheritance statistics. -- 2.17.1 --DWg365Y4B18r8evw Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="0002-minor-changes-rewordings.patch.txt" From c2203a7757f574922edf70b4cd5d85ed4edff7da Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 21 Jan 2022 18:05:56 +0100 Subject: [PATCH 2/3] minor changes / rewordings --- doc/src/sgml/maintenance.sgml | 20 +++++++++--------- doc/src/sgml/ref/analyze.sgml | 38 +++++++++++++++++++++++++---------- 2 files changed, 38 insertions(+), 20 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index b7c806cc906..9e6909cd748 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -291,11 +291,12 @@ - Tuples changed in partitions and inheritance children do not count towards + Tuples changed in partitions and inheritance children do not trigger analyze on the parent table. If the parent table is empty or rarely - changed, it may never be processed by autovacuum. It is necessary to - periodically manual run ANALYZE on the parent table to - keep the statistics of its table hierarchy up to date. + changed, it may never be processed by autovacuum, and the statistics for + the inheritance tree as a whole won't be collected. It is necessary to + run ANALYZE on the parent table manually, to keep + the statistics up to date. @@ -358,12 +359,13 @@ - The autovacuum daemon does not issue ANALYZE commands for - partitioned tables. Inheritance parents will only be analyzed if the + The autovacuum daemon may not issue ANALYZE commands + for partitioned tables. Inheritance parents will only be analyzed if the parent itself is changed - changes to child tables do not trigger - autoanalyze on the parent table. It is necessary to periodically run a - manual ANALYZE to keep the statistics of the table - hierarchy up to date. + autoanalyze on the parent table. If your queries require statistics on + parent tables for proper planning, it's necessary to periodically run + a manual ANALYZE on those tables to keep the statistics + up to date. diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 3bf904e36f9..764cfb19718 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -250,18 +250,34 @@ ANALYZE [ VERBOSE ] [ table_and_columns - If the table being analyzed is partitioned, ANALYZE - will gather statistics by sampling blocks randomly from its partitions; - in addition, it will recurse into each partition and update its statistics. - (However, in multi-level partitioning scenarios, each leaf partition - will only be analyzed once.) + If the table being analyzed has one or more children, + ANALYZE will gather statistics twice: once on the + rows of the parent table only, and a second time on the rows of the + parent table with all of its children. This second set of statistics + is needed when planning queries that traverse the entire inheritance + tree. The autovacuum daemon, however, will only consider inserts or + updates on the parent table itself when deciding whether to trigger an + automatic analyze for that table. If that table is rarely inserted into + or updated, the inheritance statistics will not be up to date unless you + run ANALYZE manually. + + + + For partitioned tables, ANALYZE gathers statistics by + sampling rows from all partitions; in addition, it will recurse into each + partition and update its statistics. Each leaf partition is analyzed only + once, even with multi-level partitioning. No statistics are collected for + the parent table (ignoring data from partitions), because with partitioning + it's guaranteed to be empty. + + + By constrast, if the table being analyzed has inheritance children, - ANALYZE will gather statistics for it twice: - once on the rows of the parent table only, and a second time on the - rows of the parent table with all of its children. This second set of - statistics is needed when planning queries that traverse the entire - inheritance tree. The child tables themselves are not individually - analyzed in this case. + ANALYZE gathers two sets of statistics: once on the rows + of the parent table only, and a second one including rows of both the parent + table and all of its children. This second set of statistics is needed when + planning queries that process the whole inheritance tree at once. The child + tables themselves are not individually analyzed in this case. -- 2.17.1 --DWg365Y4B18r8evw Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="0003-f-3.patch.txt" From 773d02724c8aa4294f46b183afccd021be9dd610 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Tue, 15 Mar 2022 13:38:08 -0500 Subject: [PATCH 3/3] f!3 --- doc/src/sgml/maintenance.sgml | 10 +++++----- doc/src/sgml/ref/analyze.sgml | 12 ++++++------ 2 files changed, 11 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 9e6909cd748..34d72dba784 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -295,8 +295,8 @@ analyze on the parent table. If the parent table is empty or rarely changed, it may never be processed by autovacuum, and the statistics for the inheritance tree as a whole won't be collected. It is necessary to - run ANALYZE on the parent table manually, to keep - the statistics up to date. + run ANALYZE on the parent table manually in order to + keep the statistics up to date. @@ -359,11 +359,11 @@ - The autovacuum daemon may not issue ANALYZE commands + The autovacuum daemon does not issue ANALYZE commands for partitioned tables. Inheritance parents will only be analyzed if the parent itself is changed - changes to child tables do not trigger autoanalyze on the parent table. If your queries require statistics on - parent tables for proper planning, it's necessary to periodically run + parent tables for proper planning, it is necessary to periodically run a manual ANALYZE on those tables to keep the statistics up to date. @@ -844,7 +844,7 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu Partitioned tables are not processed by autovacuum. Statistics should be collected by running a manual ANALYZE when it is - first populated, and updated whenever the distribution of data in its + first populated, and again whenever the distribution of data in its partitions changes significantly. diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 764cfb19718..8268ba87f63 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -267,22 +267,22 @@ ANALYZE [ VERBOSE ] [ table_and_columns By constrast, if the table being analyzed has inheritance children, - ANALYZE gathers two sets of statistics: once on the rows - of the parent table only, and a second one including rows of both the parent + ANALYZE gathers two sets of statistics: one on the rows + of the parent table only, and a second including rows of both the parent table and all of its children. This second set of statistics is needed when - planning queries that process the whole inheritance tree at once. The child + planning queries that process the inheritance tree as a whole. The child tables themselves are not individually analyzed in this case. The autovacuum daemon does not process partitioned tables, nor does it - process inheritance parents due to modifications to its child tables. + process inheritance parents if only the children are ever modified. It is usually necessary to periodically run a manual ANALYZE to keep the statistics of the table hierarchy up to date. -- 2.17.1 --DWg365Y4B18r8evw--