explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xomI

Settings
# exclusive inclusive rows x rows loops node
1. 135.940 953.914 ↓ 16,717.0 16,717 1

Sort (cost=69,009.70..69,009.71 rows=1 width=338) (actual time=945.056..953.914 rows=16,717 loops=1)

  • Sort Key: cerequest_t.priort_ind DESC, cerequest_t.sent_for_schg_dt, cerequest_t.ce_rqst_uid, cesubrqst_t.ce_sub_rqst_uid
  • Sort Method: external merge Disk: 14,152kB
2. 175.947 817.974 ↓ 16,717.0 16,717 1

Nested Loop Left Join (cost=12,952.09..69,009.69 rows=1 width=338) (actual time=130.375..817.974 rows=16,717 loops=1)

3. 10.926 240.819 ↓ 16,717.0 16,717 1

Nested Loop Left Join (cost=12,951.81..68,975.33 rows=1 width=198) (actual time=130.238..240.819 rows=16,717 loops=1)

4. 11.547 229.893 ↓ 16,717.0 16,717 1

Nested Loop Left Join (cost=12,951.52..68,974.97 rows=1 width=190) (actual time=130.229..229.893 rows=16,717 loops=1)

5. 9.922 218.346 ↓ 16,717.0 16,717 1

Hash Left Join (cost=12,951.24..68,973.63 rows=1 width=186) (actual time=130.217..218.346 rows=16,717 loops=1)

  • Hash Cond: (dcpscase_t.dcps_case_uid = astrqt_t.dcps_case_uid)
  • Join Filter: (astrqt_t.astrqt_uid = (SubPlan 9))
6. 13.790 207.454 ↓ 16,717.0 16,717 1

Nested Loop Left Join (cost=12,147.93..67,099.64 rows=1 width=174) (actual time=129.217..207.454 rows=16,717 loops=1)

7. 0.000 160.230 ↓ 16,717.0 16,717 1

Gather (cost=12,147.64..67,099.31 rows=1 width=162) (actual time=129.202..160.230 rows=16,717 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
8. 7.730 481.434 ↓ 8,358.0 8,358 2 / 2

Nested Loop (cost=11,147.64..66,099.21 rows=1 width=162) (actual time=123.779..481.434 rows=8,358 loops=2)

  • Join Filter: ((cerequest_t.dcps_case_uid = todoitemlist_t.dcps_case_uid) AND ((cesubrqst_t.ce_sub_rqst_uid)::text = (todoitemlist_t.prnt_tbl_seq_id)::text))
  • Rows Removed by Join Filter: 6,330
9. 4.415 222.799 ↓ 1.0 8,364 2 / 2

Hash Left Join (cost=11,147.21..34,123.74 rows=8,098 width=142) (actual time=123.722..222.799 rows=8,364 loops=2)

  • Hash Cond: (userorganization_t.org_uid = organization_t.org_uid)
10. 4.410 214.213 ↓ 1.0 8,364 2 / 2

Hash Left Join (cost=10,811.82..33,767.08 rows=8,098 width=134) (actual time=119.502..214.213 rows=8,364 loops=2)

  • Hash Cond: (dcpscase_t.asgnd_user_org_uid = userorganization_t.user_org_uid)
11. 4.185 204.067 ↓ 1.0 8,364 2 / 2

Hash Join (cost=10,314.07..33,248.07 rows=8,098 width=130) (actual time=113.676..204.067 rows=8,364 loops=2)

  • Hash Cond: (stcatlgprod_t.prod_uid = product_t.prod_uid)
12. 4.688 195.685 ↓ 1.0 8,364 2 / 2

Hash Join (cost=10,028.93..32,941.65 rows=8,098 width=101) (actual time=109.403..195.685 rows=8,364 loops=2)

  • Hash Cond: (cerqstitm_t.st_catlg_prod_uid = stcatlgprod_t.st_catlg_prod_uid)
13. 20.915 184.913 ↓ 1.0 8,364 2 / 2

Nested Loop (cost=9,549.98..32,441.43 rows=8,098 width=101) (actual time=103.240..184.913 rows=8,364 loops=2)

14. 11.638 138.908 ↓ 1.0 8,364 2 / 2

Parallel Hash Join (cost=9,549.56..12,622.38 rows=8,098 width=63) (actual time=103.201..138.908 rows=8,364 loops=2)

  • Hash Cond: (cesubrqst_t.ce_sub_rqst_uid = cerqstitm_t.ce_sub_rqst_uid)
15. 35.396 96.181 ↑ 1.2 29,967 2 / 2

Parallel Hash Join (cost=4,832.37..7,736.82 rows=35,901 width=55) (actual time=49.236..96.181 rows=29,967 loops=2)

  • Hash Cond: (cesubrqst_t.ce_rqst_uid = cerequest_t.ce_rqst_uid)
16. 11.823 11.823 ↑ 1.2 91,734 2 / 2

Parallel Seq Scan on cesubrqst_t (cost=0.00..2,619.91 rows=108,391 width=8) (actual time=0.006..11.823 rows=91,734 loops=2)

17. 12.667 48.962 ↓ 1.2 29,508 2 / 2

Parallel Hash (cost=4,522.76..4,522.76 rows=24,769 width=51) (actual time=48.962..48.962 rows=29,508 loops=2)

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,184kB
18. 36.295 36.295 ↓ 1.2 29,508 2 / 2

Parallel Seq Scan on cerequest_t (cost=0.00..4,522.76 rows=24,769 width=51) (actual time=0.010..36.295 rows=29,508 loops=2)

  • Filter: ((hold_ts IS NULL) AND (cncl_ts IS NULL) AND (initg_org_uid = 266) AND ((deleted_sw)::text = 'N'::text))
  • Rows Removed by Filter: 59,778
19. 6.044 31.089 ↓ 1.2 20,663 2 / 2

Parallel Hash (cost=4,500.71..4,500.71 rows=17,318 width=12) (actual time=31.089..31.089 rows=20,663 loops=2)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,496kB
20. 25.045 25.045 ↓ 1.2 20,663 2 / 2

Parallel Seq Scan on cerqstitm_t (cost=0.00..4,500.71 rows=17,318 width=12) (actual time=0.018..25.045 rows=20,663 loops=2)

  • Filter: ((ce_itm_stus_cd)::text = 'PNDS'::text)
  • Rows Removed by Filter: 109,312
21. 25.091 25.091 ↑ 1.0 1 16,727 / 2

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..2.45 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=16,727)

  • Index Cond: (dcps_case_uid = cerequest_t.dcps_case_uid)
