explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Es0W

Settings
# exclusive inclusive rows x rows loops node
1. 192.754 462,673.200 ↓ 13,517.0 13,517 1

Nested Loop (cost=67.96..27,965.00 rows=1 width=1,576) (actual time=92.373..462,673.200 rows=13,517 loops=1)

2. 35.144 9,985.354 ↓ 13,517.0 13,517 1

Nested Loop Left Join (cost=67.66..9,021.88 rows=1 width=1,380) (actual time=19.669..9,985.354 rows=13,517 loops=1)

3. 26.534 8,557.959 ↓ 13,517.0 13,517 1

Nested Loop Left Join (cost=67.24..8,976.80 rows=1 width=1,130) (actual time=18.510..8,557.959 rows=13,517 loops=1)

4. 66.372 7,210.973 ↓ 13,474.0 13,474 1

Nested Loop (cost=66.82..8,940.79 rows=1 width=944) (actual time=17.421..7,210.973 rows=13,474 loops=1)

5. 38.013 5,797.201 ↓ 13,474.0 13,474 1

Nested Loop (cost=66.40..8,932.49 rows=1 width=730) (actual time=16.313..5,797.201 rows=13,474 loops=1)

6. 110.191 5,660.272 ↓ 49,458.0 49,458 1

Nested Loop (cost=66.12..8,931.99 rows=1 width=722) (actual time=15.947..5,660.272 rows=49,458 loops=1)

7. 3,286.268 3,298.161 ↓ 2,010.6 112,596 1

