POSTGRES - Should I perfom Indexing on multiple columns or single column-CodePudding

I have a relation where I'll be querying the table with two columns for eg: findByXIdAndStatus.

select * from X where XId = '12345' and status = 'INACTIVE'

Status is a column which holds two values, ACTIVE or INACTIVE. Is it sufficient to create an index on XId column or do I need to create a composite index on both XId and Status or should I create multiple indexes on both columns. Currently I am using postgres DB.

CodePudding user response：

The most performant index might cover the entire WHERE clause, and so would be on (XId, status). However, if the XId column itself already be very restrictive, then Postgres might use a single column index on just XId. In that case, Postgres would have to seek back to clustered index to find the status value, in order to complete filtering. In any case, you may try both types of indices, check the EXPLAIN plans, and then decide which option to use.

CodePudding user response：

The single-column index on xid is probably good enough.

If only a small percentage of the rows are inactive, a partial index would be beneficial:

CREATE INDEX ON x (xid) WHERE status = 'INACTIVE';