mozilla-releng / balrog / #5040

90%

main: 89%

push

circleci

Optimize the query to get active releases for db dumps (#3584)

With an inflated number of releases/rules (60k/10k)

Before (117s):

```
EXPLAIN ANALYZE:
-> Filter: (exists(select #2) or exists(select #3))  (cost=6534 rows=62366) (actual time=6.09..117078 rows=10095 loops=1)
    -> Table scan on releases  (cost=6534 rows=62366) (actual time=0.0167..29 rows=60206 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s)  (cost=1060 rows=1) (actual time=1.94..1.94 rows=0.168 loops=60206)
            -> Filter: (releases.`name` in (rules.mapping,rules.fallbackMapping))  (cost=1060 rows=10362) (actual time=1.94..1.94 rows=0.168 loops=60206)
                -> Table scan on rules  (cost=1060 rows=10362) (actual time=0.0021..1.29 rows=9680 loops=60206)
    -> Select #3 (subquery in condition; dependent)
        -> Limit: 1 row(s)  (cost=0.35 rows=1) (actual time=0.00128..0.00128 rows=0 loops=50111)
            -> Filter: ((rules_scheduled_changes.complete = 0) and (releases.`name` in (rules_scheduled_changes.base_mapping,rules_scheduled_changes.base_fallbackMapping)))  (cost=0.35 rows=1) (actual time=0.0012..0.0012 rows=0 loops=50111)
                -> Table scan on rules_scheduled_changes  (cost=0.35 rows=1) (actual time=0.00111..0.00111 rows=0 loops=50111)
```

After (24ms)

```
EXPLAIN ANALYZE:
-> Table scan on <temporary>  (cost=10495..10731 rows=18654) (actual time=23.8..24.5 rows=10095 loops=1)
    -> Temporary table with deduplication  (cost=10495..10495 rows=18654) (actual time=23.8..23.8 rows=10095 loops=1)
        -> Nested loop inner join  (cost=8630 rows=18654) (actual time=5.81..16.7 rows=10095 loops=1)
            -> Filter: (active_names.mapping is not null)  (cost=3987..2101 rows=18654) (actual time=5.79..6.95 rows=10264 loops=1)
                -> Table scan on active_names  (cost=3987..4223 rows=18654) (actual time=5.79..6.52 rows=10264 loops=1)
                    -> Union materialize with deduplication  (co... (continued)

2167 of 2544 branches covered (85.18%)

Branch coverage included in aggregate %.

5722 of 6218 relevant lines covered (92.02%)

0.92 hits per line