public inbox for [email protected]
help / color / mirror / Atom feedFrom: Wells Oliver <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: Query plan getting less efficient over time with frequent updates and deletes..
Date: Thu, 12 Sep 2024 16:56:21 -0700
Message-ID: <CAOC+FBWzTatoqRna_tyiEkqcXnu1AvMUx=1hm0qR7=xi+uPJ7w@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaDi90pp=cq5kuTnewstkV5E30ToYu2eW-_yTrN0UJkJuw@mail.gmail.com>
References: <CAOC+FBXR59QvEvpnOj0d0Om352i=2gP_Mm+b=aeQ4Zo3w-gNdA@mail.gmail.com>
<CANzqJaDi90pp=cq5kuTnewstkV5E30ToYu2eW-_yTrN0UJkJuw@mail.gmail.com>
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 <[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]>
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], [email protected]
Subject: Re: Query plan getting less efficient over time with frequent updates and deletes..
In-Reply-To: <CAOC+FBWzTatoqRna_tyiEkqcXnu1AvMUx=1hm0qR7=xi+uPJ7w@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