Why is PostgreSQL Not Using My Index? I have the following query:
SELECT &quot;account_transactions&quot;.&quot;subsidiary_id&quot;
FROM &quot;account_transactions&quot;
WHERE (transaction_type = 'loan_payment'
        AND amount &gt; 0
        AND status = 'approved')
        AND (DATE(transacted_at) &gt;= '2020-08-01'
        AND DATE(transacted_at) &lt;= '2020-08-31'
        AND subsidiary_type = 'Loan')
ORDER BY  transacted_at ASC

I identified that it was considered a slow query so I'm trying to optimize it by applying an index. However I tried variations of indices such as putting an index on (transaction_type, amount, status) WHERE amount &gt; 0 AND status='approved' AND transaction_type = 'loan_payment' or an index on transaction_type only. But when I run EXPLAIN ANALYZE, it still shows that it's running Seq Scan meaning that indices aren't being used.
Is this a PostgreSQL thing or am I doing indexing wrong?
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.