explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DW8U

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,026,955.77..1,118,518.24 rows=13,080,352 width=56) (actual rows= loops=)

  • Output: hdi.hcc_code, string_agg((hdi.diagnosis_code)::text, ';'::text), hdi.patient_id, hdi.year, hdi.tenant_id, monthnum.monthnum
  • Group Key: hdi.hcc_code, hdi.patient_id, hdi.year, hdi.tenant_id, monthnum.monthnum
2. 0.000 0.000 ↓ 0.0

Sort (cost=1,026,955.77..1,033,495.95 rows=13,080,352 width=30) (actual rows= loops=)

  • Output: hdi.hcc_code, hdi.patient_id, hdi.year, hdi.tenant_id, monthnum.monthnum, hdi.diagnosis_code
  • Sort Key: hdi.hcc_code, hdi.patient_id, hdi.year, hdi.tenant_id, monthnum.monthnum
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.11..579,128.16 rows=13,080,352 width=30) (actual rows= loops=)

  • Output: hdi.hcc_code, hdi.patient_id, hdi.year, hdi.tenant_id, monthnum.monthnum, hdi.diagnosis_code
  • Join Filter: (COALESCE(hdi.current_year_claim_as_of, hdi.created_date) <= (((concat(monthnum.monthnum, '/01/', hdi.year))::date + '1 mon'::interval) - '00:00:00.001'::interval))
4. 0.000 0.000 ↓ 0.0

Index Scan using hcc_diagnosis_index_dx_hcc_type_index on hccapp.hcc_diagnosis_index hdi (cost=0.11..225,958.65 rows=3,270,088 width=42) (actual rows= loops=)

  • Output: hdi.id, hdi.patient_id, hdi.diagnosis_description, hdi.diagnosis_code, hdi.hcc_code, hdi.hcc_description, hdi.raf, hdi.invalid_diagnosis_reason_id, hdi.ui_state_id, hdi.invalid_diagnosis_reason_aud, hdi.key, hdi.year, hdi.diagnosis_status_comment_aud, hdi.manually_added, hdi.created_date, hdi.modified_date, hdi.created_by, hdi.modified_by, hdi.current_year_documented, hdi.current_year_claim, hdi.current_year_claim_aud, hdi.previous_years_claim, hdi.previous_years_claim_aud, hdi.valid_aud, hdi.meat_aud, hdi.current_year_documented_aud, hdi.previous_years_documented, hdi.tenant_id, hdi.hcc_type, hdi.current_year_documented_as_of, hdi.current_year_claim_as_of
  • Index Cond: (hdi.current_year_claim = true)
  • Filter: ((hdi.invalid_diagnosis_reason_id IS NULL) AND (COALESCE(hdi.current_year_claim_as_of, hdi.created_date) >= (concat('01/01/', hdi.year))::date))
5. 0.000 0.000 ↓ 0.0

Function Scan on pg_catalog.generate_series monthnum (cost=0.00..0.04 rows=12 width=4) (actual rows= loops=)

  • Output: monthnum.monthnum" Function Call: generate_series(1, 12)