commit 294d79d1999aa3b3f6002d859a2f831cc40258de
parent 357d6c65512c28b214878ee03fe27e7d009c8c60
Author: Marco Bonardo <mbonardo@mozilla.com>
Date: Tue, 23 Dec 2025 09:02:51 +0000
Bug 2007091 - Workaround a SQLite query optimization bug. r=daisuke
SQLite is optimizing this expiration query wrongly, when using a
bloom filter and an automatic covering index on moz-icons.root.
In addition we're removing the ORDER BY clause as it's expensive. While
it makes sense to first expire older entries, in this case all the
entries are old, so we can save some power by not ordering them.
Also reduce the LIMIT to 50, for smaller I/O operations.
Differential Revision: https://phabricator.services.mozilla.com/D277312
Diffstat:
1 file changed, 6 insertions(+), 8 deletions(-)
diff --git a/toolkit/components/places/PlacesExpiration.sys.mjs b/toolkit/components/places/PlacesExpiration.sys.mjs
@@ -271,17 +271,15 @@ const EXPIRATION_QUERIES = {
WHERE (page_id, icon_id) IN (
SELECT page_id, icon_id
FROM moz_icons_to_pages ip
- JOIN moz_icons i ON i.id = icon_id
JOIN moz_pages_w_icons pi ON pi.id = page_id
JOIN moz_places ON url_hash = page_url_hash
WHERE
- last_visit_date BETWEEN
- strftime('%s', ip.expire_ms / 1000, 'unixepoch', '+6 months', 'localtime', 'utc') * 1000000
- AND strftime('%s', 'now', 'localtime', '-6 months', 'utc') * 1000000
- AND root = 0
- AND foreign_count = 0
- ORDER BY last_visit_date ASC
- LIMIT 100
+ last_visit_date BETWEEN
+ strftime('%s', ip.expire_ms / 1000, 'unixepoch', '+6 months', 'localtime', 'utc') * 1000000
+ AND strftime('%s', 'now', 'localtime', '-6 months', 'utc') * 1000000
+ AND foreign_count = 0
+ AND NOT EXISTS (SELECT 1 FROM moz_icons WHERE id = icon_id AND root = 1)
+ LIMIT 50
)
`,
actions: ACTION.IDLE_DIRTY | ACTION.IDLE_DAILY | ACTION.DEBUG,
