Thread: Indexes on individual columns of composite primary key

Indexes on individual columns of composite primary key

From

"Dan Halbert"

Date:

15 November 2010, 19:07:13

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.

Thanks,

Dan

Re: Indexes on individual columns of composite primary key

From

Tom Lane

Date:

15 November 2010, 19:16:32

"Dan Halbert" <halbert@halwitz.org> writes:
> 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?

Please see
http://www.postgresql.org/docs/9.0/static/indexes.html
particularly sections 11.3 and 11.5.

            regards, tom lane

Re: Indexes on individual columns of composite primary key

From

Matthew Walden

Date:

15 November 2010, 19:19:00

Dan,

It depends on your application. There is no point in creating an index with the same 3 columns in the primary key (in the same order).

If you have an index on COL1, COL2 and COL3 (in that order) then if you have a query such as SELECT COL1, COL2, COL3 from T1 then the index will be considered. Same if you have a query with the same columns but different order ie SELECT COL2, COL1, COL3 from T1 and if you just select the first column ie SELECT COL1 from T1. The index won't be considered if you have a query such as SELECT COL2 FROM T1 so if your application does this you may wish to consider such indexes.

Also bear in mind the order of which you create the index or primary key. They should be ordered by uniqueness starting with the most unique.

On Mon, Nov 15, 2010 at 8:01 PM, Dan Halbert <halbert@halwitz.org> wrote:

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.

Thanks,
Dan

Re: Indexes on individual columns of composite primary key

From

Dann Corbit

Date:

15 November 2010, 19:20:58

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dan Halbert
Sent: Monday, November 15, 2010 12:01 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Indexes on individual columns of 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.

Re: Indexes on individual columns of composite primary key

From

Dann Corbit

Date:

15 November 2010, 19:25:06

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dann Corbit
Sent: Monday, November 15, 2010 12:21 PM
To: 'Dan Halbert'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Indexes on individual columns of 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.

Apparently (after reading the documentation link provided by Tom) there can be value to indexes when accessed with leading columns missing.

Aside:

I think there is a possible bug in the documentation. This link:

http://www.postgresql.org/docs/9.0/static/indexes-unique.html

Says that only btree indexes can be unique. But Hash indexes seem an obvious exception to me, or am I overlooking something?

Re: Indexes on individual columns of composite primary key

From

Matthew Walden

Date:

15 November 2010, 19:36:59

"Apparently (after reading the documentation link provided by Tom) there can be value to indexes when accessed with leading columns missing".

That is a new one on me too - interesting.

I suppose it comes down to testing at the end of the day - if you "set enable_seqscan to false" and "EXPLAIN ANALYSE" your query then you will see whether the indexes you create are used. Whether they are useful will require you to set enable_seqscan back to true and see whether the optimizer chooses to use them (but that will change as your data does).