explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YTAOM

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 7,114.338 ↓ 126.7 13,181 1

Gather (cost=1,155.43..22,716.65 rows=104 width=551) (actual time=48.280..7,114.338 rows=13,181 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 4.175 2,158.830 ↓ 102.2 4,394 3 / 3

Nested Loop Left Join (cost=155.43..14,110.29 rows=43 width=395) (actual time=21.329..2,158.830 rows=4,394 loops=3)

3. 3.024 1,776.800 ↓ 102.2 4,394 3 / 3

Nested Loop Left Join (cost=155.00..14,088.18 rows=43 width=341) (actual time=20.477..1,776.800 rows=4,394 loops=3)

4. 8.301 1,405.912 ↓ 101.8 4,379 3 / 3

Nested Loop (cost=154.58..14,066.09 rows=43 width=316) (actual time=19.991..1,405.912 rows=4,379 loops=3)

5. 6.222 1,380.094 ↓ 101.8 4,379 3 / 3

Nested Loop (cost=154.16..13,959.31 rows=43 width=280) (actual time=19.972..1,380.094 rows=4,379 loops=3)

6. 4.908 801.712 ↓ 14.5 6,357 3 / 3

Hash Join (cost=153.73..13,310.76 rows=437 width=79) (actual time=14.926..801.712 rows=6,357 loops=3)

  • Hash Cond: (pmtrqstpkg_t.initg_org_uid = organization_t.org_uid)
7. 17.868 787.431 ↓ 5.3 24,434 3 / 3

Merge Join (cost=1.07..13,145.90 rows=4,643 width=80) (actual time=3.324..787.431 rows=24,434 loops=3)

  • Merge Cond: (pmtrqstpkg_t.rqst_uid = ceappt_t.ce_appt_uid)
8. 719.208 719.208 ↑ 1.2 24,434 3 / 3

Parallel Index Scan using ixn01_pmtrqstpkg_t on pmtrqstpkg_t (cost=0.42..40,321.31 rows=30,260 width=55) (actual time=0.514..719.208 rows=24,434 loops=3)

  • Filter: ((rqst_typ_cd)::text = 'CE'::text)
  • Rows Removed by Filter: 184073
9. 50.355 50.355 ↑ 2.2 32,963 3 / 3

Index Scan using ceappt_pkey on ceappt_t (cost=0.29..3,561.52 rows=73,326 width=25) (actual time=0.015..50.355 rows=32,963 loops=3)

10. 0.094 9.373 ↑ 1.0 500 3 / 3

Hash (cost=146.41..146.41 rows=500 width=7) (actual time=9.373..9.373 rows=500 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
11. 8.336 9.279 ↑ 1.0 500 3 / 3

Bitmap Heap Scan on organization_t (cost=12.16..146.41 rows=500 width=7) (actual time=1.466..9.279 rows=500 loops=3)

  • Recheck Cond: ((st_cd)::text = 'OH'::text)
  • Heap Blocks: exact=48
12. 0.943 0.943 ↑ 1.0 500 3 / 3

Bitmap Index Scan on ixb01_orgainzation_t (cost=0.00..12.03 rows=500 width=0) (actual time=0.943..0.943 rows=500 loops=3)

  • Index Cond: ((st_cd)::text = 'OH'::text)
13. 572.160 572.160 ↑ 1.0 1 19,072 / 3

Index Scan using ixf02_pmtrqstpkguid on pmtrqst_t (cost=0.42..1.47 rows=1 width=213) (actual time=0.083..0.090 rows=1 loops=19,072)

  • Index Cond: (pmtrqst_pkg_uid = pmtrqstpkg_t.pmtrqst_pkg_uid)
  • Filter: (((rqst_typ_cd)::text = 'CE'::text) AND ((((pmtrqstpkg_t.fscl_stus_cd)::text = ANY ('{OBLG,NEW,APRV}'::text[])) AND (fscl_stus_cd IS NULL)) OR (((pmtrqstpkg_t.fscl_stus_cd)::text = 'DEND'::text) AND (fscl_stus_cd IS NULL) AND (xmitd_ts < pmtrqstpkg_t.stus_chg_ts)) OR (xmitd_ts IS NULL) OR (((fscl_stus_cd)::text = 'REJT'::text) AND (xmitd_ts IS NULL)) OR (((fscl_stus_cd)::text = 'REJT'::text) AND (xmitd_ts < stus_chg_ts))))
  • Rows Removed by Filter: 1
14. 17.517 17.517 ↑ 1.0 1 13,138 / 3

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..2.48 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=13,138)

  • Index Cond: (dcps_case_uid = pmtrqst_t.dcps_case_uid)
15. 367.864 367.864 ↓ 0.0 0 13,138 / 3

Index Scan using ix_cccntct_t_dcps_case_uid on cccntct_t (cost=0.42..0.50 rows=1 width=29) (actual time=0.083..0.084 rows=0 loops=13,138)

  • Index Cond: (dcpscase_t.dcps_case_uid = dcps_case_uid)
  • Filter: ((cntct_dactvd_dt IS NULL) AND ((cntct_typ_cd)::text = '3'::text))
  • Rows Removed by Filter: 1
16. 377.855 377.855 ↑ 1.0 1 13,181 / 3

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.42..0.50 rows=1 width=58) (actual time=0.078..0.086 rows=1 loops=13,181)

  • Index Cond: (dcpscase_t.dcps_case_uid = dcps_case_uid)
  • Filter: ((addr_subtyp)::text = 'M'::text)
  • Rows Removed by Filter: 1
