Database performance

[Philipp Kern <pkern@debian.org>]

Hi,

I was looking into slow queries and in general it'd be good if we kept
query latency < 500ms for all queries. One particular example I could
pick from the slow log was the one generated by
mirrorruns_get_neighbors_change. For directories that existed since the
dawn of time, the query will take longer and longer because the last_run
will be the most recent run and the first_run will be at the dawn of
time - with all rows in between. In those cases the median assumptions
PostgreSQL's query planner are also pretty off.

This is one of the cases where we should either have a smarter query or
a materialized view. The view is going to be large (6.3G in my
experiments), but will also reduce the query latency to a quarter
(assuming an index on parent).

I guess one AI would be to open up a slow query log to y'all to
investigate. And then we'd need to burn down the slow queries. I'll
start with an MR to add an index for a slow query on binpkg that goes
from ~150ms to instant. For materialized views we'd actually need hooks
to refresh them after mirror imports.

Kind regards
Philipp Kern