Postgresql – Why is PostgreSQL Not Using My Index

index-tuningpostgresql

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?

Best Answer

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.

Index md5 hash as `uuid`

Since it turns out that your value column is too big to be indexed and you decided to use md5 hashes instead (which is a good solution):

Character varying index overhead & length limit

Consider storing the md5 hash as uuid, which would be most efficient (smaller, faster storage). You can just cast the result to uuid:

md5(value)::uuid

Details in the last chapter of this answer:

Convert hex in text representation to decimal number

Your index would look like this:

CREATE INDEX eav_value_special_idx
ON eav_value_text_data (cast(md5(value) AS uuid), attribute);

Note the explicit cast() syntax in the index definition:

How to create an index on an integer json property in postgres

Your query can still use shorthand syntax:

SELECT count(*)
FROM   eav_value_text_data v 
JOIN   eav_attribute a ON a.id = v.attribute 
WHERE  a.data_type = 11 
AND    a.name = 'id' 
AND    md5(v.value)::uuid = md5('rs145368920')::uuid;

Postgresql – Postgres query plan has high rowcount, slow execution

(Unsure whether this belongs as an answer, comment or edit so I'll leave it here unless I hear otherwise.)

I was unable to find the cause of the bad row count. However, after rewriting the query like this,

SELECT t1."VisitorID", t1."Timestamp"
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT t2.VisitorID, t2.Timestamp
    FROM Table2 t2
    WHERE t2.SessionID = t1.SessionID
        AND t2.Timestamp > t1.Timestamp
)

the estimated row count went to a normal level and execution time decreased drastically, even on large datasets. I tested this with and without composite indexes like so:

CREATE INDEX "IX_Temp_Table1_Visitor-Session-Time"
ON "Table1"
USING BTREE ("VisitorID","SessionID","Timestamp");

CREATE INDEX "IX_Table2_Visitor-Session-Time"
ON "Table2"
USING BTREE ("VisitorID","SessionID","Timestamp");

and found no significant performance difference.

Best Answer

Related Solutions

PostgreSQL – Poor Query Performance Due to Recheck Condition on Index Scan

Index md5 hash as uuid

Postgresql – Postgres query plan has high rowcount, slow execution

Related Question

Index md5 hash as `uuid`