17.          

SubPlan (for Gather)

18. 61.773 1,397.186 ↑ 1.0 1 13,181

Nested Loop Left Join (cost=0.42..9.71 rows=1 width=32) (actual time=0.090..0.106 rows=1 loops=13,181)

  • Join Filter: ((caseclm_t.clm_typ)::text = (t2clmcd_t.cd)::text)
  • Rows Removed by Join Filter: 11
19. 1,318.100 1,318.100 ↑ 1.0 1 13,181

Index Scan using ixf01_caseclm on caseclm_t (cost=0.42..8.44 rows=1 width=3) (actual time=0.086..0.100 rows=1 loops=13,181)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
20. 17.313 17.313 ↑ 1.5 8 17,313

Seq Scan on t2clmcd_t (cost=0.00..1.12 rows=12 width=4) (actual time=0.001..0.001 rows=8 loops=17,313)

21. 13.181 263.620 ↑ 1.0 1 13,181

Aggregate (cost=8.54..8.55 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=13,181)

22. 250.439 250.439 ↑ 1.0 1 13,181

Index Scan using ceappthist_t_ce_appt_uid_idx on ceappthist_t (cost=0.42..8.54 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=13,181)

  • Index Cond: (ce_appt_uid = ceappt_t.ce_appt_uid)
  • Filter: ((appt_stus_cd)::text = (ceappt_t.appt_stus_cd)::text)
  • Rows Removed by Filter: 1
23. 13.181 158.172 ↑ 1.0 1 13,181

Limit (cost=1.14..17.63 rows=1 width=6) (actual time=0.011..0.012 rows=1 loops=13,181)

24. 2.079 144.991 ↑ 1.0 1 13,181

Nested Loop (cost=1.14..17.63 rows=1 width=6) (actual time=0.011..0.011 rows=1 loops=13,181)

25. 13.181 105.448 ↑ 1.0 1 13,181

Nested Loop (cost=0.85..17.24 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=13,181)

  • Join Filter: (stcatlgprod_t.prod_uid = prodrlshp_t.rltd_prod_uid)
26. 0.000 52.724 ↑ 1.0 1 13,181

Nested Loop (cost=0.57..16.60 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=13,181)

27. 26.362 26.362 ↑ 1.0 1 13,181

Index Scan using catalog_product_uid_pkey on stcatlgprod_t (cost=0.29..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=13,181)

  • Index Cond: (st_catlg_prod_uid = pmtrqst_t.st_catlg_prod_uid)
28. 26.362 26.362 ↑ 1.0 1 13,181

Index Only Scan using prod_uid_pkey on product_t (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=13,181)

  • Index Cond: (prod_uid = stcatlgprod_t.prod_uid)
  • Heap Fetches: 13181
29. 39.543 39.543 ↑ 5.0 1 13,181

Index Scan using prodrlshp_t_rltd_prod_uid_idx on prodrlshp_t (cost=0.29..0.57 rows=5 width=8) (actual time=0.002..0.003 rows=1 loops=13,181)

  • Index Cond: (rltd_prod_uid = product_t.prod_uid)
  • Filter: (((eff_endt IS NULL) OR (eff_endt > '2020-02-03'::date)) AND (eff_stdt <= '2020-02-03'::date) AND ((prod_rlshp_typ_cd)::text = 'M'::text))
  • Rows Removed by Filter: 0
