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
