Query indices: Single column index vs Composite index: Which one is good practice and also give faster performance

Question

Suppose I have table t1(c1, c2, c3, c4, c5,..)

I need to run queries where the columns (e.g. c1, c2, c3 here) used in where condition of select query as in below. The queries are making application slow.

select * from t1 where c1=somevalue1 and c2=somevalue2
select * from t1 where c3=somevalue1
select * from t1 where c1=somevalue1 and c2=somevalue2 and c3=somevalue

To improve performance of the queries, I want to create index on the columns which are used on where clause. what should be a better approach to create index on columns(c1, c2, c3)? multiple indexes like index1(c1), index2(c2), index3(c3), index4(c1, c2, c3) or Just one index like index1(c1, c2, c3)

Can anybody please advise on this??

Does this answer your question? [When should I use a composite index?](https://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index) — Benjamin M, Aug 20 '21 at 11:28
Mandatory read regarding indexes in relational databases: https://use-the-index-luke.com/ — , Aug 20 '21 at 13:32
thanks Benjamin and All, it helped to clear my doubts about usage of composite index — Phoebe, Aug 31 '21 at 04:16

score 0 · Answer 1 · answered Aug 20 '21 at 12:25

If the WHERE condition contains an arbitrary combinations of conditions on c1, c2 and c3, you are probably best off creating three single-column indexes on these three columns.

PostgreSQL can combine two index scans using a BitmapAnd if no single condition is selective enough, but the combination is.

Query indices: Single column index vs Composite index: Which one is good practice and also give faster performance

1 Answers1