explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1fQb

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 223,339.715 ↓ 130.0 13,517 1

Gather (cost=1,155.12..540,785.63 rows=104 width=550) (actual time=36.114..223,339.715 rows=13,517 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 3.044 309.350 ↓ 104.8 4,506 3 / 3

Nested Loop Left Join (cost=155.12..14,226.71 rows=43 width=394) (actual time=3.605..309.350 rows=4,506 loops=3)

3. 21.092 288.283 ↓ 104.8 4,506 3 / 3

Nested Loop Left Join (cost=154.69..14,199.23 rows=43 width=340) (actual time=3.592..288.283 rows=4,506 loops=3)

  • -> Index Scan using ix_cccntct_t_dcps_case_uid on cccntct_t (cost=0.42..0.63 rows=1 width=29)(actual time=0.004..0.004 rows=0 loops=13474)
4. 10.231 267.191 ↓ 104.4 4,491 3 / 3

Nested Loop (cost=154.27..14,171.84 rows=43 width=315) (actual time=3.578..267.191 rows=4,491 loops=3)

  • 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
5. 5.800 243.486 ↓ 104.4 4,491 3 / 3

Nested Loop (cost=153.85..14,130.99 rows=43 width=279) (actual time=3.563..243.486 rows=4,491 loops=3)

6. 5.490 205.228 ↓ 14.8 6,492 3 / 3

Hash Join (cost=153.43..13,483.91 rows=439 width=79) (actual time=2.293..205.228 rows=6,492 loops=3)

  • Hash Cond: (pmtrqstpkg_t.initg_org_uid = organization_t.org_uid)
7. 22.402 198.688 ↓ 5.3 24,789 3 / 3

Merge Join (cost=0.77..13,318.99 rows=4,667 width=80) (actual time=1.130..198.688 rows=24,789 loops=3)

  • Merge Cond: (pmtrqstpkg_t.rqst_uid = ceappt_t.ce_appt_uid)
8. 138.473 138.473 ↑ 1.3 24,789 3 / 3

Parallel Index Scan using ixn01_pmtrqstpkg_t on pmtrqstpkg_t (cost=0.42..41,012.50 rows=31,002 width=55) (actual time=1.029..138.473 rows=24,789 loops=3)

  • Filter: ((rqst_typ_cd)::text = 'CE'::text)
  • Rows Removed by Filter: 186768
9. 37.813 37.813 ↑ 2.8 26,557 3 / 3

Index Scan using ceappt_pkey on ceappt_t (cost=0.29..3,606.76 rows=74,395 width=25) (actual time=0.018..37.813 rows=26,557 loops=3)

10. 0.103 1.050 ↑ 1.0 500 3 / 3

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

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

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

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

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

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

Index Scan using ixf02_pmtrqstpkguid on pmtrqst_t (cost=0.42..1.46 rows=1 width=212) (actual time=0.005..0.005 rows=1 loops=19,475)

  • 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. 13.474 13.474 ↑ 1.0 1 13,474 / 3

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..0.95 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=13,474)

  • Index Cond: (dcps_case_uid = pmtrqst_t.dcps_case_uid)
15. 18.023 18.023 ↑ 1.0 1 13,517 / 3

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.42..0.63 rows=1 width=58) (actual time=0.003..0.004 rows=1 loops=13,517)

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

SubPlan (for Gather)

17. 63.368 135.170 ↑ 1.0 1 13,517

Nested Loop Left Join (cost=0.42..9.71 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=13,517)

  • Join Filter: ((caseclm_t.clm_typ)::text = (t2clmcd_t.cd)::text)
  • Rows Removed by Join Filter: 11
18. 54.068 54.068 ↑ 1.0 1 13,517

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

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
19. 17.734 17.734 ↑ 1.3 9 17,734

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

20. 54.068 218,083.278 ↑ 1.0 1 13,517

Aggregate (cost=4,470.97..4,470.98 rows=1 width=8) (actual time=16.133..16.134 rows=1 loops=13,517)

21. 218,029.210 218,029.210 ↑ 1.0 1 13,517

Seq Scan on ceappthist_t (cost=0.00..4,470.97 rows=1 width=8) (actual time=11.583..16.130 rows=1 loops=13,517)

  • Filter: ((ce_appt_uid = ceappt_t.ce_appt_uid) AND ((appt_stus_cd)::text = (ceappt_t.appt_stus_cd)::text))
  • Rows Removed by Filter: 144397
22. 27.034 2,041.067 ↑ 1.0 1 13,517

Limit (cost=8.88..270.17 rows=1 width=6) (actual time=0.150..0.151 rows=1 loops=13,517)

23. 27.034 2,014.033 ↑ 1.0 1 13,517

