We have a Query which do left join with multiple tables using two columns and Index exist on it rather than doing Index scan, execution plan is choosing Table Scan on the table and to improve query performance tried running vacuum analyze on table helped DB to choose best execution plan which helped for few days. We scheduled manual vacuum analyze to run everyday (autovacuum settings didn't help and we planned to update autovacuum settings later as we didn't analyze/capturing pg_stat_user_tables data). Seems manual vacuum analyze didn't helped and users reported slowness then we started executing vacuum analyze and verify the execution plan(If execution plan choose Index scan on table). There are times even vacuum analyze didn't helped to choose the best execution plan(Index scan on table) and found running analyze after vacuum analyze helped to choose right execution plan(Index scan).
On other note,
We observed disabling hashjoin (set enable_hasjoin =off) run query faster and do Index Scan on table.
Tried updating statistics target on table columns(two columns) joining with other tables (no luck). Please suggest
Problematic Table Stats: Table count ~ 800 million
1 Day Stats from pg_stat_user_table
- 1 million Dead tuples
- 14k Updates
- 1 million Deletes
- n_since_mod = 2.1 million
- n_ins_since vacuum - 1 million
AutovacuumConfiguration(Table):
autovacuum_vacuum_threshold=250000
autovacuum_vacuum_insert_threshold=300000
autovacuum_analyze_threshold=200000
