1

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??

Improve this question
5
  • Does this answer your question? When should I use a composite index? Commented Aug 20, 2021 at 11:28
  • Does the table have a Primary Key? Commented Aug 20, 2021 at 11:47
  • Mandatory read regarding indexes in relational databases: use-the-index-luke.com Commented Aug 20, 2021 at 13:32
  • yes wildplasser, it has primery key Commented Aug 31, 2021 at 4:11
  • thanks Benjamin and All, it helped to clear my doubts about usage of composite index Commented Aug 31, 2021 at 4:16

1 Answer 1

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.