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 93344533FA for ; Wed, 13 Apr 2005 13:05:10 -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 85376-05 for ; Wed, 13 Apr 2005 16:05:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E5C3D53232 for ; Wed, 13 Apr 2005 13:05:06 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3DG51jB007850; Wed, 13 Apr 2005 12:05:01 -0400 (EDT) To: Nichlas =?iso-8859-1?Q?L=F6fdahl?= Cc: pgsql-performance@postgresql.org Subject: Re: Foreign keys and partial indexes In-reply-to: <20050413154545.GA19181@shaka.acc.umu.se> References: <20050413154545.GA19181@shaka.acc.umu.se> Comments: In-reply-to Nichlas =?iso-8859-1?Q?L=F6fdahl?= message dated "Wed, 13 Apr 2005 17:45:46 +0200" Date: Wed, 13 Apr 2005 12:05:01 -0400 Message-ID: <7849.1113408301@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/252 X-Sequence-Number: 11649 Nichlas =?iso-8859-1?Q?L=F6fdahl?= 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