Foreign keys and partial indexes - Mailing list 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