Bitmap Heap Scan on pmtrqst_t (cost=65.69..8,457.60 rows=56 width=556) (actual time=14.432..3,298.161 rows=112,596 loops=1)

  • Recheck Cond: ((rqst_typ_cd)::text = 'CE'::text)
  • Filter: ((fscl_stus_cd IS NULL) OR (fscl_stus_cd IS NULL) 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: 539
  • Heap Blocks: exact=15420
8. 11.893 11.893 ↓ 33.6 113,135 1

Bitmap Index Scan on ixf04_rqsttypcd (cost=0.00..65.68 rows=3,367 width=0) (actual time=11.893..11.893 rows=113,135 loops=1)

  • Index Cond: ((rqst_typ_cd)::text = 'CE'::text)
9. 2,251.920 2,251.920 ↓ 0.0 0 112,596

Index Scan using pmtrqstpkg_t_pkey on pmtrqstpkg_t (cost=0.42..8.46 rows=1 width=178) (actual time=0.020..0.020 rows=0 loops=112,596)

  • Index Cond: (pmtrqst_pkg_uid = pmtrqst_t.pmtrqst_pkg_uid)
  • Filter: (((rqst_typ_cd)::text = 'CE'::text) AND ((((fscl_stus_cd)::text= ANY ('{OBLG,NEW,APRV}'::text[])) AND (pmtrqst_t.fscl_stus_cd IS NULL)) OR (((fscl_stus_cd)::text = 'DEND'::text) AND (pmtrqst_t.fscl_stus_cd IS NULL) AND (pmtrqst_t.xmitd_ts < stus_chg_ts)) OR (pmtrqst_t.xmitd_ts IS NULL) OR (((pmtrqst_t.fscl_stus_cd)::text = 'REJT'::text) AND (pmtrqst_t.xmitd_ts IS NULL)) OR (((pmtrqst_t.fscl_stus_cd)::text = 'REJT'::text) AND (pmtrqst_t.xmitd_ts < pmtrqst_t.stus_chg_ts))))
  • Rows Removed by Filter: 1
10. 98.916 98.916 ↓ 0.0 0 49,458

Index Scan using ixpk_organization_t on organization_t (cost=0.28..0.49 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=49,458)

  • Index Cond: (org_uid = pmtrqstpkg_t.initg_org_uid)
  • Filter: ((st_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 1
11. 1,347.400 1,347.400 ↑ 1.0 1 13,474

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..8.29 rows=1 width=214) (actual time=0.100..0.100 rows=1 loops=13,474)

  • Index Cond: (dcps_case_uid = pmtrqst_t.dcps_case_uid)
12. 1,320.452 1,320.452 ↓ 0.0 0 13,474

Index Scan using ix_cccntct_t_dcps_case_uid on cccntct_t (cost=0.42..36.01 rows=1 width=190) (actual time=0.096..0.098 rows=0 loops=13,474)

  • 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
13. 1,392.251 1,392.251 ↑ 11.0 1 13,517

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.42..44.97 rows=11 width=254) (actual time=0.093..0.103 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
14. 189.238 189.238 ↑ 1.0 1 13,517

Index Scan using ceappt_pkey on ceappt_t (cost=0.29..2.44 rows=1 width=40) (actual time=0.014..0.014 rows=1 loops=13,517)

  • Index Cond: (ce_appt_uid = pmtrqstpkg_t.rqst_uid)
15.          

SubPlan (for Nested Loop)

16. 67.571 1,473.353 ↑ 1,398.0 1 13,517

Hash Left Join (cost=28.53..3,794.10 rows=1,398 width=32) (actual time=0.095..0.109 rows=1 loops=13,517)

  • Hash Cond: ((caseclm_t.clm_typ)::text = (t2clmcd_t.cd)::text)
17. 1,257.081 1,405.768 ↑ 1,398.0 1 13,517

Bitmap Heap Scan on caseclm_t (cost=27.26..3,789.08 rows=1,398 width=20) (actual time=0.090..0.104 rows=1 loops=13,517)

  • Recheck Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Heap Blocks: exact=15922
18. 148.687 148.687 ↑ 1,398.0 1 13,517

Bitmap Index Scan on ixf01_caseclm (cost=0.00..26.91 rows=1,398 width=0) (actual time=0.011..0.011 rows=1 loops=13,517)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
19. 0.005 0.014 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=20) (actual time=0.014..0.014 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.009 0.009 ↑ 1.0 12 1

Seq Scan on t2clmcd_t (cost=0.00..1.12 rows=12 width=20) (actual time=0.007..0.009 rows=12 loops=1)

21. 54.068 227,126.151 ↑ 1.0 1 13,517

Aggregate (cost=4,470.98..4,470.99 rows=1 width=8) (actual time=16.803..16.803 rows=1 loops=13,517)

22. 227,072.083 227,072.083 ↑ 4.0 1 13,517

Seq Scan on ceappthist_t (cost=0.00..4,470.97 rows=4 width=8) (actual time=12.126..16.799 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
23. 27.034 6,123.201 ↑ 1.0 1 13,517

Limit (cost=5.47..105.32 rows=1 width=54) (actual time=0.451..0.453 rows=1 loops=13,517)

24. 36.863 6,096.167 ↑ 1.0 1 13,517

Nested Loop (cost=5.47..105.32 rows=1 width=54) (actual time=0.450..0.451 rows=1 loops=13,517)

25. 378.476 6,001.548 ↑ 1.0 1 13,517

Nested Loop (cost=5.19..96.61 rows=1 width=4) (actual time=0.423..0.444 rows=1 loops=13,517)

  • Join Filter: (stcatlgprod_t.prod_uid = prodrlshp_t.rltd_prod_uid)
  • Rows Removed by Join Filter: 381
26. 40.551 175.721 ↑ 1.0 1 13,517

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

27. 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)
28. 67.585 67.585 ↑ 1.0 1 13,517

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

  • Index Cond: (prod_uid = stcatlgprod_t.prod_uid)
  • Heap Fetches: 13517
29. 959.707 5,447.351 ↓ 76.4 382 13,517

Bitmap Heap Scan on prodrlshp_t (cost=4.62..79.94 rows=5 width=8) (actual time=0.344..0.403 rows=382 loops=13,517)

  • Recheck Cond: ((prod_rlshp_typ_cd)::text = 'M'::text)
  • Filter: (((eff_endt IS NULL) OR (eff_endt > '2020-02-03'::date)) AND (eff_stdt <='2020-02-03'::date))
  • Rows Removed by Filter: 22
  • Heap Blocks: exact=91373
30. 4,487.644 4,487.644 ↓ 175.2 7,885 13,517

Bitmap Index Scan on ixb01_prod_rlshp_typ_cd (cost=0.00..4.62 rows=45 width=0) (actual time=0.332..0.332 rows=7,885 loops=13,517)

  • Index Cond: ((prod_rlshp_typ_cd)::text = 'M'::text)
31. 57.756 57.756 ↑ 1.0 1 19,252

Index Scan using prod_uid_pkey on product_t product_t_1 (cost=0.28..8.30 rows=1 width=58) (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
32. 27.034 5,690.657 ↑ 1.0 1 13,517

Limit (cost=5.89..105.79 rows=1 width=54) (actual time=0.420..0.421 rows=1 loops=13,517)

33. 34.816 5,663.623 ↑ 1.0 1 13,517

Nested Loop (cost=5.89..105.79 rows=1 width=54) (actual time=0.419..0.419 rows=1 loops=13,517)

34. 337.925 5,609.555 ↑ 1.0 1 13,517

Nested Loop (cost=5.61..97.08 rows=1 width=4) (actual time=0.396..0.415 rows=1 loops=13,517)

  • Join Filter: (stcatlgprod_t_1.prod_uid = prodrlshp_t_1.rltd_prod_uid)
  • Rows Removed by Join Filter: 381
35. 27.034 243.306 ↑ 1.0 1 13,517

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

36. 40.551 202.755 ↑ 1.0 1 13,517

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

37. 148.687 148.687 ↑ 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.011..0.011 rows=1 loops=13,517)

  • Index Cond: (ce_appt_itm_uid = pmtrqst_t.pmtrqst_li_id)
38. 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)
39. 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
40. 824.537 5,028.324 ↓ 76.4 382 13,517

Bitmap Heap Scan on prodrlshp_t prodrlshp_t_1 (cost=4.62..79.94 rows=5 width=8) (actual time=0.319..0.372 rows=382 loops=13,517)

  • Recheck Cond: ((prod_rlshp_typ_cd)::text = 'M'::text)
  • Filter: (((eff_endt IS NULL) OR (eff_endt > '2020-02-03'::date)) AND (eff_stdt <='2020-02-03'::date))
  • Rows Removed by Filter: 22
  • Heap Blocks: exact=91373
41. 4,203.787 4,203.787 ↓ 175.2 7,885 13,517

Bitmap Index Scan on ixb01_prod_rlshp_typ_cd (cost=0.00..4.62 rows=45 width=0) (actual time=0.311..0.311 rows=7,885 loops=13,517)

  • Index Cond: ((prod_rlshp_typ_cd)::text = 'M'::text)
42. 19.252 19.252 ↑ 1.0 1 19,252

Index Scan using prod_uid_pkey on product_t product_t_3 (cost=0.28..8.30 rows=1 width=58) (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
43. 27.034 211,892.492 ↑ 1.0 1 13,517

Aggregate (cost=10,464.47..10,464.48 rows=1 width=8) (actual time=15.676..15.676 rows=1 loops=13,517)

44. 99.942 211,865.458 ↑ 1.0 1 13,517

Nested Loop (cost=398.29..10,464.47 rows=1 width=8) (actual time=15.499..15.674 rows=1 loops=13,517)

45. 7,704.690 210,297.486 ↑ 1.0 1 13,517

Bitmap Heap Scan on corrtrkg_t (cost=295.77..452.71 rows=1 width=4) (actual time=15.412..15.558 rows=1 loops=13,517)

  • Recheck Cond: ((rqst_uid = ceappt_t.ce_appt_uid) AND ((rqst_typ_cd)::text = 'CE'::text))
  • Filter: ((rcpnt_typ_cd)::text = 'M'::text)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=35469
  • -> BitmapAnd (cost=295.77..295.77 rows=40 width=0) (actual time=15.258..15.258 rows=0loops=13517)
46. 121.653 121.653 ↑ 1,582.2 5 13,517

Bitmap Index Scan on ixf03_rqstuid (cost=0.00..147.76 rows=7,911 width=0) (actual time=0.009..0.009 rows=5 loops=13,517)

  • Index Cond: (rqst_uid = ceappt_t.ce_appt_uid)
47. 202,471.143 202,471.143 ↓ 26.8 211,777 13,517

Bitmap Index Scan on ixf01_rqsttypcd (cost=0.00..147.76 rows=7,911 width=0) (actual time=14.979..14.979 rows=211,777 loops=13,517)

  • Index Cond: ((rqst_typ_cd)::text = 'CE'::text)
48. 884.840 1,468.030 ↑ 5,431.0 1 20,110

Bitmap Heap Scan on barcdtrkg_t (cost=102.52..9,957.45 rows=5,431 width=12) (actual time=0.073..0.073 rows=1 loops=20,110)

  • Recheck Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
  • Heap Blocks: exact=13514
49. 583.190 583.190 ↑ 5,431.0 1 20,110

Bitmap Index Scan on ixf01_barcdtrkg_t_corrtrkg (cost=0.00..101.16 rows=5,431 width=0) (actual time=0.029..0.029 rows=1 loops=20,110)

  • Index Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
Planning time : 61.313 ms
Execution time : 462,678.216 ms