30. 37.464 37.464 ↑ 1.0 1 18,732

Index Scan using prod_uid_pkey on product_t product_t_1 (cost=0.28..0.39 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=18,732)

  • Index Cond: (prod_uid = prodrlshp_t.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 0
31. 13.181 171.353 ↑ 1.0 1 13,181

Limit (cost=1.43..17.94 rows=1 width=6) (actual time=0.012..0.013 rows=1 loops=13,181)

32. 7.630 158.172 ↑ 1.0 1 13,181

Nested Loop (cost=1.43..17.94 rows=1 width=6) (actual time=0.012..0.012 rows=1 loops=13,181)

33. 13.181 131.810 ↑ 1.0 1 13,181

Nested Loop (cost=1.15..17.55 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=13,181)

34. 0.000 92.267 ↑ 1.0 1 13,181

Nested Loop (cost=0.86..16.95 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=13,181)

35. 13.181 79.086 ↑ 1.0 1 13,181

Nested Loop (cost=0.58..16.61 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=13,181)

36. 52.724 52.724 ↑ 1.0 1 13,181

Index Scan using ceapptitem_pkey on ceapptitem_t (cost=0.29..8.31 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=13,181)

  • Index Cond: (ce_appt_itm_uid = pmtrqst_t.pmtrqst_li_id)
37. 13.181 13.181 ↑ 1.0 1 13,181

Index Scan using catalog_product_uid_pkey on stcatlgprod_t stcatlgprod_t_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=13,181)

  • Index Cond: (st_catlg_prod_uid = ceapptitem_t.st_catlg_prod_uid)
38. 13.181 13.181 ↑ 1.0 1 13,181

Index Only Scan using prod_uid_pkey on product_t product_t_2 (cost=0.28..0.34 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=13,181)

  • Index Cond: (prod_uid = stcatlgprod_t_1.prod_uid)
  • Heap Fetches: 13181
39. 26.362 26.362 ↑ 5.0 1 13,181

Index Scan using prodrlshp_t_rltd_prod_uid_idx on prodrlshp_t prodrlshp_t_1 (cost=0.29..0.54 rows=5 width=8) (actual time=0.002..0.002 rows=1 loops=13,181)

  • Index Cond: (rltd_prod_uid = stcatlgprod_t_1.prod_uid)
  • Filter: (((eff_endt IS NULL) OR (eff_endt > '2020-02-03'::date)) AND (eff_stdt <= '2020-02-03'::date) AND ((prod_rlshp_typ_cd)::text = 'M'::text))
  • Rows Removed by Filter: 0
40. 18.732 18.732 ↑ 1.0 1 18,732

Index Scan using prod_uid_pkey on product_t product_t_3 (cost=0.28..0.39 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=18,732)

  • Index Cond: (prod_uid = prodrlshp_t_1.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 0
41. 13.181 5,035.142 ↑ 1.0 1 13,181

Aggregate (cost=19.20..19.21 rows=1 width=8) (actual time=0.382..0.382 rows=1 loops=13,181)

42. 5.405 5,021.961 ↑ 1.0 1 13,181

Nested Loop (cost=0.85..19.19 rows=1 width=8) (actual time=0.178..0.381 rows=1 loops=13,181)

43. 4,112.472 4,112.472 ↑ 1.0 1 13,181

Index Scan using ixf03_rqstuid on corrtrkg_t (cost=0.43..10.74 rows=1 width=4) (actual time=0.137..0.312 rows=1 loops=13,181)

  • Index Cond: (rqst_uid = ceappt_t.ce_appt_uid)
  • Filter: (((rcpnt_typ_cd)::text = 'M'::text) AND ((rqst_typ_cd)::text = 'CE'::text))
  • Rows Removed by Filter: 4
44. 904.084 904.084 ↑ 1.0 1 19,654

Index Scan using ixf01_barcdtrkg_t_corrtrkg on barcdtrkg_t (cost=0.43..8.45 rows=1 width=12) (actual time=0.046..0.046 rows=1 loops=19,654)

  • Index Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
Planning time : 94.810 ms
Execution time : 7,116.343 ms