pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Foreign keys and partial indexes
2+ messages / 2 participants
[nested] [flat]

* Foreign keys and partial indexes
@ 2005-04-13 15:45  Nichlas Löfdahl <crotalus@acc.umu.se>
  0 siblings, 1 reply; 2+ messages in thread

From: Nichlas Löfdahl @ 2005-04-13 15:45 UTC (permalink / raw)
  To: 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). 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Foreign keys and partial indexes
@ 2005-04-13 16:05  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nichlas Löfdahl <crotalus@acc.umu.se>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-04-13 16:05 UTC (permalink / raw)
  To: Nichlas Löfdahl <crotalus@acc.umu.se>; +Cc: pgsql-performance

Nichlas =?iso-8859-1?Q?L=F6fdahl?= <crotalus@acc.umu.se> writes:
> 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? 

It doesn't know it's appropriate.  There's nothing constraining the FK
to be positive, after all.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-04-13 16:05 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-13 15:45 Foreign keys and partial indexes Nichlas Löfdahl <crotalus@acc.umu.se>
2005-04-13 16:05 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox