explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CPQr

Settings
# exclusive inclusive rows x rows loops node
1. 58.033 1,076.736 ↓ 16,792.0 16,792 1

Sort (cost=11,381.39..11,381.40 rows=1 width=217) (actual time=1,071.284..1,076.736 rows=16,792 loops=1)

  • Sort Key: cerequest_t.ce_rqstd_dt, cerequest_t.ce_rqst_uid
  • Sort Method: external merge Disk: 15,296kB
2. 47.697 1,018.703 ↓ 16,792.0 16,792 1

Nested Loop (cost=1,031.42..11,381.38 rows=1 width=217) (actual time=1.407..1,018.703 rows=16,792 loops=1)

3. 0.000 47.446 ↓ 16,792.0 16,792 1

Gather (cost=1,031.00..11,285.16 rows=1 width=66) (actual time=1.195..47.446 rows=16,792 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 7.418 159.106 ↓ 5,597.0 5,597 3 / 3

Nested Loop (cost=31.00..10,285.06 rows=1 width=66) (actual time=13.620..159.106 rows=5,597 loops=3)

5. 20.356 51.910 ↓ 12.6 19,956 3 / 3

Hash Join (cost=30.57..4,636.44 rows=1,580 width=42) (actual time=0.325..51.910 rows=19,956 loops=3)

  • Hash Cond: (cerequest_t.initg_org_uid = organization_t.site_org_uid)
6. 31.263 31.263 ↑ 1.3 55,080 3 / 3

Parallel Seq Scan on cerequest_t (cost=0.00..4,405.80 rows=69,522 width=42) (actual time=0.010..31.263 rows=55,080 loops=3)

  • Filter: ((hold_ts IS NULL) AND (cncl_ts IS NULL) AND ((deleted_sw)::text <> 'Y'::text))
  • Rows Removed by Filter: 5,193
7. 0.002 0.291 ↑ 1.0 1 3 / 3

Hash (cost=30.56..30.56 rows=1 width=4) (actual time=0.291..0.291 rows=1 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.001 0.289 ↑ 1.0 1 3 / 3

Unique (cost=30.54..30.55 rows=1 width=4) (actual time=0.288..0.289 rows=1 loops=3)

9. 0.021 0.288 ↑ 1.0 1 3 / 3

Sort (cost=30.54..30.55 rows=1 width=4) (actual time=0.288..0.288 rows=1 loops=3)

  • Sort Key: organization_t.site_org_uid
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
10. 0.002 0.267 ↑ 1.0 1 3 / 3

Nested Loop (cost=29.01..30.53 rows=1 width=4) (actual time=0.267..0.267 rows=1 loops=3)

11. 0.003 0.256 ↑ 1.0 1 3 / 3

HashAggregate (cost=28.73..28.74 rows=1 width=4) (actual time=0.256..0.256 rows=1 loops=3)

  • Group Key: usrorgspvr_t.org_uid
12. 0.071 0.253 ↑ 1.0 1 3 / 3

Hash Join (cost=8.31..28.73 rows=1 width=4) (actual time=0.140..0.253 rows=1 loops=3)

  • Hash Cond: (usrorgspvr_t.user_uid = userorganization_t.user_uid)
13. 0.162 0.162 ↓ 1.0 599 3 / 3

Seq Scan on usrorgspvr_t (cost=0.00..18.84 rows=598 width=8) (actual time=0.013..0.162 rows=599 loops=3)

  • Filter: ((eff_stdt <= '2020-09-10'::date) AND ((eff_endt IS NULL) OR (eff_endt > '2020-09-10'::date)))
  • Rows Removed by Filter: 125
14. 0.006 0.020 ↑ 1.0 1 3 / 3

Hash (cost=8.30..8.30 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.014 0.014 ↑ 1.0 1 3 / 3

Index Scan using ixpk_userorganization_t on userorganization_t (cost=0.29..8.30 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=3)

  • Index Cond: (user_org_uid = 3,794)
16. 0.009 0.009 ↑ 1.0 1 3 / 3

Index Scan using ixpk_organization_t on organization_t (cost=0.28..1.79 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=3)

  • Index Cond: (org_uid = usrorgspvr_t.org_uid)
17. 99.778 99.778 ↓ 0.0 0 59,867 / 3

Index Scan using ixfnl01_todoitemlist_t on todoitemlist_t (cost=0.43..3.57 rows=1 width=36) (actual time=0.005..0.005 rows=0 loops=59,867)

  • Index Cond: ((dcps_case_uid = cerequest_t.dcps_case_uid) AND ((todo_itm_cd)::text = 'CESR'::text))
  • Filter: ((cerequest_t.ce_rqst_uid)::text = (prnt_tbl_seq_id)::text)
  • Rows Removed by Filter: 0
18. 50.376 50.376 ↑ 1.0 1 16,792

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..1.18 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=16,792)

  • Index Cond: (dcps_case_uid = cerequest_t.dcps_case_uid)
19.          

SubPlan (for Nested Loop)

20. 16.792 100.752 ↑ 1.0 1 16,792

Result (cost=17.26..17.27 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=16,792)

21.          

Initplan (for Result)

22. 16.366 83.960 ↑ 1.0 1 16,792

Nested Loop (cost=0.84..17.26 rows=1 width=5) (actual time=0.004..0.005 rows=1 loops=16,792)

23. 33.584 33.584 ↑ 1.0 1 16,792

Index Scan using ixn01_cesubrqst_t on cesubrqst_t (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=16,792)

  • Index Cond: (ce_rqst_uid = cerequest_t.ce_rqst_uid)
24. 34.010 34.010 ↑ 2.0 1 17,005

Index Scan using ixn01_cerqstitm_t on cerqstitm_t (cost=0.42..8.80 rows=2 width=9) (actual time=0.002..0.002 rows=1 loops=17,005)

  • Index Cond: (ce_sub_rqst_uid = cesubrqst_t.ce_sub_rqst_uid)
25. 0.000 16.792 ↑ 1.0 1 16,792

Result (cost=8.29..8.30 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=16,792)

26.          

Initplan (for Result)

27. 16.792 16.792 ↓ 0.0 0 16,792

Index Only Scan using ixf01_cerqstspecarrngmnt_t on cerqstspecarrngmnt_t (cost=0.28..8.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=16,792)

  • Index Cond: (ce_rqst_uid = cerequest_t.ce_rqst_uid)
  • Heap Fetches: 232
28. 16.792 453.384 ↑ 1.0 1 16,792

Result (cost=36.08..36.09 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=16,792)

29.          

Initplan (for Result)

30. 16.792 436.592 ↑ 1.0 1 16,792

Subquery Scan on t_1 (cost=36.07..36.08 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=16,792)

31. 16.792 419.800 ↑ 1.0 1 16,792

Sort (cost=36.07..36.07 rows=1 width=36) (actual time=0.025..0.025 rows=1 loops=16,792)

  • Sort Key: cesubrqst_t_1.ce_sub_rqst_uid
  • Sort Method: quicksort Memory: 25kB
32. 28.898 403.008 ↑ 1.0 1 16,792

Index Scan using ixn01_cesubrqst_t on cesubrqst_t cesubrqst_t_1 (cost=0.42..36.06 rows=1 width=36) (actual time=0.023..0.024 rows=1 loops=16,792)

  • Index Cond: (ce_rqst_uid = cerequest_t.ce_rqst_uid)
33.          

SubPlan (for Index Scan)

34. 17.005 374.110 ↑ 1.0 1 17,005

Result (cost=27.61..27.62 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=17,005)

35.          

Initplan (for Result)

36. 85.025 357.105 ↑ 2.0 1 17,005

Subquery Scan on t (cost=27.58..27.61 rows=2 width=32) (actual time=0.019..0.021 rows=1 loops=17,005)

37. 34.010 272.080 ↑ 2.0 1 17,005

Sort (cost=27.58..27.58 rows=2 width=71) (actual time=0.015..0.016 rows=1 loops=17,005)

  • Sort Key: cerqstitm_t_1.ce_rqst_itm_uid
  • Sort Method: quicksort Memory: 25kB
38. 10.927 238.070 ↑ 2.0 1 17,005

Nested Loop (cost=0.99..27.57 rows=2 width=71) (actual time=0.009..0.014 rows=1 loops=17,005)

39. 0.000 204.060 ↑ 2.0 1 17,005

Nested Loop (cost=0.71..26.89 rows=2 width=34) (actual time=0.007..0.012 rows=1 loops=17,005)

  • Join Filter: ((cerqstitm_t_1.ce_itm_stus_cd)::text = (ceitmstuscd_t.ce_itm_stus_cd)::text)
  • Rows Removed by Join Filter: 15
40. 17.005 17.005 ↑ 1.0 12 17,005

Seq Scan on ceitmstuscd_t (cost=0.00..1.12 rows=12 width=20) (actual time=0.000..0.001 rows=12 loops=17,005)

41. 136.040 204.060 ↑ 2.0 1 204,060

Materialize (cost=0.71..25.42 rows=2 width=19) (actual time=0.000..0.001 rows=1 loops=204,060)

42. 10.927 68.020 ↑ 2.0 1 17,005

Nested Loop (cost=0.71..25.41 rows=2 width=19) (actual time=0.003..0.004 rows=1 loops=17,005)

43. 34.010 34.010 ↑ 2.0 1 17,005

Index Scan using ixn01_cerqstitm_t on cerqstitm_t cerqstitm_t_1 (cost=0.42..8.80 rows=2 width=13) (actual time=0.002..0.002 rows=1 loops=17,005)

  • Index Cond: (ce_sub_rqst_uid = cesubrqst_t_1.ce_sub_rqst_uid)
  • Filter: ((ce_itm_stus_cd)::text <> 'DELD'::text)
  • Rows Removed by Filter: 0
44. 23.083 23.083 ↑ 1.0 1 23,083

Index Scan using catalog_product_uid_pkey on stcatlgprod_t (cost=0.29..8.30 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=23,083)

  • Index Cond: (st_catlg_prod_uid = cerqstitm_t_1.st_catlg_prod_uid)
45. 23.083 23.083 ↑ 1.0 1 23,083

Index Scan using prod_uid_pkey on product_t (cost=0.28..0.34 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=23,083)

  • Index Cond: (prod_uid = stcatlgprod_t.prod_uid)
46. 16.792 134.336 ↑ 1.0 1 16,792

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

47.          

Initplan (for Result)

48. 117.544 117.544 ↑ 1.0 1 16,792

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

  • Index Cond: (dcps_case_uid = cerequest_t.dcps_case_uid)
49. 16.792 167.920 ↑ 1.0 1 16,792

Result (cost=24.92..24.93 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=16,792)

50.          

Initplan (for Result)

51. 50.376 151.128 ↑ 1.0 1 16,792

Subquery Scan on st (cost=0.85..24.92 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=16,792)

52. 16.792 100.752 ↑ 1.0 1 16,792

Limit (cost=0.85..24.91 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=16,792)

53. 0.000 83.960 ↑ 1.0 1 16,792

Nested Loop (cost=0.85..24.91 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=16,792)

54. 33.584 67.168 ↑ 1.0 1 16,792

Nested Loop (cost=0.57..16.61 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=16,792)

55. 16.792 16.792 ↑ 1.0 1 16,792

Index Scan using ixpk_userorganization_t on userorganization_t userorganization_t_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=16,792)

  • Index Cond: (user_org_uid = dcpscase_t.asgnd_user_org_uid)
56. 16.792 16.792 ↑ 1.0 1 16,792

Index Scan using ixpk_dcpsuser_t on dcpsuser_t (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=16,792)

  • Index Cond: (user_uid = userorganization_t_1.user_uid)
57. 16.792 16.792 ↑ 1.0 1 16,792

Index Scan using ixpk_organization_t on organization_t organization_t_1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=16,792)

  • Index Cond: (org_uid = userorganization_t_1.org_uid)
Planning time : 3.403 ms
Execution time : 1,079.670 ms