On Thu, Sep 12, 2024 at 7:56 PM Wells Oliver <wells.oliver@xxxxxxxxx> 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@xxxxxxxxx> wrote:On Thu, Sep 12, 2024 at 6:52 PM Wells Oliver <wells.oliver@xxxxxxxxx> 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.oliver@xxxxxxxxx
Death to America, and butter sauce.
Iraq lobster!
