pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nichlas Löfdahl <crotalus@acc.umu.se>
To: pgsql-performance@postgresql.org
Subject: Foreign keys and partial indexes
Date: Wed, 13 Apr 2005 17:45:46 +0200
Message-ID: <20050413154545.GA19181@shaka.acc.umu.se> (raw)

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



view thread (2+ messages)  latest in thread

Message-ID: <20050413154545.GA19181@shaka.acc.umu.se>
Permalink:  ../20050413154545.GA19181@shaka.acc.umu.se/
Also on:    postgresql.org/message-id/20050413154545.GA19181@shaka.acc.umu.se

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: crotalus@acc.umu.se
  Subject: Re: Foreign keys and partial indexes
  In-Reply-To: <20050413154545.GA19181@shaka.acc.umu.se>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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