Re: Indexes on individual columns of composite primary key

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Dan Halbert
Sent: Monday, November 15, 2010 12:01 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Indexes on individual columns of composite primary key

I have a table with four columns. Three of those columns are defined as the composite primary key. Does it make sense to create indexes on any or all of those three columns individually for performance reasons? PG does let me create the indexes. But perhaps it's redundant, since there's an implicitly-created index for the composite primary key.

>>

It depends upon your access patterns.

Consider a key on:

CREATE UNIQUE INDEX fourcols ON mytable(col1, col2, col3, col4);

If you always query in this way:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?, col4 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?

Then there is no value in creating an index on the other columns. If (on the other hand) you often query like this:

SELECT * FROM mytable WHERE col2 = ?

Or possibly:

SELECT * FROM mytable WHERE col4 = ?

Then it makes sense to create an index on col2 and an index on col4. The composite index will remain useful as long as the most significant columns are provided.

<<