How to optimise a query with inner join and order by

Question

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), files(id, path), file_to_report(id, report_id, file_id).

transfers and files have many to one relation. files and 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 file_to_report faster:

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.

Answer 1

almost

You'd want to adjust this index to include amount:

CREATE INDEX i6 ON transfer (type, file_id, timestamp, id) INCLUDE (amount);

And change this index to switch the column order:

CREATE index i8 ON file_to_report (report_id, file_id);

local test

Using a similar query against tables I can access:

SELECT
    u.Id,
    u.CreationDate,
    u.Reputation
FROM dbo.Users AS u
JOIN dbo.Posts AS p
  ON u.Id = p.OwnerUserId
WHERE u.DownVotes = 0
AND   p.PostTypeId = 1
ORDER BY
    u.CreationDate,
    u.Id
OFFSET 5000 ROW
FETCH NEXT 1000 ROW ONLY;

With the following indexes:

CREATE INDEX u
ON dbo.Users
(DownVotes, Id, CreationDate)
INCLUDE(Reputation);

CREATE INDEX p
ON dbo.Posts
(PostTypeId, OwnerUserId);

I get the following query plan, completing in 169ms: