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 1sowJ4-00EyeI-Uw for pgsql-admin@arkaria.postgresql.org; Fri, 13 Sep 2024 02:45:12 +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 1sowJ4-007ucF-Jj for pgsql-admin@arkaria.postgresql.org; Fri, 13 Sep 2024 02:45:10 +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 1sowFt-007lJQ-B0 for pgsql-admin@lists.postgresql.org; Fri, 13 Sep 2024 02:41:53 +0000 Received: from mail-oi1-x229.google.com ([2607:f8b0:4864:20::229]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sowFp-000uhn-NC for pgsql-admin@postgresql.org; Fri, 13 Sep 2024 02:41:52 +0000 Received: by mail-oi1-x229.google.com with SMTP id 5614622812f47-3e0465e6bd5so163062b6e.2 for ; Thu, 12 Sep 2024 19:41:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726195309; x=1726800109; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=FJM2lvXjhCmPRpx2otZ7uB9/kWcOuC+zPYSyTNSCYms=; b=YImHYt6G6la7Z8aEfLXly1/YEjxCT1tvqiBWHRYYIra8MMhCmuUDTFpsGs9+Ig6Gie LVzzrf7OavFARny8odhOo7/hhgQYZqeGbIHtP+1iiQx2mn/aaexRYc4MbJ2SeJlkiZhZ B4QBSL68sIebTXK/pqlSdzH/nvAD4LeinqwrtNTkHGz54hC86ZG4ZjN4Wv5EStzj7j2R KgtNP0v8aRY0x5QO93r6aXH+Ek+xeAbBsjjZH1N7Frumw0gCKWs4D4lt/h9h29WJKkb7 +d5+GIvTj7hcZN4+TjBgC/ACYa7GRJTahQ8ooT/mVppfPgprwTXA48t+rRIcE00X2v2Z uZlQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726195309; x=1726800109; h=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=FJM2lvXjhCmPRpx2otZ7uB9/kWcOuC+zPYSyTNSCYms=; b=inmdhW0/SlOymzBHSfpNKSzTX00h4q+563C5kS9Xg+JV5vKs2oO2hunCwcZwzCcZQj Ud38+e0/zJ4WmChXeBFcT4+z6lFpeumbx7dOXVlnnw2d4oXRukVHGivpup3VoJtcte8x vvpOs8fk21p441YtxOa0xGo6217wu2eZkZcpSeMU7f0/VgIDCAnAJX2Dzm22E1bjUkqL VdUR4WUpDIgqcYyHaFREHDCsowwy6EtId/vkz24yWw2xevv84vigMosBXTBFRq0iYclQ v8YyPC444/idXZG4+HQhGq0Addk1ECTaNOqIc63uEB4aEND8HRIsEhRot2/qBw9Q+RIW 221Q== X-Gm-Message-State: AOJu0Yw3VA3yOGvuY+L87uWbF6J/0bXqYVE1iOpYUjIHLqrqUjONENfA GICWHu4d7rq7qn8rjzYQcuamtBbsypf01oERrECv8B9dKpe+4Xty61WOnwFOS3Y/H0no0WJq4w/ PzeUZgVOyruBW5nNPnHh2WQFGDpCgYrhb X-Google-Smtp-Source: AGHT+IHkrwCEwNI8lkQd1baYQbCWmxx8yzctuer+Bh1rl4zTdmKPJ1KjmAa6Aw+FDC0hdsXs8qblN6u9RgJsd5NrgYY= X-Received: by 2002:a05:6808:1a14:b0:3e0:465d:1cf2 with SMTP id 5614622812f47-3e07a18657bmr717015b6e.40.1726195308654; Thu, 12 Sep 2024 19:41:48 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 12 Sep 2024 22:41:37 -0400 Message-ID: Subject: Re: Query plan getting less efficient over time with frequent updates and deletes.. To: pgsql-admin Content-Type: multipart/alternative; boundary="0000000000002f7e810621f72a2e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002f7e810621f72a2e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 dat= e > (or within two days) after our nightly processes soon finish. > "Or within two days". I used to think that was adequate, but now I vacuum 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 approximate= ly >>> 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. T= he >>> 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 every >> night? >> 2. Nightly may not be often enough. >> >> > > -- > Wells Oliver > wells.oliver@gmail.com > --=20 Death to America, and butter sauce. Iraq lobster! --0000000000002f7e810621f72a2e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Sep 12, 2024 at 7:56=E2=80=AFPM W= ells Oliver <wells.oliver@gmai= l.com> wrote:
Yes, I regularly look at pg_stat_user_tables and in particular last_a= utovacuum and last_autoanalyze and these are always=C2=A0the current date (= or within two days) after our nightly processes soon finish.

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

1.5%=C2=A0autovacuum_X_scale_= factor and 200 autovacuum_X_threshold is required on some tables.

Because there's sooo many indices on that table, you mi= ght have 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 sprea= d out over disk, thereby negating a bitmap heap scan and the slower query t= aking longer due to having to read a lot more disk? Is that a possibility?<= br>

It was 30 years ago.=C2=A0 = Modern (like ext2 and newer) filesystems purposefully spread files across d= evices.
=C2=A0
On Thu, Sep 12, 2024 at 4:47=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmai= l.com> wrote:
On = Thu, Sep 12, 2024 at 6:52=E2=80=AFPM Wells Oliver <wells.oliver@gmail.com> wrote= :
Hi all: we hav= e a table which receives frequent daily updates and deletes on the order of= 100-600k. The overall row length is approximately 80m. This table has 50 i= ndexes and 303 columns and is quite frequently queried by humans and applic= ations.

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 bot= h auto vacuumed and analyzed every night.
=C2= =A0
1. You're absolutely positive that the VACUUM and ANALYZE= complete every night?
2. Nightly may not be often enough.
<= /div>


--


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