Re: Can PostgreSQL use multi-column index for FK constraint validation?

On 01/26/2016 11:38 AM, Dane Foster wrote:
> Hello,
>
> If I have a primary key index of the form:
> (col1, col2, col3)
> and a foreign key constraint of the form:
> FOREIGN KEY (col1, col2) REFERENCES foo
>  ON DELETE CASCADE ON UPDATE CASCADE
> should I create a separate index (col1, col2) or is PostgreSQL capable
> of using the primary key's index?

You are not required to create one.

foo(col1, col2) needs a unique index.  There need not be any specific
index on (col1, col2) in the referencing table.  Whether you want one
for performance depends on how selective (col1, col2) is without col3,
and how large the table is.

--
Josh Berkus
Red Hat OSAS
(opinions are my own)