explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v3Pm

Settings
# exclusive inclusive rows x rows loops node
1. 168.870 369,400.313 ↓ 12,172.0 12,172 1

Nested Loop (cost=62.12..26,459.42 rows=1 width=1,630) (actual time=41.539..369,400.313 rows=12,172 loops=1)

2. 20.118 870.207 ↓ 12,172.0 12,172 1

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

3. 26.582 764.885 ↓ 12,172.0 12,172 1

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

4. 50.303 665.529 ↓ 12,129.0 12,129 1

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

5. 27.857 554.581 ↓ 12,129.0 12,129 1

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

6. 118.148 437.000 ↓ 44,862.0 44,862 1

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

  • -> Bitmap Heap Scan on pmtrqst_t (cost=59.85..7845.53 rows=52 width=556) (actual time=9.590..79.181rows=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. 7.662 7.662 ↓ 33.4 104,207 1

Bitmap Index Scan on ixf04_rqsttypcd (cost=0.00..59.84 rows=3,122 width=0) (actual time=7.662..7.662 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. 89.724 89.724 ↓ 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.002..0.002 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. 60.645 60.645 ↑ 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.005..0.005 rows=1 loops=12,129)

  • Index Cond: (dcps_case_uid = pmtrqst_t.dcps_case_uid)
11. 72.774 72.774 ↓ 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.006..0.006 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. 85.204 85.204 ↑ 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.005..0.007 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. 48.688 48.688 ↑ 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.004..0.004 rows=1 loops=12,172)

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

SubPlan (for Nested Loop)

15. 48.679 133.892 ↑ 1,295.0 1 12,172

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

  • Hash Cond: ((caseclm_t.clm_typ)::text = (t2clmcd_t.cd)::text)
16. 36.516 85.204 ↑ 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.006..0.007 rows=1 loops=12,172)

  • Recheck Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Heap Blocks: exact=14343
17. 48.688 48.688 ↑ 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.004..0.004 rows=1 loops=12,172)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
18. 0.005 0.009 ↑ 1.0 12 1

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

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

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

20. 48.688 188,994.644 ↑ 1.0 1 12,172

Aggregate (cost=4,109.29..4,109.30 rows=1 width=8) (actual time=15.527..15.527 rows=1 loops=12,172)

21. 188,945.956 188,945.956 ↑ 3.0 1 12,172

Seq Scan on ceappthist_t (cost=0.00..4,109.28 rows=3 width=8) (actual time=11.164..15.523 rows=1 loops=12,172)

  • Filter: ((ce_appt_uid = ceappt_t.ce_appt_uid) AND ((appt_stus_cd)::text = (ceappt_t.appt_stus_cd)::text))
  • Rows Removed by Filter: 132751
22. 24.344 1,813.628 ↑ 1.0 1 12,172

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

23. 20.712 1,789.284 ↑ 1.0 1 12,172

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

24. 292.128 1,716.252 ↑ 1.0 1 12,172

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

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

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

26. 60.860 60.860 ↑ 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.005..0.005 rows=1 loops=12,172)

  • Index Cond: (st_catlg_prod_uid = pmtrqst_t.st_catlg_prod_uid)
27. 48.688 48.688 ↑ 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.004..0.004 rows=1 loops=12,172)

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

Seq Scan on prodrlshp_t (cost=0.00..243.57 rows=5 width=8) (actual time=0.037..0.105 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. 52.320 52.320 ↑ 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.003..0.003 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,521.500 ↑ 1.0 1 12,172

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

31. 31.248 1,509.328 ↑ 1.0 1 12,172

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

32. 267.784 1,460.640 ↑ 1.0 1 12,172

Nested Loop (cost=0.57..260.24 rows=1 width=4) (actual time=0.099..0.120 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 48.688 ↑ 1.0 1 12,172

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

34. 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=4) (actual time=0.001..0.001 rows=1 loops=12,172)

  • Index Cond: (st_catlg_prod_uid = pmtrqst_t.st_catlg_prod_uid)
35. 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..8.30 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
36. 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
37. 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
38. 24.344 1,594.532 ↑ 1.0 1 12,172

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

39. 19.076 1,570.188 ↑ 1.0 1 12,172

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

40. 255.612 1,533.672 ↑ 1.0 1 12,172

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

  • Join Filter: (stcatlgprod_t_2.prod_uid = prodrlshp_t_2.rltd_prod_uid)
  • Rows Removed by Join Filter: 384
41. 24.344 133.892 ↑ 1.0 1 12,172

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

42. 36.516 97.376 ↑ 1.0 1 12,172

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

43. 48.688 48.688 ↑ 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.004..0.004 rows=1 loops=12,172)

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

Index Scan using catalog_product_uid_pkey on stcatlgprod_t stcatlgprod_t_2 (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)
45. 12.172 12.172 ↑ 1.0 1 12,172

Index Only Scan using prod_uid_pkey on product_t product_t_4 (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_2.prod_uid)
  • Heap Fetches: 12172
46. 1,144.168 1,144.168 ↓ 77.2 386 12,172

Seq Scan on prodrlshp_t prodrlshp_t_2 (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
47. 17.440 17.440 ↑ 1.0 1 17,440

Index Scan using prod_uid_pkey on product_t product_t_5 (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_2.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
  • Rows Removed by Filter: 0
48. 24.344 174,254.352 ↑ 1.0 1 12,172

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

49. 86.896 174,230.008 ↑ 1.0 1 12,172

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

50. 121.720 174,035.256 ↑ 1.0 1 12,172

Bitmap Heap Scan on corrtrkg_t (cost=270.32..411.60 rows=1 width=4) (actual time=14.296..14.298 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
51. 3,201.236 173,913.536 ↓ 0.0 0 12,172

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

52. 73.032 73.032 ↑ 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.006..0.006 rows=5 loops=12,172)

  • Index Cond: (rqst_uid = ceappt_t.ce_appt_uid)
53. 170,639.268 170,639.268 ↓ 26.6 193,447 12,172

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

  • Index Cond: ((rqst_typ_cd)::text = 'CE'::text)
54. 17.976 107.856 ↑ 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.006..0.006 rows=1 loops=17,976)

  • Recheck Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
  • Heap Blocks: exact=12169
55. 89.880 89.880 ↑ 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.005..0.005 rows=1 loops=17,976)

  • Index Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
Planning time : 8.871 ms
Execution time : 369,404.072 ms