MIME-Version: 1.0
References: 
 <CAOC+FBXR59QvEvpnOj0d0Om352i=2gP_Mm+b=aeQ4Zo3w-gNdA@mail.gmail.com>
 <CANzqJaDi90pp=cq5kuTnewstkV5E30ToYu2eW-_yTrN0UJkJuw@mail.gmail.com>
In-Reply-To: 
 <CANzqJaDi90pp=cq5kuTnewstkV5E30ToYu2eW-_yTrN0UJkJuw@mail.gmail.com>
From: Wells Oliver <wells.oliver@gmail.com>
Date: Thu, 12 Sep 2024 16:56:21 -0700
Message-ID: 
 <CAOC+FBWzTatoqRna_tyiEkqcXnu1AvMUx=1hm0qR7=xi+uPJ7w@mail.gmail.com>
Subject: Re: Query plan getting less efficient over time with frequent updates
 and deletes..
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a6a2d10621f4dc49"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOC%2BFBWzTatoqRna_tyiEkqcXnu1AvMUx%3D1hm0qR7%3Dxi%2BuPJ7w%40mail.gmail.com>
Precedence: bulk

--000000000000a6a2d10621f4dc49
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Yes, I regularly look at pg_stat_user_tables and in particular
last_autovacuum and last_autoanalyze and these are always the current date
(or within two days) after our nightly processes soon finish.

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?


On Thu, Sep 12, 2024 at 4:47=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.co=
m> 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 delete=
s
>> on the order of 100-600k. The overall row length is approximately 80m. T=
his
>> 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. Th=
e
>> 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.
>
>

--=20
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>

--000000000000a6a2d10621f4dc49
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-size:small">Yes=
, I regularly look at pg_stat_user_tables and in particular last_autovacuum=
 and last_autoanalyze and these are always=C2=A0the current date (or within=
 two days) after our nightly processes soon finish.</div><div class=3D"gmai=
l_default" style=3D"font-size:small"><br></div><div class=3D"gmail_default"=
 style=3D"font-size:small">I wondered if the similar low planning time but =
the dissimilar longer execution time might indicate rows are spread out ove=
r disk, thereby negating a bitmap heap scan and the slower query taking lon=
ger due to having to read a lot more disk? Is that a possibility?</div><div=
 class=3D"gmail_default" style=3D"font-size:small"><br></div><div class=3D"=
gmail_default" style=3D"font-size:small"><br></div></div><br><div class=3D"=
gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Sep 12, 2024 at =
4:47=E2=80=AFPM Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com">=
ronljohnsonjr@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Thu, Sep 12, 20=
24 at 6:52=E2=80=AFPM Wells Oliver &lt;<a href=3D"mailto:wells.oliver@gmail=
.com" target=3D"_blank">wells.oliver@gmail.com</a>&gt; wrote:<br></div><div=
 class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr"><div style=3D"font-size:small">Hi all: we have 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 indexes and 303 =
columns and is quite frequently queried by humans and applications.</div><d=
iv style=3D"font-size:small"><br></div><div style=3D"font-size:small">I&#39=
;ve been in the habit of using pg_repack maybe once a month on this table b=
ecause I can&#39;t quite figure out why querying gets bogged down. The vacu=
um and analyze thresholds are set such that the table is both auto vacuumed=
 and analyzed every night.</div></div></blockquote><div>=C2=A0</div><div>1.=
 You&#39;re absolutely positive that the VACUUM and ANALYZE complete every =
night?</div><div>2. Nightly may not be often enough.</div></div><br></div>
</blockquote></div><br clear=3D"all"><div><br></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr"><div>Wells Oliver<br><a href=3D"mailto:wellsoliver@gmail.com=
" target=3D"_blank">wells.oliver@gmail.com</a></div></div></div>

--000000000000a6a2d10621f4dc49--