bcgov / bcgov/lcfs

LCFS - Optimize ComplianceReport query eager loading and joins

Open
#4,099 0 comments 0 reactions 1 assignee Claimed by @AlexZorkin View on GitHub
Compliance Reporting Medium python Tech Debt
Dominant language
Python
Stars
6
Forks
6
Avg merge
2d 4h
Merged PRs (30d)
87

Description

**Describe the task**
Optimize the ComplianceReport repository queries which use deep nested `joinedload()` chains (lines 77-113 in `compliance_report/repo.py`) that can produce cartesian products and excessive data transfer.

**Purpose**
ComplianceReport queries are the most complex in the system with multi-level eager loading (organization → address, history → status → user → organization). These queries are hit on every report view and list page, making them a top performance bottleneck.

**Acceptance Criteria**
- [ ] Profile current query execution time and generated SQL
- [ ] Replace deep `joinedload()` chains with targeted `selectinload()` or `subqueryload()` where appropriate
- [ ] Eliminate cartesian product scenarios
- [ ] Add database indexes on frequently queried FK columns (organization_id, compliance_period_id, current_status_id)
- [ ] Verify no N+1 regressions introduced
- [ ] Measure and document query time improvement

**Additional context**
- File: `backend/lcfs/web/api/compliance_report/repo.py` (lines 77-113, 206-264)
- `get_assessed_compliance_report_by_period()` has 4+ join operations with outer joins
- Related: ComplianceReport model at `backend/lcfs/db/models/compliance/ComplianceReport.py` has `assigned_analyst` with `lazy="selectin"`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.