Do I need to create index for the column when using it in update statement?

Question

I have a table in postgresql11 which has a userId as a column. And it is not primary key in the table. I need to perform below update statement on the table.

update entity set name='xxxx' where userId = 'yyy.

The userId is in the where condition but I don't know whether I should create an index for userId. Will it improve performance? If yes, how can I specify index in the update statement?

• Akina: I don't know whether I should create an index You may, but not must.

Answer 1

Indexes are meant to improve performance of predicates used in WHERE, JOIN, and HAVING clauses for any type of query. So yes having an index on userId theoretically can improve performance of your UPDATE example query.

After you create the index you don't need to specify it in your query, PostgreSQL will automatically use it if its engine thinks it'll help the query performance.