explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0dN

Settings
# exclusive inclusive rows x rows loops node
1. 6,540.772 36,749.917 ↓ 2.0 4,444,294 1

HashAggregate (cost=1,427,477.96..1,478,039.39 rows=2,247,175 width=161) (actual time=34,422.593..36,749.917 rows=4,444,294 loops=1)

  • Group Key: cd.dwh_txn_seq, cd.dwh_txn_ts, cd.dwh_txn_oper_cd, cd.dwh_clndr_dt, lag(cd.dwh_clndr_dt, 1, cd.dwh_clndr_dt) OVER (?), CASE WHEN ((cd.case_stus_cd)::text = '4'::text) THEN lead(cd.dwh_clndr_dt, 1, cd.dwh_clndr_dt) OVER (?) ELSE lead(cd.dwh_clndr_dt, 1, '2020-09-09'::date) OVER (?) END, cd.dcps_case_uid, cd.adjulvl_cd, cd.case_clsfn_cd, cd.dds_ccrnt_case_ind, (lag(cd.asgnd_user_org_uid, 1, cd.asgnd_user_org_uid) OVER (?)), cd.asgnd_user_org_uid, (lead(cd.asgnd_user_org_uid, 1, cd.asgnd_user_org_uid) OVER (?)), cd.cdr_typ, cd.case_stus_cd, cd.case_rcpdt, cd.case_asgnd_dt, cd.case_closd_dt, CASE WHEN (((cd.case_stus_cd)::text = '4'::text) AND (cd.case_closd_dt IS NOT NULL) AND (cd.case_closd_dt = cd.dwh_clndr_dt)) THEN (rank() OVER (?)) ELSE '0'::bigint END, cd.insrt_ts, date(timezone('America/New_York'::text, cd.insrt_ts)), cd.dwh_crnt_row_sw, 'N'::text, cd.lu_ts, cd.dwh_end_of_day_sw
  • OR (((case_stus_cd)::text = '5'::text) AND ((dwh_crnt_row_sw)::text = 'Y'::text) AND (dwh_clndr_dt <= '2016-01-01'::date))))
2. 5,613.433 30,209.145 ↓ 2.0 4,444,294 1

WindowAgg (cost=1,202,760.46..1,287,029.52 rows=2,247,175 width=161) (actual time=24,250.004..30,209.145 rows=4,444,294 loops=1)

3. 7,218.538 24,595.712 ↓ 2.0 4,444,294 1

Sort (cost=1,202,760.46..1,208,378.39 rows=2,247,175 width=117) (actual time=24,249.769..24,595.712 rows=4,444,294 loops=1)

  • Sort Key: cd.dcps_case_uid, cd.dwh_txn_seq
  • Sort Method: quicksort Memory: 1,342,678kB
4. 3,234.076 17,377.174 ↓ 2.0 4,444,294 1

WindowAgg (cost=915,125.64..965,687.08 rows=2,247,175 width=117) (actual time=13,859.900..17,377.174 rows=4,444,294 loops=1)

5. 3,473.036 14,143.098 ↓ 2.0 4,444,294 1

Sort (cost=915,125.64..920,743.58 rows=2,247,175 width=109) (actual time=13,859.885..14,143.098 rows=4,444,294 loops=1)

  • Sort Key: cd.dcps_case_uid, cd.dwh_txn_ts
  • Sort Method: quicksort Memory: 1,342,678kB
6. 3,211.354 10,670.062 ↓ 2.0 4,444,294 1

WindowAgg (cost=616,254.95..678,052.26 rows=2,247,175 width=109) (actual time=6,857.114..10,670.062 rows=4,444,294 loops=1)

7. 4,655.106 7,458.708 ↓ 2.0 4,444,294 1

Sort (cost=616,254.95..621,872.89 rows=2,247,175 width=101) (actual time=6,857.101..7,458.708 rows=4,444,294 loops=1)

  • Sort Key: cd.dcps_case_uid, cd.case_closd_dt, cd.dwh_clndr_dt, cd.asgnd_user_org_uid, cd.dwh_txn_ts DESC
  • Sort Method: quicksort Memory: 1,231,867kB
8. 2,789.701 2,803.602 ↓ 2.0 4,444,294 1

Seq Scan on dcpscase_dim cd (cost=9,211.69..379,181.57 rows=2,247,175 width=101) (actual time=15.829..2,803.602 rows=4,444,294 loops=1)

  • Filter: (((adjulvl_cd)::text = ANY ('{1,2}'::text[])) AND (NOT (hashed SubPlan 1)) AND (((dwh_clndr_dt > '2016-01-01'::date) AND (dwh_clndr_dt <= '2020-09-09'::date))
  • Rows Removed by Filter: 139,747
9.          

SubPlan (for Seq Scan)

10. 5.304 13.901 ↓ 1.2 13,596 1

HashAggregate (cost=9,069.50..9,183.25 rows=11,375 width=4) (actual time=12.577..13.901 rows=13,596 loops=1)

  • Group Key: dcpscase_dim.dcps_case_uid
11. 8.597 8.597 ↓ 1.2 13,596 1

Index Scan using dcpscase_dim_ixn03 on dcpscase_dim (cost=0.43..9,039.97 rows=11,810 width=4) (actual time=0.021..8.597 rows=13,596 loops=1)

  • Index Cond: ((dwh_txn_oper_cd)::text = 'D'::text)
Planning time : 0.899 ms
Execution time : 37,195.445 ms