Nested Loop (cost=8.88..270.17 rows=1 width=6) (actual time=0.149..0.149 rows=1 loops=13,517)

  • Join Filter: (stcatlgprod_t.prod_uid = product_t.prod_uid)
24. 50.380 1,959.965 ↑ 1.0 1 13,517

Nested Loop (cost=8.60..269.76 rows=1 width=14) (actual time=0.145..0.145 rows=1 loops=13,517)

25. 391.993 1,851.829 ↑ 5.0 1 13,517

Hash Join (cost=8.31..267.79 rows=5 width=12) (actual time=0.114..0.137 rows=1 loops=13,517)

  • Hash Cond: (prodrlshp_t.rltd_prod_uid = stcatlgprod_t.prod_uid)
26. 1,365.217 1,365.217 ↑ 14.7 382 13,517

Seq Scan on prodrlshp_t (cost=0.00..244.73 rows=5,597 width=8) (actual time=0.035..0.101 rows=382 loops=13,517)

  • 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: 379
27. 27.034 94.619 ↑ 1.0 1 13,517

Hash (cost=8.30..8.30 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=13,517)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 67.585 67.585 ↑ 1.0 1 13,517

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

  • Index Cond: (st_catlg_prod_uid = pmtrqst_t.st_catlg_prod_uid)
29. 57.756 57.756 ↑ 1.0 1 19,252

Index Scan using prod_uid_pkey on product_t product_t_1 (cost=0.28..0.39 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=19,252)

  • Index Cond: (prod_uid = prodrlshp_t.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 0
30. 27.034 27.034 ↑ 1.0 1 13,517

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

  • Index Cond: (prod_uid = prodrlshp_t.rltd_prod_uid)
  • Heap Fetches: 13517
31. 27.034 1,865.346 ↑ 1.0 1 13,517

Limit (cost=17.37..283.25 rows=1 width=6) (actual time=0.137..0.138 rows=1 loops=13,517)

32. 48.333 1,838.312 ↑ 1.0 1 13,517

Nested Loop (cost=17.37..283.25 rows=1 width=6) (actual time=0.136..0.136 rows=1 loops=13,517)

33. 364.959 1,770.727 ↑ 1.0 1 13,517

Hash Join (cost=17.09..282.86 rows=1 width=4) (actual time=0.109..0.131 rows=1 loops=13,517)

  • Hash Cond: (prodrlshp_t_1.rltd_prod_uid = stcatlgprod_t_1.prod_uid)
34. 1,257.081 1,257.081 ↑ 14.7 382 13,517

Seq Scan on prodrlshp_t prodrlshp_t_1 (cost=0.00..244.73 rows=5,597 width=8) (actual time=0.032..0.093 rows=382 loops=13,517)

  • 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: 379
35. 13.517 148.687 ↑ 1.0 1 13,517

Hash (cost=17.08..17.08 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=13,517)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 27.034 135.170 ↑ 1.0 1 13,517

Nested Loop (cost=0.98..17.08 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=13,517)

37. 27.034 94.619 ↑ 1.0 1 13,517

Nested Loop (cost=0.70..16.74 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=13,517)

38. 54.068 54.068 ↑ 1.0 1 13,517

Index Scan using ceapptitem_pkey on ceapptitem_t (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=13,517)

  • Index Cond: (ce_appt_itm_uid = pmtrqst_t.pmtrqst_li_id)
39. 13.517 13.517 ↑ 1.0 1 13,517

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

  • Index Cond: (st_catlg_prod_uid = ceapptitem_t.st_catlg_prod_uid)
40. 13.517 13.517 ↑ 1.0 1 13,517

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

  • Index Cond: (prod_uid = stcatlgprod_t_1.prod_uid)
  • Heap Fetches: 13517
41. 19.252 19.252 ↑ 1.0 1 19,252

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=19,252)

  • Index Cond: (prod_uid = prodrlshp_t_1.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 0
42. 13.517 1,081.360 ↑ 1.0 1 13,517

Aggregate (cost=19.23..19.24 rows=1 width=8) (actual time=0.080..0.080 rows=1 loops=13,517)

43. 20.772 1,067.843 ↑ 1.0 1 13,517

Nested Loop (cost=0.85..19.23 rows=1 width=8) (actual time=0.071..0.079 rows=1 loops=13,517)

44. 986.741 986.741 ↑ 1.0 1 13,517

Index Scan using ixf03_rqstuid on corrtrkg_t (cost=0.43..10.77 rows=1 width=4) (actual time=0.066..0.073 rows=1 loops=13,517)

  • 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
45. 60.330 60.330 ↑ 1.0 1 20,110

Index Scan using ixf01_barcdtrkg_t_corrtrkg on barcdtrkg_t (cost=0.43..8.45 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=20,110)

  • Index Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
Planning time : 25.046 ms
Execution time : 223,342.959 ms