Investigate most heavy queries and see if constraints can be placed on them

[hellais]

We have a dashboard for plotting the most heavy queries against our database (see: https://grafana.ooni.org/d/b5401f39-9e48-47a9-bf87-4d8984c35fb1/slowest-clickhouse-queries?orgId=1). From here we can look into some of the ones that have the highest runtime and try to see if something can be done to either improve them or apply tighter constraints on them.

So far we have identified these:

• aggregation query which doesn't apply default since and until hence can lead to unbound scan without default settings:

  backend/ooniapi/services/oonimeasurements/src/oonimeasurements/routers/v1/aggregation.py

  Line 288 in d056b84

  "countIf(anomaly = 't' AND confirmed = 'f' AND msm_failure = 'f') AS anomaly_count"

• list_measurements which allows setting arbitrarily wide since and until time ranges. Since in this explorer view we are only really supporting loading one page of data at a time we can probably get away with limiting this to just 6 months (see:

  backend/ooniapi/services/oonimeasurements/src/oonimeasurements/routers/v1/measurements.py

  Line 570 in d056b84

  async def list_measurements(

  ). The performance is especially bad when a category_code filter is applied