explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3EZH

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 361.182 ↑ 1.0 1 1

Aggregate (cost=149,672.37..149,672.38 rows=1 width=288) (actual time=361.182..361.182 rows=1 loops=1)

2. 0.009 361.154 ↑ 1.0 1 1

Subquery Scan on overview (cost=149,672.31..149,672.33 rows=1 width=152) (actual time=361.153..361.154 rows=1 loops=1)

3. 0.020 361.145 ↑ 1.0 1 1

Result (cost=149,672.31..149,672.32 rows=1 width=84) (actual time=361.145..361.145 rows=1 loops=1)

4.          

CTE assigned_cases

5. 208.091 208.091 ↑ 1.0 134,190 1

Index Scan using ixf02_case on dcpscase_t (cost=0.42..22,972.63 rows=137,949 width=12) (actual time=0.029..208.091 rows=134,190 loops=1)

  • Index Cond: ((case_stus_cd)::text = '5'::text)
6.          

Initplan (for Result)

7. 0.016 264.657 ↑ 1.0 1 1

Aggregate (cost=3,105.58..3,105.59 rows=1 width=8) (actual time=264.656..264.657 rows=1 loops=1)

8. 264.641 264.641 ↓ 0.0 0 1

CTE Scan on assigned_cases (cost=0.00..3,103.85 rows=690 width=4) (actual time=264.641..264.641 rows=0 loops=1)

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,190
9. 0.007 10.214 ↑ 1.0 1 1

Aggregate (cost=4,139.05..4,139.06 rows=1 width=8) (actual time=10.213..10.214 rows=1 loops=1)

10. 10.207 10.207 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_1 (cost=0.00..4,138.47 rows=230 width=4) (actual time=10.207..10.207 rows=0 loops=1)

  • Filter: ((asgnd_user_org_uid = 397) AND (case_asgnd_dt <= (now() - '120 days'::interval)))
  • Rows Removed by Filter: 134,190
11. 0.006 10.244 ↑ 1.0 1 1

Aggregate (cost=4,139.05..4,139.06 rows=1 width=8) (actual time=10.243..10.244 rows=1 loops=1)

12. 10.238 10.238 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_2 (cost=0.00..4,138.47 rows=230 width=4) (actual time=10.238..10.238 rows=0 loops=1)

  • Filter: ((asgnd_user_org_uid = 397) AND (case_asgnd_dt <= (now() - '90 days'::interval)))
  • Rows Removed by Filter: 134,190
13. 0.005 9.948 ↑ 1.0 1 1

Aggregate (cost=4,139.05..4,139.06 rows=1 width=8) (actual time=9.947..9.948 rows=1 loops=1)

14. 9.943 9.943 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_3 (cost=0.00..4,138.47 rows=230 width=4) (actual time=9.943..9.943 rows=0 loops=1)

  • Filter: ((asgnd_user_org_uid = 397) AND (case_asgnd_dt <= (now() - '60 days'::interval)))
  • Rows Removed by Filter: 134,190
15. 0.002 9.879 ↑ 1.0 1 1

Aggregate (cost=12,354.20..12,354.21 rows=1 width=8) (actual time=9.879..9.879 rows=1 loops=1)

16. 0.001 9.877 ↓ 0.0 0 1

Nested Loop (cost=3,106.01..12,349.85 rows=1,742 width=4) (actual time=9.876..9.877 rows=0 loops=1)

17. 0.003 9.876 ↓ 0.0 0 1

HashAggregate (cost=3,105.58..3,107.52 rows=194 width=4) (actual time=9.876..9.876 rows=0 loops=1)

  • Group Key: assigned_cases_4.dcps_case_uid
18. 9.873 9.873 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_4 (cost=0.00..3,103.85 rows=690 width=4) (actual time=9.873..9.873 rows=0 loops=1)

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,190
19. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_todoitemlist_t_dcps_case_uid on todoitemlist_t (cost=0.43..47.61 rows=3 width=8) (never executed)

  • Index Cond: (dcps_case_uid = assigned_cases_4.dcps_case_uid)
  • Filter: ((todo_itm_cmpld_dt IS NULL) AND (deld_ts IS NULL) AND ((todo_itm_cd)::text = ANY ('{UREV,UENR}'::text[])))
