Hello,

I'm trying to optimize a request and cannot explain what I observe.

I have a relatively simple request using 3 tables returning around 1.8M
rows. On each table I have an index on: the primary key (ascending,
unique), the foreign keys (ascending) and the primary key (descending).
I'm using Firebird 2.5.3 (embedded and super server).

When I use ORDER BY ... DESC here is the plan I get:

PLAN JOIN (Table1 ORDER Index_on_primary_key_desc, Table2 INDEX
(RDB$PRIMARY888), Table3 INDEX (RDB$PRIMARY886))

8012 fetches, 0 marks, 43 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 2003 index, 0 seq.
Delta memory: 43832 bytes.
Total execution time: 0.076s

Very fast, excellent. When I switch to ORDER BY ... ASC I have:

PLAN JOIN (Table1 ORDER RDB$PRIMARY896, Table2 INDEX (RDB$PRIMARY888),
Table3 INDEX (RDB$PRIMARY886))

16164861 fetches, 4 marks, 58282 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 4033950 index, 5802 seq.
Delta memory: 1427232 bytes.
Total execution time: 7.272s

Execution time is multiplied by 100 even when indices are used.

I tried to recompute statistics for all indices but results are weird:
ORDER BY ASC went from 7.2s to 3.5s (faster but still slow) and ORDER BY
DESC went from 0.076s to 3.5s (slowdown by a factor 50).

I don't really understand what might cause this huge performance
difference even when using indices, if I would be better off using another
type of index, why the slowdown after statistics are updated or if I'm
missing something basic.

Here is the full request if needed:

SELECT
Table1.ID, Table1.FKID, Table1.MD... some other fields
FROM Table1, Table2 , Table3

WHERE 1 IS NOT NULL
AND (Table2.ID = Table1.FKID)
AND (Table2.FKID = Table3.ID)
AND (Table3.ComputationID = 9)

ORDER BY Table1.ID ASC;

I'd welcome any insight as to what I might be doing wrong.

--
Boris HERBINIÈRE-SÈVE | Software developer