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

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dane Foster
Sent: Tuesday, January 26, 2016 2:39 PM
To: pgsql-general <pgsql-general@postgresql.org>
Subject: [GENERAL] Can PostgreSQL use multi-column index for FK constraint validation?

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?
Thanks,

Dane

Columns in proposed index on FK (col1, col2) are in the same order (and in the beginning) of PK index.
So, no need for additional index (col1, col2).

Regards,
Igor Neyman

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jan 26, 2016 at 3:15 PM, Josh Berkus <josh@agliodbs.com> wrote:

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

​
My example is modeling an order details table and the answer to the
question of selectivity is it depends. For some of our clients it is highly
selective because customers generally order a single item at a time. For
others it's multi-modal because it starts out w/ their customers ordering
only a single item but over time customer behavior changes and there is
this mix of single and multi item orders. Additionally my use case for
PostgreSQL is the VPS use case where each client has their own schema so
I'd prefer not to have to deal w/ per client index building and
maintenance. So is there a rule of thumb design wise for variable
selectivity as I've described?

Dane
​

Dane Foster <studdugie@gmail.com> writes:

My example is modeling an order details table and the answer to the
question of selectivity is it depends. For some of our clients it is highly
selective because customers generally order a single item at a time. For
others it's multi-modal because it starts out w/ their customers ordering
only a single item but over time customer behavior changes and there is
this mix of single and multi item orders. Additionally my use case for
PostgreSQL is the VPS use case where each client has their own schema so
I'd prefer not to have to deal w/ per client index building and
maintenance. So is there a rule of thumb design wise for variable
selectivity as I've described?

See
http://www.postgresql.org/docs/9.4/static/indexes.html
particularly sections 11.3 and 11.5.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/26/2016 12:47 PM, Dane Foster wrote:

My example is modeling an order details table and the answer to the
question of selectivity is it depends. For some of our clients it is
highly selective because customers generally order a single item at a
time. For others it's multi-modal because it starts out w/ their
customers ordering only a single item but over time customer behavior
changes and there is this mix of single and multi item orders.
Additionally my use case for PostgreSQL is the VPS use case where each
client has their own schema so I'd prefer not to have to deal w/ per
client index building and maintenance. So is there a rule of thumb
design wise for variable selectivity as I've described?

Well, my general perspective is that if the table has millions of rows
(or more), and there are 100's (or more) of col3 items for each
col1/col2 combo, then I'd *probably* add a specific FK index.

Given the "I don't know" you have above, I generally wouldn't add one,
and then look at response times on updates/deletes to the orders table
to see if there's a performance issue.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general