Re: Primary key index partially used - Mailing list pgsql-performance

I have the following three tables:

visit
( nb bigint NOT NULL,
  CONSTRAINT visit_pkey PRIMARY KEY (nb)
)

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.

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,

“god” -> good J