I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful.
There are 3 tables:
transfers(id, amount, type, timestamp, file_id),
file_to_report(id, report_id, file_id).
files have many to one relation.
file_to_report have many to many relation.
1 report ≈ 20k files ≈ 200k transfers
I have to select all transfers of particular type for particular report_id, sorted by timestamp.
SELECT t.id, t.amount, t.timestamp FROM transfers t INNER JOIN file_to_report ftr ON t.file_id = ftr.file_id WHERE t.type = 'debit' AND ftr.report_id = 1 ORDER BY t.timestamp, t.id OFFSET 5000 ROWS FETCH NEXT 1000 ROWS ONLY;
Currently this query takes ~20s. With every next page it needs more and more time. Without
ORDER BY it takes 0.05s.
I've tried to create the next indexes to avoid sorting / make filtering by
CREATE INDEX i1 ON transfer (timestamp, id); CREATE INDEX i2 ON transfer (timestamp, id, file_id); CREATE INDEX i3 ON transfer (file_id, timestamp, id); CREATE INDEX i4 ON transfer (type, timestamp, id); CREATE INDEX i5 ON transfer (type); CREATE INDEX i6 ON transfer (type, file_id, timestamp, id); CREATE index i7 ON file_to_report (file_id); CREATE index i8 ON file_to_report (file_id, report_id);
However, none of these indexes makes the query faster. Please, help me to understand what is wrong.