I have the following query:
SELECT "account_transactions"."subsidiary_id" FROM "account_transactions" WHERE (transaction_type = 'loan_payment' AND amount > 0 AND status = 'approved') AND (DATE(transacted_at) >= '2020-08-01' AND DATE(transacted_at) <= '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 > 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?