public inbox for [email protected]
help / color / mirror / Atom feedFrom: Wells Oliver <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: Query plan getting less efficient over time with frequent updates and deletes..
Date: Fri, 13 Sep 2024 09:55:48 -0700
Message-ID: <CAOC+FBWV8Xqk_auh9Vd0G9a9K2X0Gv2GK0eaUPsF_6cXj7VTjA@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaCAHbn4vvPq-uWA5PJW6Ti3iKfeePjuFR0ve0_f-ZLWhw@mail.gmail.com>
References: <CAOC+FBXR59QvEvpnOj0d0Om352i=2gP_Mm+b=aeQ4Zo3w-gNdA@mail.gmail.com>
<CANzqJaDi90pp=cq5kuTnewstkV5E30ToYu2eW-_yTrN0UJkJuw@mail.gmail.com>
<CAOC+FBWzTatoqRna_tyiEkqcXnu1AvMUx=1hm0qR7=xi+uPJ7w@mail.gmail.com>
<CANzqJaCAHbn4vvPq-uWA5PJW6Ti3iKfeePjuFR0ve0_f-ZLWhw@mail.gmail.com>
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 PM Ron Johnson <[email protected]> wrote:
> On Thu, Sep 12, 2024 at 7:56 PM Wells Oliver <[email protected]>
> wrote:
>
>> 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.
>>
>
> "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 PM Ron Johnson <[email protected]>
>> wrote:
>>
>>> On Thu, Sep 12, 2024 at 6:52 PM Wells Oliver <[email protected]>
>>> 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 approximately
>>>> 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 every
>>> night?
>>> 2. Nightly may not be often enough.
>>>
>>>
>>
>> --
>> Wells Oliver
>> [email protected] <[email protected]>
>>
>
>
> --
> Death to America, and butter sauce.
> Iraq lobster!
>
--
Wells Oliver
[email protected] <[email protected]>
view thread (5+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Query plan getting less efficient over time with frequent updates and deletes..
In-Reply-To: <CAOC+FBWV8Xqk_auh9Vd0G9a9K2X0Gv2GK0eaUPsF_6cXj7VTjA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox