explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xucV

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 366.737 ↑ 1.0 1 1

Aggregate (cost=149,697.54..149,697.55 rows=1 width=288) (actual time=366.737..366.737 rows=1 loops=1)

2. 0.008 366.718 ↑ 1.0 1 1

Subquery Scan on overview (cost=149,697.47..149,697.49 rows=1 width=152) (actual time=366.717..366.718 rows=1 loops=1)

3. 0.018 366.710 ↑ 1.0 1 1

Result (cost=149,697.47..149,697.48 rows=1 width=84) (actual time=366.709..366.710 rows=1 loops=1)

4.          

CTE assigned_cases

5. 214.246 214.246 ↑ 1.0 134,186 1

Index Scan using ixf02_case on dcpscase_t (cost=0.42..22,980.57 rows=138,002 width=12) (actual time=0.025..214.246 rows=134,186 loops=1)

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

Initplan (for Result)

7. 0.019 271.080 ↑ 1.0 1 1

Aggregate (cost=3,106.77..3,106.78 rows=1 width=8) (actual time=271.079..271.080 rows=1 loops=1)

8. 271.061 271.061 ↓ 0.0 0 1

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

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,186
9. 0.004 10.417 ↑ 1.0 1 1

Aggregate (cost=4,140.63..4,140.64 rows=1 width=8) (actual time=10.417..10.417 rows=1 loops=1)

10. 10.413 10.413 ↓ 0.0 0 1

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

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

Aggregate (cost=4,140.63..4,140.64 rows=1 width=8) (actual time=10.147..10.147 rows=1 loops=1)

12. 10.143 10.143 ↓ 0.0 0 1

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

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

Aggregate (cost=4,140.63..4,140.64 rows=1 width=8) (actual time=10.240..10.241 rows=1 loops=1)

14. 10.236 10.236 ↓ 0.0 0 1

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

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

Aggregate (cost=12,355.40..12,355.41 rows=1 width=8) (actual time=9.677..9.677 rows=1 loops=1)

16. 0.001 9.676 ↓ 0.0 0 1

Nested Loop (cost=3,107.20..12,351.04 rows=1,743 width=4) (actual time=9.675..9.676 rows=0 loops=1)

17. 0.003 9.675 ↓ 0.0 0 1

HashAggregate (cost=3,106.77..3,108.71 rows=194 width=4) (actual time=9.675..9.675 rows=0 loops=1)

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

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

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,186
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.004 9.755 ↑ 1.0 1 1

Aggregate (cost=4,775.11..4,775.12 rows=1 width=8) (actual time=9.755..9.755 rows=1 loops=1)

21. 0.000 9.751 ↓ 0.0 0 1

Nested Loop (cost=3,107.48..4,775.09 rows=6 width=4) (actual time=9.751..9.751 rows=0 loops=1)

22. 0.001 9.751 ↓ 0.0 0 1

Nested Loop (cost=3,107.19..4,547.52 rows=535 width=8) (actual time=9.751..9.751 rows=0 loops=1)

23. 0.002 9.750 ↓ 0.0 0 1

HashAggregate (cost=3,106.77..3,108.71 rows=194 width=4) (actual time=9.750..9.750 rows=0 loops=1)

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

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

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,186
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.004 9.569 ↑ 1.0 1 1

Aggregate (cost=4,711.78..4,711.79 rows=1 width=8) (actual time=9.568..9.569 rows=1 loops=1)

28. 0.001 9.565 ↓ 0.0 0 1

Nested Loop (cost=3,107.48..4,711.33 rows=181 width=4) (actual time=9.565..9.565 rows=0 loops=1)

29. 0.001 9.564 ↓ 0.0 0 1

Nested Loop (cost=3,107.19..4,547.52 rows=337 width=8) (actual time=9.564..9.564 rows=0 loops=1)

30. 0.002 9.563 ↓ 0.0 0 1

HashAggregate (cost=3,106.77..3,108.71 rows=194 width=4) (actual time=9.563..9.563 rows=0 loops=1)

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

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

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,186
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.348 ↑ 1.0 1 1

Aggregate (cost=12,842.34..12,842.35 rows=1 width=8) (actual time=16.347..16.348 rows=1 loops=1)

35. 16.345 16.345 ↓ 0.0 0 1

Index Scan using ixn01_refrqst_t on refrqst_t (cost=0.42..12,842.32 rows=10 width=4) (actual time=16.345..16.345 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.813 ↑ 1.0 1 1

Aggregate (cost=38,213.15..38,213.16 rows=1 width=8) (actual time=9.812..9.813 rows=1 loops=1)

37. 0.001 9.811 ↓ 0.0 0 1

Nested Loop (cost=3,239.95..38,209.53 rows=1,448 width=4) (actual time=9.810..9.811 rows=0 loops=1)

38. 0.003 9.810 ↓ 0.0 0 1

HashAggregate (cost=3,106.77..3,108.71 rows=194 width=4) (actual time=9.810..9.810 rows=0 loops=1)

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

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

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,186
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.645 ↑ 1.0 1 1

Aggregate (cost=38,290.34..38,290.35 rows=1 width=8) (actual time=9.645..9.645 rows=1 loops=1)

47. 0.001 9.643 ↓ 0.0 0 1

Nested Loop (cost=3,239.95..38,289.07 rows=510 width=4) (actual time=9.643..9.643 rows=0 loops=1)

48. 0.002 9.642 ↓ 0.0 0 1

HashAggregate (cost=3,106.77..3,108.71 rows=194 width=4) (actual time=9.642..9.642 rows=0 loops=1)

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

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

  • Filter: (asgnd_user_org_uid = 397)
  • Rows Removed by Filter: 134,186
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.587 ms
Execution time : 368.162 ms