From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@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
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@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 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. << 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?
