Your index is probably not used because it does not match the most selective conditions. If too many rows are returned from the index scan, a sequential scan is more efficient.
Try this index:
CREATE INDEX ON account_transactions (transaction_type, subsidiary_type, status, transacted_at);
If the condition of any of the leading three columns is not selective, omitting the column from the index is better. The important part is that transacted_at
is last, because it is not used in a condition with =
and is used in ORDER BY
.
You can add WHERE
conditions to the index like you did in your question.