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)
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?
