2042142 - SmartShortcuts: fetchLast10VisitsByGuid uses an inefficient correlated subquery

Status: NEW

(Firefox :: New Tab Page, task, P3)

Description

[Marco Bonardo [:mak]]

In browser/extensions/newtab/lib/SmartShortcutsRanker/RankShortcuts.mjs, the fetchLast10VisitsByGuid function uses a correlated subquery to find the 10 most recent visits per place:

JOIN moz_historyvisits v ON v.place_id = h.id
WHERE v.id IN (
SELECT vv.id FROM moz_historyvisits vv
WHERE vv.place_id = h.id
ORDER BY vv.visit_date DESC
LIMIT 10
)

This causes SQLite to re-evaluate the subquery for every row produced by the
outer join — up to once per visit per GUID. Since a page may have hundreds of visits, querying 20 GUIDs can result in thousands subquery evaluations, making performance highly sensitive to visit count.

Comment 1

[Marco Bonardo [:mak]]

Attachment: WIP: Bug 2042142 - Replace correlated subquery in fetchLast10VisitsByGuid with ROW_NUMBER() window function.

The previous query re-evaluated a correlated subquery once per visit row in
the outer join, scaling O(N * V) with the number of GUIDs and visits per page.
The window function scans all visits in a single pass, reducing cost to O(N + V).