explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jgxf

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 30,968.160 ↓ 122.6 1,962 1

Gather (cost=1,155.47..97,918.44 rows=16 width=558) (actual time=38.582..30,968.160 rows=1,962 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.316 204.373 ↓ 93.4 654 3 / 3

Nested Loop Left Join (cost=155.47..14,221.57 rows=7 width=394) (actual time=1.470..204.373 rows=654 loops=3)

3. 6.294 199.479 ↓ 93.4 654 3 / 3

Nested Loop Left Join (cost=155.05..14,217.10 rows=7 width=340) (actual time=1.454..199.479 rows=654 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.005..0.005 rows=0 loops=1953)
4. 2.617 193.185 ↓ 93.0 651 3 / 3

Nested Loop (cost=154.63..14,212.64 rows=7 width=315) (actual time=1.431..193.185 rows=651 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. 0.300 187.964 ↓ 93.0 651 3 / 3

Nested Loop (cost=154.21..14,198.19 rows=7 width=280) (actual time=1.414..187.964 rows=651 loops=3)

6. 3.175 182.806 ↓ 3.7 810 3 / 3

Hash Join (cost=153.78..13,635.07 rows=218 width=79) (actual time=1.395..182.806 rows=810 loops=3)

  • Hash Cond: (pmtrqstpkg_t.initg_org_uid = organization_t.org_uid)
7. 14.144 178.590 ↓ 5.2 11,977 3 / 3

Merge Join (cost=1.13..13,476.33 rows=2,317 width=80) (actual time=0.158..178.590 rows=11,977 loops=3)

  • Merge Cond: (pmtrqstpkg_t.rqst_uid = ceappt_t.ce_appt_uid)
8. 146.551 146.551 ↑ 1.2 11,977 3 / 3

Parallel Index Scan using ixn01_pmtrqstpkg_t on pmtrqstpkg_t (cost=0.42..41,323.56 rows=14,935 width=55) (actual time=0.014..146.551 rows=11,977 loops=3)

  • Filter: ((rqst_typ_cd)::text = 'TRA'::text)
  • Rows Removed by Filter: 203308
9. 17.895 17.895 ↑ 2.4 31,773 3 / 3

Index Scan using ceappt_pkey on ceappt_t (cost=0.29..3,694.22 rows=76,144 width=25) (actual time=0.017..17.895 rows=31,773 loops=3)

10. 0.119 1.041 ↑ 1.0 500 3 / 3

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

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

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

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

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

  • Index Cond: ((st_cd)::text = 'OH'::text)
13. 4.858 4.858 ↑ 1.0 1 2,429 / 3

Index Scan using ixf02_pmtrqstpkguid on pmtrqst_t (cost=0.42..2.57 rows=1 width=205) (actual time=0.005..0.006 rows=1 loops=2,429)

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

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..2.06 rows=1 width=35) (actual time=0.004..0.004 rows=1 loops=1,953)

  • Index Cond: (dcps_case_uid = pmtrqst_t.dcps_case_uid)
15. 4.578 4.578 ↑ 1.0 1 1,962 / 3

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.42..0.63 rows=1 width=58) (actual time=0.006..0.007 rows=1 loops=1,962)

  • 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. 7.310 17.658 ↑ 1.0 1 1,962

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

  • Join Filter: ((caseclm_t.clm_typ)::text = (t2clmcd_t.cd)::text)
  • Rows Removed by Join Filter: 10
18. 7.848 7.848 ↑ 1.0 1 1,962

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=1,962)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
19. 2.500 2.500 ↑ 1.5 8 2,500

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

20. 30,856.374 30,856.374 ↑ 1.0 1 1,962

Aggregate (cost=4,566.96..4,566.97 rows=1 width=8) (actual time=15.727..15.727 rows=1 loops=1,962)

  • -> Seq Scan on ceappthist_t (cost=0.00..4566.96 rows=1 width=8) (actual time=9.562..15.724 rows=1loops=1962)
  • Filter: ((ce_appt_uid = ceappt_t.ce_appt_uid) AND ((appt_stus_cd)::text = (ceappt_t.appt_stus_cd)::text))
  • Rows Removed by Filter: 147463
21. 1.962 17.658 ↓ 0.0 0 1,962

Limit (cost=8.88..270.17 rows=1 width=6) (actual time=0.009..0.009 rows=0 loops=1,962)

22. 14.160 15.696 ↓ 0.0 0 1,962

Nested Loop (cost=8.88..270.17 rows=1 width=6) (actual time=0.008..0.008 rows=0 loops=1,962)

  • Join Filter: (stcatlgprod_t.prod_uid = product_t.prod_uid)
  • -> Index Only Scan using prod_uid_pkey on product_t (cost=0.28..0.39 rows=1 width=4) (neverexecuted)
23. 0.000 1.536 ↓ 0.0 0 192

Nested Loop (cost=8.60..269.76 rows=1 width=14) (actual time=0.008..0.008 rows=0 loops=192)

  • Index Cond: (prod_uid = prodrlshp_t.rltd_prod_uid)
  • Heap Fetches: 0
24. 7.776 9.810 ↓ 0.0 0 1,962

Hash Join (cost=8.31..267.79 rows=5 width=12) (actual time=0.005..0.005 rows=0 loops=1,962)

  • Hash Cond: (prodrlshp_t.rltd_prod_uid = stcatlgprod_t.prod_uid)
