Re: Primary key index partially used

From: Igor Neyman
Sent: Tuesday, January 26, 2016 11:01 AM
To: 'Florian Gossin' <fluancefg(at)gmail(dot)com>; pgsql-performance(at)postgresql(dot)org
Subject: RE: [PERFORM] Primary key index partially used

From: pgsql-performance-owner(at)postgresql(dot)org<mailto:pgsql-performance-owner(at)postgresql(dot)org> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Florian Gossin
Sent: Tuesday, January 26, 2016 10:52 AM
To: pgsql-performance(at)postgresql(dot)org<mailto:pgsql-performance(at)postgresql(dot)org>
Subject: [PERFORM] Primary key index partially used

Hi all,
I'm using PostgreSQL 9.4.5 and I have a weird issue.
I have the following three tables:

visit
( nb bigint NOT NULL,
CONSTRAINT visit_pkey PRIMARY KEY (nb)
)
with ~ 750'000 rows

invoice
( id bigint NOT NULL,
CONSTRAINT invoice_pkey PRIMARY KEY (id)
)
with ~ 3'000'000 rows

visit_invoice
( invoice_id bigint NOT NULL,
visit_nb bigint NOT NULL,
CONSTRAINT visit_invoice_pkey PRIMARY KEY (visit_nb, invoice_id),
CONSTRAINT fk_vis_inv_inv FOREIGN KEY (invoice_id)
REFERENCES invoice (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT fk_vis_inv_vis FOREIGN KEY (visit_nb)
REFERENCES visit (nb) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
with ~ 3'000'000 rows

When I delete a row in visit table, it runs the trigger for constraint fk_vis_inv_vis and it seems to use the primary key index on visit_invoice:
explain analyze DELETE FROM visit WHERE nb = 2000013;
------------------------------------------------------------------------------------------------------------------------
Delete on visit (cost=0.42..8.44 rows=1 width=6) (actual time=2.225..2.225 rows=0 loops=1)
-> Index Scan using visit_pkey on visit (cost=0.42..8.44 rows=1 width=6) (actual time=2.084..2.088 rows=1 loops=1)
Index Cond: (nb = 2000013)
Planning time: 0.201 ms
Trigger for constraint fk_vis_inv_vis: time=0.673 calls=1

But when I delete a record in the table invoice, it runs the trigger for constraint fk_vis_inv_vis and it doesn't seem to use the primary key index on visit_invoice:

explain analyze DELETE FROM invoice WHERE id = 30140470;
----------------------------------------------------------------------------------------------------------------------------
Delete on invoice (cost=0.43..8.45 rows=1 width=6) (actual time=0.109..0.109 rows=0 loops=1)
-> Index Scan using invoice_pkey on invoice (cost=0.43..8.45 rows=1 width=6) (actual time=0.060..0.060 rows=1 loops=1)
Index Cond: (id = 30140470)
Planning time: 0.156 ms
Trigger for constraint fk_vis_inv_inv: time=219.122 calls=1
So, if I create explicitly an index for the second column (which is already part of the primary key), it seems to use it because the trigger execution is really faster:

CREATE INDEX fki_vis_inv_inv
ON visit_invoice
USING btree
(invoice_id);

explain analyze DELETE FROM invoice WHERE id = 30140470;
----------------------------------------------------------------------------------------------------------------------------
Delete on invoice (cost=0.43..8.45 rows=1 width=6) (actual time=0.057..0.057 rows=0 loops=1)
-> Index Scan using invoice_pkey on invoice (cost=0.43..8.45 rows=1 width=6) (actual time=0.039..0.040 rows=1 loops=1)
Index Cond: (id = 120043571)
Planning time: 0.074 ms
Trigger for constraint fk_vis_inv_inv: time=0.349 calls=1
So I have tried to create the primary key differently, like PRIMARY KEY (invoice_id, visit_nb), and in that case it is the opposite, the deletion of the invoice is very fast and the deletion of the visit is really slower, unless I create a specific index as above.
So my question is: why is my index on the primary key not used by both triggers and why should I always create an explicit index on the second column ?
Thanks.
Florian

First, It’s a god (for performance) practice to create indexes on FK columns in “child” table.
Second, PG is using index only if the first column in concatenated index is used in WHERE clause. That is exactly what you observe.

Regards,
Igor Neyman

“god” -> good ☺