large number dead tup - Postgres 9.5

From: Patrick B <patrickbakerbr(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: large number dead tup - Postgres 9.5
Date: 2016-09-11 23:17:11
Message-ID: CAJNY3iu2rWB8Vfe-rRME2pnfnbNrMh34r4POjWX0f3J1JrEhiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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)

Thanks
Patrick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Venkata B Nagothi 2016-09-12 01:33:40 Re: large number dead tup - Postgres 9.5
Previous Message Christian Convey 2016-09-11 16:34:47 Re: [GENERAL] C++ port of Postgres