X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 267065349B for ; Wed, 13 Apr 2005 12:45:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79112-02 for ; Wed, 13 Apr 2005 15:45:52 +0000 (GMT) Received: from khan.acc.umu.se (khan.acc.umu.se [130.239.18.139]) by svr1.postgresql.org (Postfix) with ESMTP id 2FDDE5349A for ; Wed, 13 Apr 2005 12:45:51 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by amavisd-new (Postfix) with ESMTP id 295D6D221 for ; Wed, 13 Apr 2005 17:45:49 +0200 (MEST) Received: from shaka.acc.umu.se (shaka.acc.umu.se [130.239.18.148]) by khan.acc.umu.se (Postfix) with ESMTP id A9D98D204 for ; Wed, 13 Apr 2005 17:45:46 +0200 (MEST) Received: by shaka.acc.umu.se (Postfix, from userid 23132) id 5DA0F17213; Wed, 13 Apr 2005 17:45:46 +0200 (MEST) Date: Wed, 13 Apr 2005 17:45:46 +0200 From: Nichlas =?iso-8859-1?Q?L=F6fdahl?= To: pgsql-performance@postgresql.org Subject: Foreign keys and partial indexes Message-ID: <20050413154545.GA19181@shaka.acc.umu.se> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at acc.umu.se X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/251 X-Sequence-Number: 11648 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