Problem
frs_habitat_classify runs the NOT EXISTS accessibility query once per species. Species sharing the same access threshold (CO/CH/SK all at 15%) run identical queries — 3x redundant work. For BULK (32k segments, 7 species), classify takes 5+ min per species.
Two optimizations needed
1. Recycle accessibility by threshold group
Compute accessibility ONCE per unique threshold, store result, map to species:
Threshold 15% → accessible_15 (computed once)
CO, CH, SK, PK → use accessible_15
Threshold 20% → accessible_20
ST → use accessible_20
Threshold 25% → accessible_25
BT, RB → use accessible_25
3 queries instead of 7. Same results.
2. Index breaks table on (label, blue_line_key)
The NOT EXISTS queries filter by b.label IN (...) then join on b.blue_line_key. A composite index would make this instant. Currently .frs_index_working indexes label and blue_line_key separately.
Impact
Current ADMS (5 species): ~183s
Estimated with recycling: ~90s (2 threshold queries instead of 5)
Estimated with recycling + composite index: ~30s
Relates to
Problem
frs_habitat_classifyruns the NOT EXISTS accessibility query once per species. Species sharing the same access threshold (CO/CH/SK all at 15%) run identical queries — 3x redundant work. For BULK (32k segments, 7 species), classify takes 5+ min per species.Two optimizations needed
1. Recycle accessibility by threshold group
Compute accessibility ONCE per unique threshold, store result, map to species:
3 queries instead of 7. Same results.
2. Index breaks table on (label, blue_line_key)
The NOT EXISTS queries filter by
b.label IN (...)then join onb.blue_line_key. A composite index would make this instant. Currently.frs_index_workingindexeslabelandblue_line_keyseparately.Impact
Current ADMS (5 species): ~183s
Estimated with recycling: ~90s (2 threshold queries instead of 5)
Estimated with recycling + composite index: ~30s
Relates to