25. 0.072 0.072 ↑ 5,597.0 1 1

Seq Scan on prodrlshp_t (cost=0.00..244.73 rows=5,597 width=8) (actual time=0.072..0.072 rows=1 loops=1)

  • 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: 304
26. 0.000 1.962 ↓ 0.0 0 1,962

Hash (cost=8.30..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,962)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 1.962 1.962 ↓ 0.0 0 1,962

Index Scan using catalog_product_uid_pkey on stcatlgprod_t (cost=0.29..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,962)

  • Index Cond: (st_catlg_prod_uid = pmtrqst_t.st_catlg_prod_uid)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using prod_uid_pkey on product_t product_t_1 (cost=0.28..0.39 rows=1 width=10) (never executed)

  • Index Cond: (prod_uid = prodrlshp_t.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
29. 0.000 7.848 ↓ 0.0 0 1,962

Limit (cost=17.37..283.25 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=1,962)

30. 1.962 7.848 ↓ 0.0 0 1,962

Nested Loop (cost=17.37..283.25 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=1,962)

31. 3.861 5.886 ↓ 0.0 0 1,962

Hash Join (cost=17.09..282.86 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,962)

  • Hash Cond: (prodrlshp_t_1.rltd_prod_uid = stcatlgprod_t_1.prod_uid)
32. 0.063 0.063 ↑ 5,597.0 1 1

Seq Scan on prodrlshp_t prodrlshp_t_1 (cost=0.00..244.73 rows=5,597 width=8) (actual time=0.063..0.063 rows=1 loops=1)

  • 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: 304
33. 0.000 1.962 ↓ 0.0 0 1,962

Hash (cost=17.08..17.08 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,962)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
34. 0.000 1.962 ↓ 0.0 0 1,962

Nested Loop (cost=0.98..17.08 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,962)

35. 1.962 1.962 ↓ 0.0 0 1,962

Nested Loop (cost=0.70..16.74 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,962)

36. 0.000 0.000 ↓ 0.0 0 1,962

Index Scan using ceapptitem_pkey on ceapptitem_t (cost=0.42..8.44 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1,962)

  • Index Cond: (ce_appt_itm_uid = pmtrqst_t.pmtrqst_li_id)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_product_uid_pkey on stcatlgprod_t stcatlgprod_t_1 (cost=0.29..8.30 rows=1 width=8) (never executed)

  • Index Cond: (st_catlg_prod_uid = ceapptitem_t.st_catlg_prod_uid)
38. 0.000 0.000 ↓ 0.0 0

Index Only Scan using prod_uid_pkey on product_t product_t_2 (cost=0.28..0.34 rows=1 width=4) (never executed)

  • Index Cond: (prod_uid = stcatlgprod_t_1.prod_uid)
  • Heap Fetches: 0
39. 0.000 0.000 ↓ 0.0 0

Index Scan using prod_uid_pkey on product_t product_t_3 (cost=0.28..0.39 rows=1 width=10) (never executed)

  • Index Cond: (prod_uid = prodrlshp_t_1.prod_uid)
  • Filter: ((prod_nmspace_cd)::text = 'OH'::text)
40. 1.962 33.354 ↑ 1.0 1 1,962

Aggregate (cost=19.17..19.18 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1,962)

41. 3.238 31.392 ↑ 1.0 1 1,962

Nested Loop (cost=0.85..19.17 rows=1 width=8) (actual time=0.013..0.016 rows=1 loops=1,962)

42. 17.658 17.658 ↑ 1.0 1 1,962

Index Scan using ixf03_rqstuid on corrtrkg_t (cost=0.43..10.71 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1,962)

  • 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: 3
43. 10.496 10.496 ↓ 0.0 1 2,624

Index Scan using ixf01_barcdtrkg_t_corrtrkg on barcdtrkg_t (cost=0.43..8.45 rows=1 width12) (cost=0..0 rows=0 width=0) (actual time=0.004..0.004 rows=1 loops=2,624)

  • Index Cond: (crsp_trkg_uid = corrtrkg_t.crsp_trkg_uid)
44. 1.962 15.696 ↑ 1.0 1 1,962

Aggregate (cost=19.17..19.18 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1,962)

45. 3.102 13.734 ↓ 3.0 3 1,962

Nested Loop (cost=0.85..19.17 rows=1 width=8) (actual time=0.005..0.007 rows=3 loops=1,962)

46. 5.886 5.886 ↑ 1.0 1 1,962

Index Scan using ixf03_rqstuid on corrtrkg_t corrtrkg_t_1 (cost=0.43..10.71 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1,962)

  • Index Cond: (rqst_uid = ceappt_t.ce_appt_uid)
  • Filter: (((rcpnt_typ_cd)::text = 'C'::text) AND ((rqst_typ_cd)::text = 'TRA'::text))
  • Rows Removed by Filter: 4
47. 4.746 4.746 ↓ 2.0 2 2,373

Index Scan using ixf01_barcdtrkg_t_corrtrkg on barcdtrkg_t barcdtrkg_t_1 (cost=0.43..8.45 rows=1 width=12) (actual time=0.002..0.002 rows=2 loops=2,373)

  • Index Cond: (crsp_trkg_uid = corrtrkg_t_1.crsp_trkg_uid)
Planning time : 5.439 ms
Execution time : 30,968.982 ms