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 1sp9ax-00H1lF-7h for pgsql-admin@arkaria.postgresql.org; Fri, 13 Sep 2024 16:56:32 +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 1sp9aw-007OSx-Tj for pgsql-admin@arkaria.postgresql.org; Fri, 13 Sep 2024 16:56:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sp9aw-007ORm-D0 for pgsql-admin@lists.postgresql.org; Fri, 13 Sep 2024 16:56:30 +0000 Received: from mail-lf1-x132.google.com ([2a00:1450:4864:20::132]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sp9at-000zix-Dd for pgsql-admin@postgresql.org; Fri, 13 Sep 2024 16:56:29 +0000 Received: by mail-lf1-x132.google.com with SMTP id 2adb3069b0e04-535be093a43so3254440e87.3 for ; Fri, 13 Sep 2024 09:56:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726246586; x=1726851386; 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=guDe/dZDKUL2Nm4Ximjvf3VYfYsWMvdOcfqDov/Q2UU=; b=brU9BHmy5hEByikTzdFZMEtNZzTKlt+LM2qEKOes3Ti/0bi1TZUh0hTCS9jSnDAsoY tcuduFXPq9kgvnYGNhwt6DalBuJBid6ybU+nzU0x3Po546T8OtPn2P7iRyht5VrFVTFp F9m+9RJ+k/YrhulRuh4LSHIr2UBbja3U/DOoeYYAPfWDu7N3fH5hW0M9ewRqnk9uXgv9 XBI4Q7oRBtSwdsZBpH1SostpMpNG/A3t4kXmJLVVPUGJ661evpYza2qS8VrNW/01F1TB ELLnFUuNtiOHBeInma8QYqqJxjl4x21jG2B2bZwd0o3r7dbLsOEez1r0esKtinP4+kY0 iUBw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726246586; x=1726851386; 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=guDe/dZDKUL2Nm4Ximjvf3VYfYsWMvdOcfqDov/Q2UU=; b=fxQxXfj/CazZp/V4U9e7TExc4M34pIPU9KvJSoo4+rZjN185n+QuoXQTTsjNUB5bz0 UMvn5JDqQc4/ZlbyHgXiUyXimzyNTmIgq33ggygHLJfQVStPVMkiTn+nyeAPSq7bF/lZ eaBEDwuXpiHkZcC13PLcbaGbTi9TSfdFYXT94FE2a3QXNn/p4LYyP+sxktcz+/O2HPtb x55Cnws/5hMMTrUHndD6TV6UyLuXQ2Ul3GTvkAxkseSxZa9vW9ltr5xwI7q+yZ2r2EP1 6oVqelObFhenWLqp+yKyRp0bA4ACAWyHMdpJkmpZXKZEviH0F/wdGGWvEZ0p2kTkO855 Bp4w== X-Gm-Message-State: AOJu0YwWoM5vfcUseIOabBclr8ZifvfTDNt9ic27ezv1VbERSEdohgSO whSfM8D5L3UAqC5NiqM9/m7SpD8o76wTiYIgoZUHOo5rwdcBTmJNfffgoAsKu1VluehT+cIxn2N ywNpa9DqD4BYRcZU39036kwPndYXcHg== X-Google-Smtp-Source: AGHT+IF7ZY7L2kVsFs9Srb6lJU8tLkGs1DpLboMe/GRkrJ7plovGQoXoTvyJ8E2HvhVacTaCK/TeViPc1dVEYaMRbVo= X-Received: by 2002:ac2:4f01:0:b0:533:4560:48b7 with SMTP id 2adb3069b0e04-53678fc9aabmr4662162e87.30.1726246585319; Fri, 13 Sep 2024 09:56:25 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Wells Oliver Date: Fri, 13 Sep 2024 09:55:48 -0700 Message-ID: Subject: Re: Query plan getting less efficient over time with frequent updates and deletes.. To: Ron Johnson Cc: pgsql-admin Content-Type: multipart/alternative; boundary="000000000000834a430622031a96" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000834a430622031a96 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Well, hmm, does it stand to reason that if it's just a stale plan where vacuum and/or analyze is needed, I could execute a VACUUM ANALYZE rather than doing a full pg_repack and it should solve the issue, right? I could try that next time and see if the query plan reverts to the faster plan. On Thu, Sep 12, 2024 at 7:42=E2=80=AFPM Ron Johnson wrote: > On Thu, Sep 12, 2024 at 7:56=E2=80=AFPM Wells Oliver > wrote: > >> Yes, I regularly look at pg_stat_user_tables and in particular >> last_autovacuum and last_autoanalyze and these are always the current da= te >> (or within two days) after our nightly processes soon finish. >> > > "Or within two days". I used to think that was adequate, but now I vacuu= m > and analyze some tables multiple times a day. > > 1.5% autovacuum_X_scale_factor and 200 autovacuum_X_threshold is required > on some tables. > > Because there's sooo many indices on that table, you might have to > manually vacuum it with a pretty high PARALLEL value. > > >> I wondered if the similar low planning time but the dissimilar longer >> execution time might indicate rows are spread out over disk, thereby >> negating a bitmap heap scan and the slower query taking longer due to >> having to read a lot more disk? Is that a possibility? >> > > It was 30 years ago. Modern (like ext2 and newer) filesystems > purposefully spread files across devices. > > >> On Thu, Sep 12, 2024 at 4:47=E2=80=AFPM Ron Johnson >> wrote: >> >>> On Thu, Sep 12, 2024 at 6:52=E2=80=AFPM Wells Oliver >>> wrote: >>> >>>> Hi all: we have a table which receives frequent daily updates and >>>> deletes on the order of 100-600k. The overall row length is approximat= ely >>>> 80m. This table has 50 indexes and 303 columns and is quite frequently >>>> queried by humans and applications. >>>> >>>> I've been in the habit of using pg_repack maybe once a month on this >>>> table because I can't quite figure out why querying gets bogged down. = The >>>> vacuum and analyze thresholds are set such that the table is both auto >>>> vacuumed and analyzed every night. >>>> >>> >>> 1. You're absolutely positive that the VACUUM and ANALYZE complete ever= y >>> night? >>> 2. Nightly may not be often enough. >>> >>> >> >> -- >> Wells Oliver >> wells.oliver@gmail.com >> > > > -- > Death to America, and butter sauce. > Iraq lobster! > --=20 Wells Oliver wells.oliver@gmail.com --000000000000834a430622031a96 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Wel= l, hmm, does it stand=C2=A0to reason that if it's just a stale plan whe= re vacuum and/or analyze is needed, I could execute a VACUUM ANALYZE rather= than doing a full pg_repack and it should solve the issue, right? I could = try that next time and see if the query plan reverts to the faster plan.

On Thu, Sep 12, 2024 at 7:42=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
=
On Thu, Sep 12, 2024 at 7:56=E2=80=AFPM Wells Oliver <wells.oliver@gmail.com= > wrote:
= Yes, I regularly look at pg_stat_user_tables and in particular last_autovac= uum and last_autoanalyze and these are always=C2=A0the current date (or wit= hin two days) after our nightly processes soon finish.

"Or within two days".=C2=A0 I used to thi= nk that was adequate, but now I vacuum and analyze some tables multiple=C2= =A0times a day.

1.5%=C2=A0autovacuum_X_scale_facto= r and 200 autovacuum_X_threshold is required on some tables.

=
Because there's sooo many indices on that table, you might h= ave to manually vacuum it with a pretty high PARALLEL value.
=C2= =A0
I wondered if the similar low planning time= but the dissimilar longer execution time might indicate rows are spread ou= t over disk, thereby negating a bitmap heap scan and the slower query takin= g longer due to having to read a lot more disk? Is that a possibility?
<= /div>

It was 30 years ago.=C2=A0 Mode= rn (like ext2 and newer) filesystems purposefully spread files across devic= es.
=C2=A0
On Thu, Sep 12, 2024 at 4:47=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.c= om> wrote:
On Thu= , Sep 12, 2024 at 6:52=E2=80=AFPM Wells Oliver <wells.oliver@gmail.com> wrote:
Hi all: we have = a table which receives frequent daily updates and deletes on the order of 1= 00-600k. The overall row length is approximately 80m. This table has 50 ind= exes and 303 columns and is quite frequently queried by humans and applicat= ions.

I've been in the habit of using pg_repack maybe once a month on= this table because I can't quite figure out why querying gets bogged d= own. The vacuum and analyze thresholds are set such that the table is both = auto vacuumed and analyzed every night.
=C2=A0=
1. You're absolutely positive that the VACUUM and ANALYZE co= mplete every night?
2. Nightly may not be often enough.



--


--
Death to America, and butter sauce.
Iraq lobster!
<= /div>


--
--000000000000834a430622031a96--