Skip to main content
2 of 2
deleted 111 characters in body
Laurenz Albe
  • 56.4k
  • 4
  • 50
  • 82

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.

Laurenz Albe
  • 56.4k
  • 4
  • 50
  • 82