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

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 <ronljohnsonjr(at)gmail(dot)com> wrote:

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

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>