public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-admin <[email protected]>
Subject: Re: Query plan getting less efficient over time with frequent updates and deletes..
Date: Thu, 12 Sep 2024 22:41:37 -0400
Message-ID: <CANzqJaCAHbn4vvPq-uWA5PJW6Ti3iKfeePjuFR0ve0_f-ZLWhw@mail.gmail.com> (raw)
In-Reply-To: <CAOC+FBWzTatoqRna_tyiEkqcXnu1AvMUx=1hm0qR7=xi+uPJ7w@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>
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!
view thread (5+ messages) latest in thread
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]
Subject: Re: Query plan getting less efficient over time with frequent updates and deletes..
In-Reply-To: <CANzqJaCAHbn4vvPq-uWA5PJW6Ti3iKfeePjuFR0ve0_f-ZLWhw@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