Primary key index partially used

Поиск
Список
Период
Сортировка
От Florian Gossin
Тема Primary key index partially used
Дата
Msg-id CAPW7RP=7zRVF-ePvwN5D5jhf6r+FcXhAXT-7eERxC-b4WBLqgA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Primary key index partially used  (Igor Neyman <ineyman@perceptron.com>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Jim Nasby
Дата:
Сообщение: Re: insert performance
Следующее
От: Igor Neyman
Дата:
Сообщение: Re: Primary key index partially used