On Mon, Sep 12, 2016 at 9:17 AM, Patrick B <patrickbake...@gmail.com> wrote:
> Hi guys, > > select schemaname,relname,n_live_tup, n_dead_tup from pg_stat_all_tables > where relname = 'parts'; > > > schemaname relname n_live_tup n_dead_tup >> ---------- ------------- ---------- ---------- >> public parts 191623953 182477402 > > > See the large number of dead_tup? > > My autovacuum parameters are: > > "autovacuum_vacuum_threshold" : "300", >> "autovacuum_analyze_threshold" : "200", >> "autovacuum_vacuum_scale_factor" : "0.005", >> "autovacuum_analyze_scale_factor" : "0.002", > > > Table size: 68 GB > > Why does that happen? Autovacuum shouldn't take care of dead_tuples? > Could you notice if the table is regularly getting vacuumed at all ? when was the last_autovacuum and last_autoanalyze time ? > > Because of that the table is very slow... > When I do a select on that table it doesn't use an index, for example: > > > \d parts; > >> "index_parts_id" btree (company_id) >> "index_parts_id_and_country" btree (company_id, country) > > > > > > explain select * from parts WHERE company_id = 12; > > Seq Scan on parts (cost=0.00..6685241.40 rows=190478997 width=223) >> Filter: (company_id = 12) > > That should be due to not running VACUUM and ANALYZE. Did you VACUUM ANALYZE and see if the query is picking up the Index. This is possible if "company_id" has unique values. Regards, Venkata B N Fujitsu Australia
