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!
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? |