From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nichlas Löfdahl <crotalus@acc.umu.se>
Cc: pgsql-performance@postgresql.org
Subject: Re: Foreign keys and partial indexes
Date: Wed, 13 Apr 2005 12:05:01 -0400
Message-ID: <7849.1113408301@sss.pgh.pa.us> (raw)
In-Reply-To: <20050413154545.GA19181@shaka.acc.umu.se>
References: <20050413154545.GA19181@shaka.acc.umu.se>
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
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: tgl@sss.pgh.pa.us, crotalus@acc.umu.se
Subject: Re: Foreign keys and partial indexes
In-Reply-To: <7849.1113408301@sss.pgh.pa.us>
* 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