| description |
Bug 2058952 - Avoid a sort operation in the most-frecent-subpage icon query r=places-reviewers,mak
The query in FetchMostFrecentSubPageIcon logged a storage warning:
Suboptimal indexes for the SQL statement `SELECT i.icon_url, i.id, ...
ORDER BY p.frecency DESC, i.width DESC LIMIT 1` [1 sort operation(s)]
The ORDER BY spans moz_places.frecency and moz_icons.width, two tables joined
through the many-to-many moz_icons_to_pages, so no index can supply the
ordering: adding a compound moz_places(rev_host, frecency) index only turns
USE TEMP B-TREE FOR ORDER BY into USE TEMP B-TREE FOR LAST TERM OF ORDER BY.
A single max() over a packed key expresses the same selection without a sort.
moz_icons.width is a uint16, so max(p.frecency * 65536 + i.width) ranks rows
exactly like ORDER BY p.frecency DESC, i.width DESC, and the bare columns come
from the winning row. HAVING count(*) > 0 suppresses the all-NULL row that a
bare aggregate would otherwise return when the host has no subpage with an
icon.
EXPLAIN QUERY PLAN on a real profile is identical to the previous statement
minus USE TEMP B-TREE FOR ORDER BY. Verified equivalent over 800 randomized
differential trials against the previous query, covering varying page counts,
hosts, icon counts, frecency ties, negative frecency, icon-less pages and the
boundary widths 0 and 65535. On a 242k-page profile, summed median latency over
the 15 hosts with the most pages: 121 ms before, 115 ms after.
Differential Revision: https://phabricator.services.mozilla.com/D314986 |
|---|