2056115 - Poor address bar auto-complete performance in rare cases when places DB table stats become poisoned

Status: RESOLVED FIXED

(Firefox for Android :: General, defect)

Description

[Shawn Zivontsis]

Steps to reproduce:

1. Get your places DB table stats into a poisoned state. I'm not exactly sure how to reproduce this consistently, but it can be explicitly triggered by running:

UPDATE sqlite_stat1 SET stat='1 1'   WHERE tbl='moz_origins' AND idx='hostindex';
UPDATE sqlite_stat1 SET stat='1 1 1' WHERE tbl='moz_origins' AND idx='sqlite_autoindex_moz_origins_1';

It's possible that this might be reproducible with a process in which you have a fresh installation, visit exactly one host, start a sync, and then kill the browser after the sync begins. I think that's how it happened for me, but I haven't specifically reproduced it with that path.
2. Fill your history with plenty of entries.
3. Start typing some letters in the address bar, which lead into a website you visit frequently. For example, "n" (for news.ycombinator.com) or "r" (for reddit.com).

Actual results:

What I actually observed is the autocomplete taking 30-60 seconds or more to write out the rest of the domain name.

Expected results:

I would expect this to complete in seconds or less.

Comment 1

[Shawn Zivontsis]

I tried investigating this using Claude and came to a couple of conclusions, but I'm not too familiar with Firefox dev and these conclusions should be confirmed by someone more experienced.

1. The background task in application-services which runs PRAGMA optimize is not effective because it doesn't include the 0x10000 flag (0x10000 = analyze all tables even if they weren't queried in this connection). See: https://github.com/mozilla/application-services/blob/6a29a76c37859167ff5f56ec0953390723862ac4/components/places/src/storage/mod.rs#L294

Since the connection which runs this task typically won't have queried the affected tables, it won't fix the poisoning. Notice how in Firefox desktop, the equivalent background optimization function does set the 0x10000 flag: https://github.com/mozilla-firefox/firefox/commit/df9ad3fc1d2c#diff-c1baca928c807325696b2213242536acea8de5efb564da762752765b1c482849R238

The reason why I believe the sync pathway is part of the chain of events to reproduce is because it triggers the optimize in the PlacesDb teardown: https://github.com/mozilla/application-services/blob/6a29a76c37859167ff5f56ec0953390723862ac4/components/places/src/db/db.rs#L206

2. The Places DB has no index on moz_origins(host). If the table stats are accurate, this isn't a big deal (although the query is faster with such an index even with good table stats). But, with bad table stats, the query ends up taking many orders of magnitude longer.

I am going to submit a couple PRs to address each of these issues separately. This will be my first contribution to a Mozilla product so let me know if I've done anything wrong or if I'm taking the wrong approach here.

Thanks, Shawn

Comment 2

[Shawn Zivontsis]

I've opened PRs

https://github.com/mozilla/application-services/pull/7484

and

https://github.com/mozilla/application-services/pull/7485

to address this issue.

Thanks, Shawn

Comment 3

[Shawn Zivontsis]

Another detail to note: for those facing this issue, you can mitigate it by running the following code in the main browser context using the remote debugging feature. This will recompute the table stats:

await (async () => {
  const { Sqlite } = ChromeUtils.importESModule("resource://gre/modules/Sqlite.sys.mjs");
  const path = PathUtils.join(PathUtils.parent(PathUtils.parent(PathUtils.profileDir)), "places.sqlite");
  const conn = await Sqlite.openConnection({ path });
  try {
    await conn.execute("ANALYZE");
    const rows = await conn.execute("SELECT idx, stat FROM sqlite_stat1 WHERE tbl='moz_origins'");
    return rows.map(r => `${r.getResultByName("idx")}: ${r.getResultByName("stat")}`).join("\n");
  } finally {
    await conn.close();
  }
})();