CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4
Mathieu
Mathieu

Reputation: 4787

Should I use one-column index if the column is already used in a multiple-column index?

I have a query that I optimize by using a postgresql index

(called index1) add_index(:deal, [:partner_id, :partner_status, :deal_user_connect_datetime])

I have another query inside the Admin panel that queries all the partners inside the Deal model.

Should I create another index like so (add_index(:deal, [:partner_id]), with only 'partner_id' as 'filter' criteria for the index or should I assume that, since it is already one of the column used in index1, it would overlap/be redundant/not be useful as the database already implements a index on partner_id.

Upvotes: 0

Views: 77

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

No, you shouldn't create another index -- not because it is already one of the column used in index1, but because it is the leading column in the index, and is therefore just as capable of optimising predicates based on partner_id as an index only on partner_id.

In some circumstances it may be better, in fact. For example, if partner_id was nullable and you wanted to use a predicate such as "partner_id is null", then on many RDBMSs an index on partner_id would not be usable. However, if any one of the other columns was non-nullable then the optimiser would know that there is a value of partner_id in the index for every row in the table, and hence it could be used.

A multicolumn index is going to be larger than a single column index, but in most instances this is not going to be prejudicial to performance in comparison to having both indexes present, as you are then using even more disk space and requiring more memory to operate efficiently,

Upvotes: 1

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

As usual, the answer is "it depends." The multicolumn index can be used since the first value is partner_id. However, if the other columns have many different values it might be more efficient to create a second index only for that single column.

This requires benchmarking and/or more information on the data in the database.

Upvotes: 1

Related Questions