Delete, foreign key, index usage

From: Johann Spies <johann(dot)spies(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Delete, foreign key, index usage
Date: 2017-04-04 12:07:06
Message-ID: CAGZ55DRKE8Jkj+aE+vxeQqC+uP46ujH5fAgL1vZLqafdeKZRiQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

While updating our database which includes a lot of deletions where a lot
of foreign key references are involved we found that in the case of two
tables the indexes are ignored and it slow down the process a lot.

Here are stats about those two tables:

relname seq_scan seq_tup_read idx_scan idx_tup_fetch n_tup_ins n_tup_upd
n_tup_del n_tup_hot_upd n_live_tup n_dead_tup n_mod_since_analyze
belongs_to 227 52539487559 0 0 771 0 1459 0 125 1459 2230
publication 229 11502854612 0 0 254 0 229 0 60 229 483
Publication ( has a foreign key (ut) and more than 50million records) that
references the top of the chain of references. This field (ut) is also the
primary key of publication.

In the case of belongs_to (about 231393000 records) which references the
same table (article) ut has an index.

All other tables in this dependency chain reports 100% or near 100% usage
of the indexes e.g.

citation_2010_2014 0 0 226 1882 2510 0 1910 0 816 1910 4420

The indexes are on a ssd and we have set the random_page_cost to 1 for
those queries.

The definition of belongs_to:

CREATE TABLE wos_2017_1.belongs_to
(
suborg_id uuid,
organisation_id uuid,
address_id uuid,
ut citext,
uuid uuid NOT NULL,
id integer NOT NULL DEFAULT
nextval('wos_2017_1.belongs2_id_seq'::regclass),
pref_name_id uuid,
addr_no smallint,
reprint_addr_no smallint,
CONSTRAINT belongs2_pkey PRIMARY KEY (uuid),
CONSTRAINT belongs_to_address_id_fkey FOREIGN KEY (address_id)
REFERENCES wos_2017_1.address (uuid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL,
CONSTRAINT belongs_to_pref_name_id_fkey FOREIGN KEY (pref_name_id)
REFERENCES wos_2017_1.org_pref_name (uuid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL,
CONSTRAINT belongs_to_suborg_id_fkey FOREIGN KEY (suborg_id)
REFERENCES wos_2017_1.suborg (uuid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL,
CONSTRAINT belongs_to_ut_fkey FOREIGN KEY (ut)
REFERENCES wos_2017_1.article (ut) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT belongs2_id_key UNIQUE (id),
CONSTRAINT
belongs2_ut_suborg_id_organisation_id_address_id_addr_no_re_key UNIQUE (ut,
suborg_id, organisation_id, address_id, addr_no, reprint_addr_no,
pref_name_id)
)
WITH (
OIDS=FALSE
);
with indexes on address_id, organisation_id, pref_name_id, ut

I have also tried to set enable_seqscan to false for these queries, but
still no usage of the indexes.

Why would that be?

Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Johann Spies 2017-04-05 10:40:21 Re: Delete, foreign key, index usage
Previous Message Riaan Stander 2017-03-28 22:51:06 Re: Best design for performance