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