20. 0.006 10.619 ↑ 1.0 1 1

Aggregate (cost=4,773.92..4,773.93 rows=1 width=8) (actual time=10.618..10.619 rows=1 loops=1)

21. 0.001 10.613 ↓ 0.0 0 1

Nested Loop (cost=3,106.29..4,773.90 rows=6 width=4) (actual time=10.613..10.613 rows=0 loops=1)

22. 0.001 10.612 ↓ 0.0 0 1

Nested Loop (cost=3,105.99..4,546.33 rows=535 width=8) (actual time=10.612..10.612 rows=0 loops=1)

23. 0.002 10.611 ↓ 0.0 0 1

HashAggregate (cost=3,105.58..3,107.52 rows=194 width=4) (actual time=10.611..10.611 rows=0 loops=1)

  • Group Key: assigned_cases_5.dcps_case_uid
24. 10.609 10.609 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_5 (cost=0.00..3,103.85 rows=690 width=4) (actual time=10.608..10.609 rows=0 loops=1)

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,190
25. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_qasamp_t on qasamp_t (cost=0.42..7.41 rows=1 width=8) (never executed)

  • Index Cond: (dcps_case_uid = assigned_cases_5.dcps_case_uid)
  • Filter: ((qa_rev_cd)::text = 'INQA'::text)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_qacsrev_t on qacsrev_t (cost=0.29..0.42 rows=1 width=4) (never executed)

  • Index Cond: (qasamp_uid = qasamp_t.qasamp_uid)
  • Filter: ((qa_stus_cd)::text = ANY ('{FLRW,NDCR}'::text[]))
27. 0.011 9.817 ↑ 1.0 1 1

Aggregate (cost=4,710.59..4,710.60 rows=1 width=8) (actual time=9.817..9.817 rows=1 loops=1)

28. 0.001 9.806 ↓ 0.0 0 1

Nested Loop (cost=3,106.29..4,710.13 rows=181 width=4) (actual time=9.805..9.806 rows=0 loops=1)

29. 0.001 9.805 ↓ 0.0 0 1

Nested Loop (cost=3,105.99..4,546.33 rows=337 width=8) (actual time=9.805..9.805 rows=0 loops=1)

30. 0.002 9.804 ↓ 0.0 0 1

HashAggregate (cost=3,105.58..3,107.52 rows=194 width=4) (actual time=9.804..9.804 rows=0 loops=1)

  • Group Key: assigned_cases_6.dcps_case_uid
31. 9.802 9.802 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_6 (cost=0.00..3,103.85 rows=690 width=4) (actual time=9.801..9.802 rows=0 loops=1)

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,190
32. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_qasamp_t on qasamp_t qasamp_t_1 (cost=0.42..7.41 rows=1 width=8) (never executed)

  • Index Cond: (dcps_case_uid = assigned_cases_6.dcps_case_uid)
  • Filter: ((qa_rev_cd)::text = 'FDQA'::text)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_qacsrev_t on qacsrev_t qacsrev_t_1 (cost=0.29..0.48 rows=1 width=4) (never executed)

  • Index Cond: (qasamp_uid = qasamp_t_1.qasamp_uid)
  • Filter: ((qa_stus_cd)::text <> ALL ('{CMPL,SMPL}'::text[]))
34. 0.003 16.115 ↑ 1.0 1 1

Aggregate (cost=12,837.05..12,837.06 rows=1 width=8) (actual time=16.114..16.115 rows=1 loops=1)

35. 16.112 16.112 ↓ 0.0 0 1

Index Scan using ixn01_refrqst_t on refrqst_t (cost=0.42..12,837.02 rows=10 width=4) (actual time=16.111..16.112 rows=0 loops=1)

  • Index Cond: (assgnd_to_user_org_uid = 397)
  • Filter: ((refl_stus_cd)::text <> ALL ('{CMPD,RTCT}'::text[]))
36. 0.002 9.821 ↑ 1.0 1 1

Aggregate (cost=38,211.96..38,211.97 rows=1 width=8) (actual time=9.821..9.821 rows=1 loops=1)

37. 0.001 9.819 ↓ 0.0 0 1

Nested Loop (cost=3,238.75..38,208.34 rows=1,448 width=4) (actual time=9.819..9.819 rows=0 loops=1)

