explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4b9b

Settings
# exclusive inclusive rows x rows loops node
1. 82.118 149,258.863 ↓ 12,172.0 12,172 1

Nested Loop (cost=62.12..22,089.71 rows=1 width=1,576) (actual time=23.799..149,258.863 rows=12,172 loops=1)

2. 15.850 702.689 ↓ 12,172.0 12,172 1

Nested Loop Left Join (cost=61.83..8,370.10 rows=1 width=1,380) (actual time=8.551..702.689 rows=12,172 loops=1)

3. 13.341 625.979 ↓ 12,172.0 12,172 1

Nested Loop Left Join (cost=61.40..8,328.27 rows=1 width=1,130) (actual time=8.542..625.979 rows=12,172 loops=1)

4. 32.601 551.993 ↓ 12,129.0 12,129 1

Nested Loop (cost=60.98..8,294.83 rows=1 width=944) (actual time=8.530..551.993 rows=12,129 loops=1)

5. 42.595 470.876 ↓ 12,129.0 12,129 1

Nested Loop (cost=60.56..8,286.55 rows=1 width=730) (actual time=8.520..470.876 rows=12,129 loops=1)

6. 65.649 383.419 ↓ 44,862.0 44,862 1

Nested Loop (cost=60.28..8,286.03 rows=1 width=722) (actual time=8.513..383.419 rows=44,862 loops=1)

  • -> Bitmap Heap Scan on pmtrqst_t (cost=59.85..7845.53 rows=52 width=556) (actual time=8.497..70.040rows=103730 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: 477
  • Heap Blocks: exact=14299
7. 6.580 6.580 ↓ 33.4 104,207 1

Bitmap Index Scan on ixf04_rqsttypcd (cost=0.00..59.84 rows=3,122 width=0) (actual time=6.580..6.580 rows=104,207 loops=1)

  • Index Cond: ((rqst_typ_cd)::text = 'CE'::text)
8. 311.190 311.190 ↓ 0.0 0 103,730

Index Scan using pmtrqstpkg_t_pkey on pmtrqstpkg_t (cost=0.42..8.46 rows=1 width=178) (actual time=0.003..0.003 rows=0 loops=103,730)

  • 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
9. 44.862 44.862 ↓ 0.0 0 44,862

Index Scan using ixpk_organization_t on organization_t (cost=0.28..0.50 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=44,862)

  • Index Cond: (org_uid = pmtrqstpkg_t.initg_org_uid)
  • Filter: ((st_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 1
10. 48.516 48.516 ↑ 1.0 1 12,129

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..8.28 rows=1 width=214) (actual time=0.004..0.004 rows=1 loops=12,129)

  • Index Cond: (dcps_case_uid = pmtrqst_t.dcps_case_uid)
11. 60.645 60.645 ↓ 0.0 0 12,129

Index Scan using ix_cccntct_t_dcps_case_uid on cccntct_t (cost=0.42..33.43 rows=1 width=190) (actual time=0.005..0.005 rows=0 loops=12,129)

  • 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
12. 60.860 60.860 ↑ 10.0 1 12,172

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.42..41.73 rows=10 width=254) (actual time=0.004..0.005 rows=1 loops=12,172)

  • Index Cond: (dcpscase_t.dcps_case_uid = dcps_case_uid)
  • Filter: ((addr_subtyp)::text = 'M'::text)
  • Rows Removed by Filter: 1
13. 36.516 36.516 ↑ 1.0 1 12,172

Index Scan using ceappt_pkey on ceappt_t (cost=0.29..2.42 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=12,172)

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

SubPlan (for Nested Loop)

15. 36.505 109.548 ↑ 1,295.0 1 12,172

Hash Left Join (cost=27.73..3,515.39 rows=1,295 width=32) (actual time=0.008..0.009 rows=1 loops=12,172)

  • Hash Cond: ((caseclm_t.clm_typ)::text = (t2clmcd_t.cd)::text)
16. 36.516 73.032 ↑ 1,295.0 1 12,172

Bitmap Heap Scan on caseclm_t (cost=26.46..3,510.65 rows=1,295 width=20) (actual time=0.005..0.006 rows=1 loops=12,172)

  • Recheck Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Heap Blocks: exact=14343
17. 36.516 36.516 ↑ 1,295.0 1 12,172

Bitmap Index Scan on ixf01_caseclm (cost=0.00..26.13 rows=1,295 width=0) (actual time=0.003..0.003 rows=1 loops=12,172)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
18. 0.004 0.011 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.007 0.007 ↑ 1.0 12 1

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

20. 12.172 73.032 ↑ 1.0 1 12,172

Aggregate (cost=8.54..8.55 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=12,172)

21. 60.860 60.860 ↑ 1.0 1 12,172

Index Scan using ceappthist_t_ce_appt_uid_idx on ceappthist_t (cost=0.42..8.53 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=12,172)

  • 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
22. 24.344 1,631.048 ↑ 1.0 1 12,172

Limit (cost=0.85..268.95 rows=1 width=54) (actual time=0.133..0.134 rows=1 loops=12,172)

23. 13.808 1,606.704 ↑ 1.0 1 12,172

Nested Loop (cost=0.85..268.95 rows=1 width=54) (actual time=0.132..0.132 rows=1 loops=12,172)

24. 267.784 1,558.016 ↑ 1.0 1 12,172

Nested Loop (cost=0.57..260.24 rows=1 width=4) (actual time=0.106..0.128 rows=1 loops=12,172)

  • Join Filter: (stcatlgprod_t.prod_uid = prodrlshp_t.rltd_prod_uid)
  • Rows Removed by Join Filter: 384
25. 24.344 85.204 ↑ 1.0 1 12,172

Nested Loop (cost=0.57..16.60 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=12,172)

26. 24.344 24.344 ↑ 1.0 1 12,172

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=12,172)

  • Index Cond: (st_catlg_prod_uid = pmtrqst_t.st_catlg_prod_uid)
