Обсуждение: Delete, foreign key, index usage
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
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)
my lips will praise you. (Psalm 63:3)
On 4 April 2017 at 14:07, Johann Spies <johann.spies@gmail.com> wrote: > Why would that be? To answer my own question. After experimenting a lot we found that 9.6 uses a parallel seqscan that is actually a lot faster than using the index on these large tables. This, to us was a surprise! Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies <johann.spies@gmail.com> wrote:
On 4 April 2017 at 14:07, Johann Spies <johann.spies@gmail.com> wrote:
> Why would that be?
To answer my own question. After experimenting a lot we found that
9.6 uses a parallel seqscan that is actually a lot faster than using
the index on these large tables.
This, to us was a surprise!
If you have modern GPU's available, you could try the pg-strom extension - https://github.com/pg-strom/devel
It leverages GPU's to further parallelize scans.