38. 0.002 9.818 ↓ 0.0 0 1

HashAggregate (cost=3,105.58..3,107.52 rows=194 width=4) (actual time=9.818..9.818 rows=0 loops=1)

  • Group Key: assigned_cases_7.dcps_case_uid
39. 9.816 9.816 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_7 (cost=0.00..3,103.85 rows=690 width=4) (actual time=9.815..9.816 rows=0 loops=1)

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,190
40. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_todoitemlist_t_dcps_case_uid on todoitemlist_t todoitemlist_t_1 (cost=133.18..180.91 rows=2 width=8) (never executed)

  • Index Cond: (dcps_case_uid = assigned_cases_7.dcps_case_uid)
  • Filter: ((todo_itm_cmpld_dt IS NULL) AND (deld_ts IS NULL) AND ((todo_itm_cd)::text = 'FLUP'::text) AND (((fup_typ)::text = ANY ('{GENL,MCPC,NOTE,NOTC,NOTM,PD}'::text[])) OR (hashed SubPlan 10)))
41.          

SubPlan (for Index Scan)

42. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.29..132.71 rows=12 width=4) (never executed)

  • Join Filter: ((fupdshbasc_t.dshb_nm_cd)::text = (userdshb_t.dshb_nm_cd)::text)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on fupdshbasc_t (cost=0.00..120.23 rows=20 width=9) (never executed)

  • Filter: ((org_uid = 3,166) AND ((eff_endt > now()) OR (eff_endt IS NULL)))
44. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.29..10.70 rows=6 width=5) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_userdshb_t on userdshb_t (cost=0.29..10.67 rows=6 width=5) (never executed)

  • Index Cond: (user_org_uid = 397)
  • Filter: ((eff_endt IS NULL) OR (eff_endt > now()))
46. 0.002 9.811 ↑ 1.0 1 1

Aggregate (cost=38,289.15..38,289.16 rows=1 width=8) (actual time=9.811..9.811 rows=1 loops=1)

47. 0.019 9.809 ↓ 0.0 0 1

Nested Loop (cost=3,238.75..38,287.88 rows=509 width=4) (actual time=9.809..9.809 rows=0 loops=1)

48. 0.003 9.790 ↓ 0.0 0 1

HashAggregate (cost=3,105.58..3,107.52 rows=194 width=4) (actual time=9.790..9.790 rows=0 loops=1)

  • Group Key: assigned_cases_8.dcps_case_uid
49. 9.787 9.787 ↓ 0.0 0 1

CTE Scan on assigned_cases assigned_cases_8 (cost=0.00..3,103.85 rows=690 width=4) (actual time=9.787..9.787 rows=0 loops=1)

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,190
50. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_todoitemlist_t_dcps_case_uid on todoitemlist_t todoitemlist_t_2 (cost=133.18..181.33 rows=1 width=8) (never executed)

  • Index Cond: (dcps_case_uid = assigned_cases_8.dcps_case_uid)
  • Filter: ((todo_itm_cmpld_dt IS NULL) AND (deld_ts IS NULL) AND ((todo_itm_cd)::text = 'FLUP'::text) AND (fup_duedt < (now())::date) AND (((fup_typ)::text = ANY ('{GENL,MCPC,NOTE,NOTC,NOTM,PD}'::text[])) OR (hashed SubPlan (...)
51.          

SubPlan (for Index Scan)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.29..132.71 rows=12 width=4) (never executed)

  • Join Filter: ((fupdshbasc_t_1.dshb_nm_cd)::text = (userdshb_t_1.dshb_nm_cd)::text)
53. 0.000 0.000 ↓ 0.0 0

Seq Scan on fupdshbasc_t fupdshbasc_t_1 (cost=0.00..120.23 rows=20 width=9) (never executed)

  • Filter: ((org_uid = 3,166) AND ((eff_endt > now()) OR (eff_endt IS NULL)))
54. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.29..10.70 rows=6 width=5) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_userdshb_t on userdshb_t userdshb_t_1 (cost=0.29..10.67 rows=6 width=5) (never executed)

  • Index Cond: (user_org_uid = 397)
  • Filter: ((eff_endt IS NULL) OR (eff_endt > now()))
Planning time : 2.624 ms
Execution time : 362.696 ms