explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PO7Z

Settings
# exclusive inclusive rows x rows loops node
1. 144.215 1,687.097 ↓ 16,686.0 16,686 1

Sort (cost=53,625.27..53,625.27 rows=1 width=339) (actual time=1,675.163..1,687.097 rows=16,686 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,120kB
2. 185.225 1,542.882 ↓ 16,686.0 16,686 1

Nested Loop Left Join (cost=11,553.52..53,625.26 rows=1 width=339) (actual time=174.429..1,542.882 rows=16,686 loops=1)

3. 23.723 306.439 ↓ 16,686.0 16,686 1

Nested Loop Left Join (cost=11,553.24..53,586.20 rows=1 width=199) (actual time=174.243..306.439 rows=16,686 loops=1)

4. 24.572 282.716 ↓ 16,686.0 16,686 1

Nested Loop Left Join (cost=11,552.95..53,585.84 rows=1 width=191) (actual time=174.233..282.716 rows=16,686 loops=1)

5. 14.041 258.144 ↓ 16,686.0 16,686 1

Hash Left Join (cost=11,552.67..53,584.51 rows=1 width=187) (actual time=174.223..258.144 rows=16,686 loops=1)

  • Hash Cond: (dcpscase_t.dcps_case_uid = astrqt_t.dcps_case_uid)
  • Join Filter: (astrqt_t.astrqt_uid = (SubPlan 9))
6. 23.782 242.878 ↓ 16,686.0 16,686 1

Nested Loop Left Join (cost=10,753.54..51,721.43 rows=1 width=175) (actual time=172.982..242.878 rows=16,686 loops=1)

7. 0.000 185.724 ↓ 16,686.0 16,686 1

Gather (cost=10,753.25..51,721.09 rows=1 width=163) (actual time=172.967..185.724 rows=16,686 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 10.554 400.764 ↓ 5,562.0 5,562 3 / 3

Nested Loop (cost=9,753.25..50,720.99 rows=1 width=163) (actual time=166.218..400.764 rows=5,562 loops=3)

  • 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: 4,188
9. 4.759 234.381 ↑ 1.0 5,565 3 / 3

Hash Left Join (cost=9,752.82..28,259.66 rows=5,691 width=143) (actual time=166.156..234.381 rows=5,565 loops=3)

  • Hash Cond: (userorganization_t.org_uid = organization_t.org_uid)
10. 4.456 222.263 ↑ 1.0 5,565 3 / 3

Hash Left Join (cost=9,419.31..27,911.21 rows=5,691 width=135) (actual time=158.760..222.263 rows=5,565 loops=3)

  • Hash Cond: (dcpscase_t.asgnd_user_org_uid = userorganization_t.user_org_uid)
11. 4.695 205.464 ↑ 1.0 5,565 3 / 3

Hash Join (cost=8,923.62..27,400.58 rows=5,691 width=131) (actual time=146.343..205.464 rows=5,565 loops=3)

  • Hash Cond: (stcatlgprod_t.prod_uid = product_t.prod_uid)
12. 4.506 193.516 ↑ 1.0 5,565 3 / 3

Hash Join (cost=8,638.48..27,100.48 rows=5,691 width=102) (actual time=139.018..193.516 rows=5,565 loops=3)

  • Hash Cond: (cerqstitm_t.st_catlg_prod_uid = stcatlgprod_t.st_catlg_prod_uid)
13. 13.846 175.276 ↑ 1.0 5,565 3 / 3

Nested Loop (cost=8,159.52..26,606.59 rows=5,691 width=102) (actual time=125.209..175.276 rows=5,565 loops=3)

14. 10.312 144.734 ↑ 1.0 5,565 3 / 3

Parallel Hash Join (cost=8,159.10..12,733.31 rows=5,691 width=64) (actual time=125.177..144.734 rows=5,565 loops=3)

  • Hash Cond: (cerqstitm_t.ce_sub_rqst_uid = cesubrqst_t.ce_sub_rqst_uid)
15. 15.029 15.029 ↑ 1.3 13,772 3 / 3

Parallel Seq Scan on cerqstitm_t (cost=0.00..4,475.89 rows=17,289 width=12) (actual time=0.008..15.029 rows=13,772 loops=3)

  • Filter: ((ce_itm_stus_cd)::text = 'PNDS'::text)
  • Rows Removed by Filter: 72,978
16. 13.572 119.393 ↑ 1.8 20,007 3 / 3

Parallel Hash (cost=7,714.48..7,714.48 rows=35,570 width=56) (actual time=119.393..119.393 rows=20,007 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,696kB
17. 44.323 105.821 ↑ 1.8 20,007 3 / 3

Parallel Hash Join (cost=4,818.16..7,714.48 rows=35,570 width=56) (actual time=35.944..105.821 rows=20,007 loops=3)

  • Hash Cond: (cesubrqst_t.ce_rqst_uid = cerequest_t.ce_rqst_uid)
18. 25.720 25.720 ↑ 1.8 61,228 3 / 3

Parallel Seq Scan on cesubrqst_t (cost=0.00..2,612.64 rows=108,064 width=8) (actual time=0.005..25.720 rows=61,228 loops=3)

19. 12.594 35.778 ↑ 1.2 19,701 3 / 3

Parallel Hash (cost=4,511.38..4,511.38 rows=24,542 width=52) (actual time=35.777..35.778 rows=19,701 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,184kB
20. 23.184 23.184 ↑ 1.2 19,701 3 / 3

Parallel Seq Scan on cerequest_t (cost=0.00..4,511.38 rows=24,542 width=52) (actual time=0.010..23.184 rows=19,701 loops=3)

  • 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: 39,893
21. 16.696 16.696 ↑ 1.0 1 16,696 / 3

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

  • Index Cond: (dcps_case_uid = cerequest_t.dcps_case_uid)
22. 6.952 13.734 ↑ 1.0 14,309 3 / 3

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

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

Seq Scan on stcatlgprod_t (cost=0.00..300.09 rows=14,309 width=8) (actual time=0.009..6.782 rows=14,309 loops=3)

24. 3.867 7.253 ↑ 1.0 7,873 3 / 3

Hash (cost=186.73..186.73 rows=7,873 width=37) (actual time=7.252..7.253 rows=7,873 loops=3)

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

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

26. 6.280 12.343 ↑ 1.0 12,599 3 / 3

Hash (cost=337.53..337.53 rows=12,653 width=12) (actual time=12.343..12.343 rows=12,599 loops=3)

  • Buckets: 16,384 Batches: 1 Memory Usage: 670kB
27. 6.063 6.063 ↑ 1.0 12,599 3 / 3

Seq Scan on userorganization_t (cost=0.00..337.53 rows=12,653 width=12) (actual time=0.011..6.063 rows=12,599 loops=3)

28. 3.414 7.359 ↑ 1.0 7,267 3 / 3

Hash (cost=242.67..242.67 rows=7,267 width=12) (actual time=7.359..7.359 rows=7,267 loops=3)

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

Seq Scan on organization_t (cost=0.00..242.67 rows=7,267 width=12) (actual time=0.008..3.945 rows=7,267 loops=3)

30. 155.829 155.829 ↓ 2.0 2 16,696 / 3

Index Scan using ixpk_todoitemlist_t on todoitemlist_t (cost=0.43..3.93 rows=1 width=36) (actual time=0.019..0.028 rows=2 loops=16,696)

  • 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.372 33.372 ↑ 1.0 1 16,686

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,686)

  • Index Cond: (user_uid = userorganization_t.user_uid)
32. 0.367 1.225 ↓ 1.0 972 1

Hash (cost=787.02..787.02 rows=969 width=16) (actual time=1.225..1.225 rows=972 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
33. 0.786 0.858 ↓ 1.0 972 1

Bitmap Heap Scan on astrqt_t (cost=24.09..787.02 rows=969 width=16) (actual time=0.092..0.858 rows=972 loops=1)

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

Bitmap Index Scan on ix01_astrqt (cost=0.00..23.85 rows=969 width=0) (actual time=0.072..0.072 rows=976 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,063.91..1,063.92 rows=1 width=4) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,063.91..1,063.92 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,063.90 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,055.59 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,686

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

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

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,686)

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

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,686)

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

SubPlan (for Nested Loop Left Join)

45. 33.372 83.430 ↑ 1.0 1 16,686

Result (cost=8.68..8.69 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=16,686)

46.          

Initplan (for Result)

47. 50.058 50.058 ↑ 2.0 1 16,686

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

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
48. 33.372 684.126 ↑ 1.0 1 16,686

Result (cost=12.98..12.99 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=16,686)

49.          

Initplan (for Result)

50. 650.754 650.754 ↓ 0.0 0 16,686

Seq Scan on cerqstspecarrngmnt_t (cost=0.00..12.98 rows=1 width=5) (actual time=0.039..0.039 rows=0 loops=16,686)

  • Filter: (cerequest_t.ce_rqst_uid = ce_rqst_uid)
  • Rows Removed by Filter: 561
51. 33.372 150.174 ↑ 1.0 1 16,686

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

52.          

Initplan (for Result)

53. 116.802 116.802 ↑ 1.0 2 16,686

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

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
54. 16.686 133.488 ↑ 1.0 1 16,686

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

55.          

Initplan (for Result)

56. 116.802 116.802 ↑ 1.0 1 16,686

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

  • Index Cond: (dcps_case_uid = cerequest_t.dcps_case_uid)
Planning time : 13.982 ms
Execution time : 1,694.723 ms