22. 3.165 6.084 ↑ 1.0 14,309 2 / 2

Hash (cost=300.09..300.09 rows=14,309 width=8) (actual time=6.084..6.084 rows=14,309 loops=2)

  • Buckets: 16,384 Batches: 1 Memory Usage: 687kB
23. 2.919 2.919 ↑ 1.0 14,309 2 / 2

Seq Scan on stcatlgprod_t (cost=0.00..300.09 rows=14,309 width=8) (actual time=0.011..2.919 rows=14,309 loops=2)

24. 2.303 4.197 ↑ 1.0 7,873 2 / 2

Hash (cost=186.73..186.73 rows=7,873 width=37) (actual time=4.197..4.197 rows=7,873 loops=2)

  • Buckets: 8,192 Batches: 1 Memory Usage: 616kB
25. 1.894 1.894 ↑ 1.0 7,873 2 / 2

Seq Scan on product_t (cost=0.00..186.73 rows=7,873 width=37) (actual time=0.011..1.894 rows=7,873 loops=2)

26. 3.039 5.736 ↑ 1.0 12,593 2 / 2

Hash (cost=339.00..339.00 rows=12,700 width=12) (actual time=5.736..5.736 rows=12,593 loops=2)

  • Buckets: 16,384 Batches: 1 Memory Usage: 670kB
27. 2.697 2.697 ↑ 1.0 12,593 2 / 2

Seq Scan on userorganization_t (cost=0.00..339.00 rows=12,700 width=12) (actual time=0.017..2.697 rows=12,593 loops=2)

28. 1.784 4.171 ↑ 1.0 7,267 2 / 2

Hash (cost=244.06..244.06 rows=7,306 width=12) (actual time=4.170..4.171 rows=7,267 loops=2)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
29. 2.387 2.387 ↑ 1.0 7,267 2 / 2

Seq Scan on organization_t (cost=0.00..244.06 rows=7,306 width=12) (actual time=0.014..2.387 rows=7,267 loops=2)

30. 250.905 250.905 ↓ 2.0 2 16,727 / 2

Index Scan using ixpk_todoitemlist_t on todoitemlist_t (cost=0.43..3.93 rows=1 width=36) (actual time=0.021..0.030 rows=2 loops=16,727)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Filter: ((deld_ts IS NULL) AND (todo_itm_cmpld_dt IS NULL) AND ((todo_itm_cd)::text = 'CEPS'::text))
  • Rows Removed by Filter: 29