27. 36.516 36.516 ↑ 1.0 1 12,172

Index Only Scan using prod_uid_pkey on product_t (cost=0.28..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=12,172)

  • Index Cond: (prod_uid = stcatlgprod_t.prod_uid)
  • Heap Fetches: 12172
28. 1,205.028 1,205.028 ↓ 77.2 386 12,172

Seq Scan on prodrlshp_t (cost=0.00..243.57 rows=5 width=8) (actual time=0.034..0.099 rows=386 loops=12,172)

  • 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: 380
29. 34.880 34.880 ↑ 1.0 1 17,440

Index Scan using prod_uid_pkey on product_t product_t_1 (cost=0.28..8.30 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=17,440)

  • Index Cond: (prod_uid = prodrlshp_t.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 0
30. 12.172 1,545.844 ↑ 1.0 1 12,172

Limit (cost=1.14..269.30 rows=1 width=54) (actual time=0.126..0.127 rows=1 loops=12,172)

31. 19.076 1,533.672 ↑ 1.0 1 12,172

Nested Loop (cost=1.14..269.30 rows=1 width=54) (actual time=0.126..0.126 rows=1 loops=12,172)

32. 255.612 1,497.156 ↑ 1.0 1 12,172

Nested Loop (cost=0.86..260.59 rows=1 width=4) (actual time=0.101..0.123 rows=1 loops=12,172)

  • Join Filter: (stcatlgprod_t_1.prod_uid = prodrlshp_t_1.rltd_prod_uid)
  • Rows Removed by Join Filter: 384
33. 24.344 97.376 ↑ 1.0 1 12,172

Nested Loop (cost=0.86..16.95 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=12,172)

34. 12.172 60.860 ↑ 1.0 1 12,172

Nested Loop (cost=0.58..16.61 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=12,172)

35. 36.516 36.516 ↑ 1.0 1 12,172

Index Scan using ceapptitem_pkey on ceapptitem_t (cost=0.29..8.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=12,172)

  • Index Cond: (ce_appt_itm_uid = pmtrqst_t.pmtrqst_li_id)
36. 12.172 12.172 ↑ 1.0 1 12,172

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=12,172)

  • Index Cond: (st_catlg_prod_uid = ceapptitem_t.st_catlg_prod_uid)
37. 12.172 12.172 ↑ 1.0 1 12,172

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=12,172)

  • Index Cond: (prod_uid = stcatlgprod_t_1.prod_uid)
  • Heap Fetches: 12172
38. 1,144.168 1,144.168 ↓ 77.2 386 12,172

Seq Scan on prodrlshp_t prodrlshp_t_1 (cost=0.00..243.57 rows=5 width=8) (actual time=0.032..0.094 rows=386 loops=12,172)

  • 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: 380
39. 17.440 17.440 ↑ 1.0 1 17,440

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=17,440)

  • Index Cond: (prod_uid = prodrlshp_t_1.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 0
40. 24.344 145,078.068 ↑ 1.0 1 12,172

Aggregate (cost=9,655.00..9,655.01 rows=1 width=8) (actual time=11.919..11.919 rows=1 loops=12,172)

41. 68.356 145,053.724 ↑ 1.0 1 12,172

Nested Loop (cost=365.60..9,654.99 rows=1 width=8) (actual time=11.913..11.917 rows=1 loops=12,172)

42. 85.204 144,895.488 ↑ 1.0 1 12,172

Bitmap Heap Scan on corrtrkg_t (cost=270.32..411.60 rows=1 width=4) (actual time=11.902..11.904 rows=1 loops=12,172)

  • 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=31980
43. 24.344 144,810.284 ↓ 0.0 0 12,172

BitmapAnd (cost=270.32..270.32 rows=36 width=0) (actual time=11.897..11.897 rows=0 loops=12,172)

44. 48.688 48.688 ↑ 1,456.2 5 12,172

Bitmap Index Scan on ixf03_rqstuid (cost=0.00..135.03 rows=7,281 width=0) (actual time=0.004..0.004 rows=5 loops=12,172)

  • Index Cond: (rqst_uid = ceappt_t.ce_appt_uid)
45. 144,737.252 144,737.252 ↓ 26.6 193,447 12,172

Bitmap Index Scan on ixf01_rqsttypcd (cost=0.00..135.03 rows=7,281 width=0) (actual time=11.891..11.891 rows=193,447 loops=12,172)

  • Index Cond: ((rqst_typ_cd)::text = 'CE'::text)
46. 17.976 89.880 ↑ 5,014.0 1 17,976

Bitmap Heap Scan on barcdtrkg_t (cost=95.28..9,193.25 rows=5,014 width=12) (actual time=0.005..0.005 rows=1 loops=17,976)

  • Recheck Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
  • Heap Blocks: exact=12169
47. 71.904 71.904 ↑ 5,014.0 1 17,976

Bitmap Index Scan on ixf01_barcdtrkg_t_corrtrkg (cost=0.00..94.03 rows=5,014 width=0) (actual time=0.004..0.004 rows=1 loops=17,976)

  • Index Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
Planning time : 4.284 ms
Execution time : 149,261.316 ms