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

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.

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?

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>