Re: large number dead tup - Postgres 9.5

On Mon, Sep 12, 2016 at 9:17 AM, Patrick B <patrickbakerbr(at)gmail(dot)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