31. 33.434 33.434 ↑ 1.0 1 16,717

Index Scan using ixpk_dcpsuser_t on dcpsuser_t (cost=0.28..0.34 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=16,717)

  • Index Cond: (user_uid = userorganization_t.user_uid)
32. 0.228 0.970 ↑ 1.0 964 1

Hash (cost=791.26..791.26 rows=964 width=16) (actual time=0.970..0.970 rows=964 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
33. 0.631 0.742 ↑ 1.0 964 1

Bitmap Heap Scan on astrqt_t (cost=24.05..791.26 rows=964 width=16) (actual time=0.133..0.742 rows=964 loops=1)

  • Recheck Cond: ((astg_ofc_astrqt_stus_cd)::text = ANY ('{ASGD,CLSD}'::text[]))
  • Heap Blocks: exact=176
34. 0.111 0.111 ↓ 1.1 1,064 1

Bitmap Index Scan on ix01_astrqt (cost=0.00..23.80 rows=964 width=0) (actual time=0.111..0.111 rows=1,064 loops=1)

  • Index Cond: ((astg_ofc_astrqt_stus_cd)::text = ANY ('{ASGD,CLSD}'::text[]))
35.          

SubPlan (for Hash Left Join)

36. 0.000 0.000 ↓ 0.0 0

Limit (cost=1,070.63..1,070.64 rows=1 width=4) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,070.63..1,070.64 rows=1 width=4) (never executed)

  • Sort Key: astrqt_t_1.astrqt_uid DESC
38. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..1,070.62 rows=1 width=4) (never executed)

  • Join Filter: ((astrqt_t_1.dest_ocd)::text = (organization_t_2.ofc_cd)::text)
39. 0.000 0.000 ↓ 0.0 0

Seq Scan on astrqt_t astrqt_t_1 (cost=0.00..1,062.31 rows=1 width=8) (never executed)

  • Filter: (dcps_case_uid = astrqt_t.dcps_case_uid)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_organization_t on organization_t organization_t_2 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (org_uid = cerequest_t.initg_org_uid)
41. 0.000 0.000 ↓ 0.0 0 16,717

Index Scan using ixpk_userorganization_t on userorganization_t userorganization_t_1 (cost=0.29..1.34 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=16,717)

  • Index Cond: (user_org_uid = astrqt_t.astg_ofc_asgnd_user_org_uid)
42. 0.000 0.000 ↓ 0.0 0 16,717

Index Scan using ixpk_organization_t on organization_t organization_t_1 (cost=0.28..0.36 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=16,717)

  • Index Cond: (org_uid = userorganization_t_1.org_uid)
43. 0.000 0.000 ↓ 0.0 0 16,717

Index Scan using ixpk_dcpsuser_t on dcpsuser_t dcpsuser_t_1 (cost=0.28..0.34 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=16,717)

  • Index Cond: (user_uid = userorganization_t_1.user_uid)
44.          

SubPlan (for Nested Loop Left Join)

45. 16.717 66.868 ↑ 1.0 1 16,717

Result (cost=8.67..8.68 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=16,717)

46.          

Initplan (for Result)

47. 50.151 50.151 ↑ 2.0 1 16,717

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t (cost=0.42..8.67 rows=2 width=3) (actual time=0.003..0.003 rows=1 loops=16,717)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
48. 16.717 33.434 ↑ 1.0 1 16,717

Result (cost=8.29..8.30 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=16,717)

49.          

Initplan (for Result)

50. 16.717 16.717 ↓ 0.0 0 16,717

Index Scan using ix01_cerqstspecarrngmnt on cerqstspecarrngmnt_t (cost=0.28..8.29 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=16,717)

  • Index Cond: (ce_rqst_uid = cerequest_t.ce_rqst_uid)
51. 16.717 150.453 ↑ 1.0 1 16,717

Result (cost=8.57..8.58 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=16,717)

52.          

Initplan (for Result)

53. 133.736 133.736 ↑ 1.0 2 16,717

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.42..8.57 rows=2 width=32) (actual time=0.005..0.008 rows=2 loops=16,717)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
54. 16.717 150.453 ↑ 1.0 1 16,717

Result (cost=8.45..8.46 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=16,717)

55.          

Initplan (for Result)

56. 133.736 133.736 ↑ 1.0 1 16,717

Index Scan using ixf01_caseclm on caseclm_t (cost=0.42..8.45 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=16,717)

  • Index Cond: (dcps_case_uid = cerequest_t.dcps_case_uid)
Planning time : 11.639 ms
Execution time : 958.048 ms