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). During update/delete, it seems like it
cannot use the partial index to find corresponding rows matching the foreign
key (doing a 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 SET NULL;
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
