Re: Query plan getting less efficient over time with frequent updates and deletes..

From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Query plan getting less efficient over time with frequent updates and deletes..
Date: 2024-09-13 02:41:37
Message-ID: CANzqJaCAHbn4vvPq-uWA5PJW6Ti3iKfeePjuFR0ve0_f-ZLWhw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Thu, Sep 12, 2024 at 7:56 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> 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 <ronljohnsonjr(at)gmail(dot)com>
> wrote:
>
>> On Thu, Sep 12, 2024 at 6:52 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
>> 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
> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>

--
Death to America, and butter sauce.
Iraq lobster!

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Muhammad Waqas 2024-09-13 04:45:16 Re: is the oracle client supposed to be owned by postgres or root?
Previous Message lennam 2024-09-13 01:19:46 RE: is the oracle client supposed to be owned by postgres or root?