Foreign keys and partial indexes
От | Nichlas Löfdahl |
---|---|
Тема | Foreign keys and partial indexes |
Дата | |
Msg-id | 20050413154545.GA19181@shaka.acc.umu.se обсуждение исходный текст |
Ответы |
Re: Foreign keys and partial indexes
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-performance |
Hello! I have a partial index (btree(col) WHERE col > 0) on table2 ('col' contains alot of NULL-values). There's also a foreign key on the column pointing to the primary key of table1 (ON UPDATE CASCADE ON DELETE SET NULL). Duringupdate/delete, it seems like it cannot use the partial index to find corresponding rows matching the foreign key (doinga full seqscan instead)? Is there any special reason for not letting the planner use the partial index when appropriate? \d table1 Table "public.table1" Column | Type | Modifiers --------+---------+----------- id | integer | not null text | text | Indexes: "table1_pkey" primary key, btree (id) \d table2 Table "public.table2" Column | Type | Modifiers --------+---------+----------- id | integer | not null col | integer | value | integer | Indexes: "table2_pkey" primary key, btree (id) CREATE INDEX col_part_key ON table2 USING btree(col) WHERE col > 0; ANALYZE table2; EXPLAIN ANALYZE DELETE FROM table2 WHERE col=1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Index Scan using col_part_key on table2 (cost=0.00..6.01 rows=6 width=6) (actual time=0.592..1.324 rows=8 loops=1) Index Cond: (col = 1) Total runtime: 4.904 ms Delete manually WITHOUT foreign key: test=> begin work; BEGIN Time: 0.808 ms test=> explain analyze delete from table1 where id=1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Index Scan using table1_pkey on table1 (cost=0.00..3.01 rows=2 width=6) (actual time=0.312..0.324 rows=1 loops=1) Index Cond: (id = 1) Total runtime: 0.623 ms (3 rows) Time: 3.912 ms test=> explain analyze delete from table2 where col=1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Index Scan using col_part_key on table2 (cost=0.00..14.70 rows=36 width=6) (actual time=0.338..0.557 rows=8 loops=1) Index Cond: (col = 1) Total runtime: 0.881 ms (3 rows) Time: 3.802 ms test=> rollback; ROLLBACK Delete WITH foreign key: test=> ALTER TABLE table2 ADD CONSTRAINT col_fkey FOREIGN KEY (col) REFERENCES table1(id) ON UPDATE CASCADE ON DELETE SETNULL; ALTER TABLE Time: 3783.009 ms test=> begin work; BEGIN Time: 1.509 ms test=> explain analyze delete from table1 where id=1; rollback; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Index Scan using table1_pkey on table1 (cost=0.00..3.01 rows=2 width=6) (actual time=0.769..0.781 rows=1 loops=1) Index Cond: (id = 1) Total runtime: 1.027 ms (3 rows) Time: 3458.585 ms test=> rollback; ROLLBACK Time: 1.506 ms /Nichlas
В списке pgsql-performance по дате отправления: