Delete, foreign key, index usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux