Re: Indexes on individual columns of composite primary key

Поиск
Список
Период
Сортировка
От Matthew Walden
Тема Re: Indexes on individual columns of composite primary key
Дата
Msg-id AANLkTi=xZSMq6vre5FBoS+TjOb7_td0ZETXDvzPSdkdX@mail.gmail.com
обсуждение исходный текст
Ответ на Indexes on individual columns of composite primary key  ("Dan Halbert" <halbert@halwitz.org>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Indexes on individual columns of composite primary key
Следующее
От: Greg Smith
Дата:
Сообщение: Re: Considering Solid State Drives