Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgRhK-002HYX-47 for pgsql-hackers@arkaria.postgresql.org; Tue, 20 Aug 2024 16:27:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sgRhI-001IPP-9k for pgsql-hackers@arkaria.postgresql.org; Tue, 20 Aug 2024 16:27:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgRhH-001IPH-Ui for pgsql-hackers@lists.postgresql.org; Tue, 20 Aug 2024 16:27:04 +0000 Received: from mail-oi1-x231.google.com ([2607:f8b0:4864:20::231]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sgRhE-000diW-TB for pgsql-hackers@postgresql.org; Tue, 20 Aug 2024 16:27:03 +0000 Received: by mail-oi1-x231.google.com with SMTP id 5614622812f47-3db130a872fso3550908b6e.2 for ; Tue, 20 Aug 2024 09:27:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724171220; x=1724776020; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zMVfsmcWs3IVgmnH7u7+7NvfOZNzxVsJucdP/f1XBMw=; b=b1jmMfnHSZHeqSrodnqTIzc7GEEf14L8mCGewfCPnY+qfTEOJInywyk4wv/HPdFSfl 4tQCryc7ewTwjQePDUBEP0TMcfpOMQ+4jNOnVDLRKJ3aUatYKHqcETIWdrvLf1Ab01Ij ndSoMkPxvV+YW6JWZLEChXWBNjrjvp0HLGWH0aW7r79TV6h//NAcU3t6x6qZZWgQWMuU eOkNH8t3SElXLVR0tTr+/axSHjTtFZtumSMnZKZK+b/5q13WnbLM1r+aCC67sKV19Y60 ib0YM1NFAAGgAhyOsA/H7bL0OChYvtAIJmP4gwbrwBZtbYqfERmPe+OUOlgOTkexGy7w fByg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724171220; x=1724776020; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=zMVfsmcWs3IVgmnH7u7+7NvfOZNzxVsJucdP/f1XBMw=; b=ZnnyDyZXbpyNaym4X55ylbJOaDvRaPLJCvDQPJ+5QfHsZDmguwpq/T30ckgp61QqnV Pc7RgWGHUD6uJnBCUylaJv5whQ/ja8FfUMgWinBVL5qSHViFsi8a8E08BJ04tiKzC5r1 LAO38Won47v+uQSj3irbLv34OoIPYIyiE9dP4e/XBqNvclTce5yKVKU1wDiLk3IKwDOr AJRGb8GoxFyNNW7lV+xA0e8VNoSuD7JZjK+sL9AQONt9H8/iARDgGiVOM7LVnmLe3jrm Kj59umB/iZdKzQqE42nmRPJU66MAK0hOZFfHnGC+xfxwcQoupbaQf/YxjNMNz1TVBvwF LDPw== X-Gm-Message-State: AOJu0Ywbsz1fNOMBz9PKXodGCTaNUov75ugs5Os2UCYLiD6TKYPEolCW PX9ZcircvFzOmFxCis61/JNhf+hbHBmhb9K6lw9HD+gZPKkq3Ce+kSn3/gG04mR7BUxPEPuNmwG ICsXtaBNOLZw/e5TK0Ed0Pmvgi7w= X-Google-Smtp-Source: AGHT+IFeOeTY+DuWoZm489AmPbVi8KHx+BbBzgpy2QS48o0zo/0AIIVdRMoLuM2/DabOwlaiSvrda/5zW2AO7ofJYkQ= X-Received: by 2002:a05:6808:1403:b0:3da:ac2c:4c92 with SMTP id 5614622812f47-3dd3ad399b3mr19212623b6e.23.1724171220392; Tue, 20 Aug 2024 09:27:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Melih Mutlu Date: Tue, 20 Aug 2024 19:26:48 +0300 Message-ID: Subject: Re: ANALYZE ONLY To: Michael Harris Cc: PostgreSQL-development , Jelte Fennema , David Rowley Content-Type: multipart/alternative; boundary="0000000000001fa65206201fe50d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001fa65206201fe50d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Michael, Thanks for starting this thread. I've also spent a bit time on this after reading your first thread on this issue [1] Michael Harris , 20 A=C4=9Fu 2024 Sal, 08:52 tarihinde = =C5=9Funu yazd=C4=B1: > The problem is that giving an ANALYZE command targeting a partitioned tab= le > causes it to update statistics for the partitioned table AND all the > individual > partitions. There is currently no option to prevent it from including the > partitions. > > This is wasteful for our application: for one thing the autovacuum > has already analyzed the individual partitions; for another most of > the partitions > will have had no changes, so they don't need to be analyzed repeatedly. > I agree that it's a waste to analyze partitions when they're already analyzed by autovacuum. It would be nice to have a way to run analyze only on a partitioned table without its partitions. > I took some measurements when running ANALYZE on one of our tables. It > took approx > 4 minutes to analyze the partitioned table, then 29 minutes to analyze th= e > partitions. We have hundreds of these tables, so the cost is very > significant. > I quickly tweaked the code a bit to exclude partitions when a partitioned table is being analyzed. I can confirm that there is a significant gain even on a simple case like a partitioned table with 10 partitions and 1M rows in each partition. 1. Would such a feature be welcomed? Are there any traps I might not > have thought of? > > 2. The existing ANALYZE command has the following structure: > > ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ] > > It would be easiest to add ONLY as another option, but that > doesn't look quite > right to me - surely the ONLY should be attached to the table name > An alternative would be: > > ANALYZE [ ( option [, ...] ) ] [ONLY] [ table_and_columns [, ...] ] > I feel closer to adding this as an option instead of a new keyword in ANALYZE grammar. To me, it would be easier to have this option and then give the names of partitioned tables as opposed to typing ONLY before each partition table. But we should think of another name as ONLY is used differently (attached to the table name as you mentioned) in other contexts. I've been also thinking about how this new option should affect inheritance tables. Should it have just no impact on them or only analyze the parent table without taking child tables into account? There are two records for an inheritance parent table in pg_statistic, one row for only the parent table and a second row including children. We might only analyze the parent table if this new "ONLY" option is specified. I'm not sure if that would be something users would need or not, but I think this option should behave similarly for both partitioned tables and inheritance tables. If we decide to go with only partition tables and not care about inheritance, then naming this option to SKIP_PARTITIONS as Jelte suggested sounds fine. But that name wouldn't work if this option will affect inheritance tables. Thanks, --=20 Melih Mutlu Microsoft --0000000000001fa65206201fe50d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Michael,

Thanks fo= r starting this thread. I've also spent a bit time on this after readin= g your first thread on this issue [1]=C2=A0

Michael Harris <<= a href=3D"mailto:harmic@gmail.com">harmic@gmail.com>, 20 A=C4=9Fu 20= 24 Sal, 08:52 tarihinde =C5=9Funu yazd=C4=B1:
The problem is that giving an ANALYZE command targeting a partitioned table=
causes it to update statistics for the partitioned table AND all the indivi= dual
partitions. There is currently no option to prevent it from including the partitions.

This is wasteful for our application: for one thing the autovacuum
has already analyzed the individual partitions; for another most of
the partitions
will have had no changes, so they don't need to be analyzed repeatedly.=

I agree that it's a waste to analyze part= itions when they're already analyzed by autovacuum. It would be nice to= have a way to run analyze only on a partitioned table without its partitio= ns.

=C2=A0
I took some measurements when running ANALYZE on one of our tables. It
took approx
4 minutes to analyze the partitioned table, then 29 minutes to analyze the<= br> partitions. We have hundreds of these tables, so the cost is very significa= nt.

I quickly tweaked the code a bit to exclu= de partitions when a partitioned table is being analyzed. I can confirm tha= t there is a significant gain even on a simple case like a partitioned tabl= e with 10 partitions and 1M rows in each partition.

=C2=A0 1. Would such a feature be welcomed? Are there any traps I might not=
have thought of?

=C2=A0 2. The existing ANALYZE command has the following structure:

=C2=A0 =C2=A0 =C2=A0ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, .= ..] ]

