Query plan getting less efficient over time with frequent updates and deletes.. - Mailing list pgsql-admin

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.

My questions/concerns:

- this gets vacuumed and analyzed due to auto thresholds every night, so it's surprising that the planner's choices would change and choose a worse plan right?

- I look at pg_stat_user_tables to asses n_dead_tup along with n_tup_del and n_tup_upd to try and assess if there is table bloat and this seems to be under control

- I look at pgstattuple to check free_space and try and guess how much is being given back to the server for new rows, it's roughly 20-28% and it seems OK?

- what other factors are at play here? index bloat? disk IO? etc? something I can eyeball better in pg_stat_user_tables, or pgstattuple?

How can I make this table behave properly, with all of its updates, and without restoring to frequent pg_repack?

Here are the EXPLAIN ANALYZE VERBOSE results of a routine SELECT query with a few columns in a WHERE clause. I've abbreviated these otherwise it would just be too much text, glad to share more. After a fresh pg_repack, the former is performed for a while (like two weeks) then it reverts to the latter and everyone is unhappy.

Fast, better!