Re: Foreign keys and partial indexes

От: Tom Lane
Тема: Re: Foreign keys and partial indexes
Дата: ,
Msg-id: 7849.1113408301@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Foreign keys and partial indexes  (Nichlas Löfdahl)
Список: pgsql-performance

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
foreignkey (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


В списке pgsql-performance по дате сообщения:

От: Tom Mack
Дата:
Сообщение: Problem with slow query (caused by improper nestloop?)
От: Mischa Sandberg
Дата:
Сообщение: Strange serialization problem