=C2=A0 =C2=A0 =C2=A0It would be easiest to add ONLY as another option, but = that
doesn't look quite
=C2=A0 =C2=A0 =C2=A0right to me - surely the ONLY should be attached to the= table name
=C2=A0 =C2=A0 =C2=A0An alternative would be:

=C2=A0 =C2=A0 =C2=A0ANALYZE [ ( option [, ...] ) ] [ONLY] [ table_and_colum= ns [, ...] ]

I feel closer to adding this as an opt= ion instead of a new keyword=C2=A0in ANALYZE grammar. To me, it would be ea= sier to have this option and then give the names of partitioned tables as o= pposed to typing ONLY before each partition table.
But we should think of ano= ther name as ONLY is used differently (attached to the table name as you me= ntioned) in other contexts.

I've been also thinking about how this new= option should affect inheritance tables. Should it have just no impact on = them or only analyze the parent table without taking child tables into acco= unt? There are two records for an inheritance parent table in pg_statistic,= one row for only the parent table and a second row including children. We = might only analyze the parent table if this new "ONLY" option is = specified. I'm not sure if that would be something users would need or = not, but I think this option should behave similarly for both partitioned t= ables and inheritance tables.

If we decide to go with only partition table= s and not care about inheritance, then naming this option to=C2=A0SKIP_PARTITIONS as Jelte sugg= ested sounds fine. But that name wouldn't work if this option will affe= ct inheritance tables.


Thanks,
--
Melih Mutlu
Microsof= t
--0000000000001fa65206201fe50d--