explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RgIH : qwer

Settings
# exclusive inclusive rows x rows loops node
1. 11.915 13,679.387 ↓ 9,999.0 9,999 1

Subquery Scan on d (cost=2.35..48,505.61 rows=1 width=1,145) (actual time=0.807..13,679.387 rows=9,999 loops=1)

  • Filter: ((d.row_id >= 0) AND (d.row_id < 10000))
  • Rows Removed by Filter: 30024
2. 150.417 13,667.472 ↓ 40,023.0 40,023 1

WindowAgg (cost=2.35..48,505.59 rows=1 width=1,145) (actual time=0.806..13,667.472 rows=40,023 loops=1)

3. 191.761 13,517.055 ↓ 40,023.0 40,023 1

Nested Loop Left Join (cost=2.35..48,505.53 rows=1 width=824) (actual time=0.788..13,517.055 rows=40,023 loops=1)

  • Join Filter: (((dl.company_code)::text = (a.company_code)::text) AND ((dl.prod_type)::text = (a.prod_type)::text) AND (dl.cur_code = a.cur_code))
4. 560.697 13,285.271 ↓ 40,023.0 40,023 1

Nested Loop Left Join (cost=2.35..48,504.42 rows=1 width=775) (actual time=0.778..13,285.271 rows=40,023 loops=1)

  • Join Filter: (((pl.company_code)::text = (a.company_code)::text) AND ((pl.prod_code)::text = (a.prod_code)::text) AND (pl.cur_code = a.cur_code))
  • Rows Removed by Join Filter: 4933210
5. 397.281 12,124.229 ↓ 40,023.0 40,023 1

Nested Loop Left Join (cost=2.35..48,487.67 rows=1 width=766) (actual time=0.738..12,124.229 rows=40,023 loops=1)

  • Join Filter: (((pci.prod_code)::text = (a.prod_code)::text) AND ((pci.int_type_code)::text = (ai.int_type_code)::text) AND (pci.cur_code = a.cur_code))
  • Rows Removed by Join Filter: 2521314
6. 123.792 11,606.879 ↓ 40,023.0 40,023 1

Nested Loop Left Join (cost=2.35..48,484.94 rows=1 width=750) (actual time=0.719..11,606.879 rows=40,023 loops=1)

  • Filter: ((aa.last_accrual_date IS NULL) OR (aa.last_accrual_date < to_date('2019-09-17'::text, 'yyyy-mm-dd'::text)))
7. 439.053 10,962.788 ↓ 40,023.0 40,023 1

Nested Loop Left Join (cost=1.93..48,483.64 rows=1 width=742) (actual time=0.692..10,962.788 rows=40,023 loops=1)

  • Join Filter: (((i.dest_cont_bal_type)::text = (bt_cont.bal_type_code)::text) AND ((i.prod_type)::text = (bt_cont.prod_type)::text))
  • Rows Removed by Join Filter: 4052309
8. 467.052 10,323.620 ↓ 40,023.0 40,023 1

Nested Loop Left Join (cost=1.93..48,470.84 rows=1 width=793) (actual time=0.671..10,323.620 rows=40,023 loops=1)

  • Join Filter: (((i.dest_bal_type)::text = (bt.bal_type_code)::text) AND ((i.prod_type)::text = (bt.prod_type)::text))
  • Rows Removed by Join Filter: 4012263
9. 695.034 9,616.430 ↓ 40,023.0 40,023 1

Nested Loop (cost=1.93..48,458.04 rows=1 width=786) (actual time=0.641..9,616.430 rows=40,023 loops=1)

  • Join Filter: ((a.prod_code)::text = (p.prod_code)::text)
  • Rows Removed by Join Filter: 5844845
10. 1,234.150 8,561.189 ↓ 40,023.0 40,023 1

Nested Loop Left Join (cost=1.93..48,422.52 rows=1 width=768) (actual time=0.594..8,561.189 rows=40,023 loops=1)

  • Join Filter: (((pi.prod_code)::text = (a.prod_code)::text) AND ((pi.int_type_code)::text = (ai.int_type_code)::text))
  • Rows Removed by Join Filter: 2482002
  • Filter: (fnc_pk_bcom_checkfreqday_new(to_date('2019-09-09'::text, 'yyyy-mm-dd'::text), (CASE ai.int_lvl WHEN 'P'::text THEN pi.accr_freq ELSE ai.accr_freq END)::text, (CASE ai.int_lvl WHEN 'P'::text THEN pi.accr_unit ELSE ai.accr_unit END)::integer, (CASE ai.int_lvl WHEN 'P'::text THEN pi.accr_day ELSE ai.accr_day END)::integer, a.start_date, a.end_date, a.next_schd_date) = 1)
11. 61.116 7,246.993 ↓ 20,011.5 40,023 1

Nested Loop (cost=1.93..48,131.01 rows=2 width=649) (actual time=0.325..7,246.993 rows=40,023 loops=1)

12. 276.710 3,168.707 ↓ 963.4 401,717 1

Hash Join (cost=1.51..47,736.05 rows=417 width=560) (actual time=0.111..3,168.707 rows=401,717 loops=1)

  • Hash Cond: (((ai.int_type_code)::text = (i.int_type_code)::text) AND ((ai.prod_type)::text = (i.prod_type)::text))
13. 2,891.947 2,891.947 ↓ 3.0 903,248 1

Seq Scan on bcom_acnt_int ai (cost=0.00..46,151.19 rows=301,104 width=332) (actual time=0.039..2,891.947 rows=903,248 loops=1)

  • Filter: ((int_stopped = '0'::numeric) AND (CASE WHEN (expire_date IS NULL) THEN (to_date('2019-09-17'::text, 'yyyy-mm-dd'::text) + ('1 days'::cstring)::interval) ELSE (expire_date)::timestamp without time zone END > to_date('2019-09-17'::text, 'yyyy-mm-dd'::text)))
  • Rows Removed by Filter: 125
14. 0.011 0.050 ↓ 7.5 15 1

Hash (cost=1.48..1.48 rows=2 width=290) (actual time=0.050..0.050 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.039 0.039 ↓ 7.5 15 1

Seq Scan on bcom_int i (cost=0.00..1.48 rows=2 width=290) (actual time=0.032..0.039 rows=15 loops=1)

  • Filter: ((type)::text = ANY ('{SIMPLE,SIMPLE_H}'::text[]))
  • Rows Removed by Filter: 23
16. 4,017.170 4,017.170 ↓ 0.0 0 401,717

Index Scan using pk_bcom_acnt_link on bcom_acnt a (cost=0.42..0.95 rows=1 width=102) (actual time=0.010..0.010 rows=0 loops=401,717)

  • Index Cond: ((acnt_code)::text = (ai.acnt_code)::text)
  • Filter: (((company_code)::text = '30'::text) AND ((status)::text <> ALL ('{N,C,D}'::text[])) AND (((prod_type)::text <> 'TD'::text) OR (end_date > to_date('2019-09-17'::text, 'yyyy-mm-dd'::text))) AND (fnc_pk_bcom_check_acnt_code_digit(10, 0, (last_digit)::integer) = 1))
  • Rows Removed by Filter: 1
17. 79.879 80.046 ↑ 7.7 63 40,023

Materialize (cost=0.00..16.28 rows=485 width=152) (actual time=0.000..0.002 rows=63 loops=40,023)

18. 0.167 0.167 ↑ 1.3 364 1

Seq Scan on bcom_prod_int pi (cost=0.00..13.85 rows=485 width=152) (actual time=0.027..0.167 rows=364 loops=1)

19. 360.207 360.207 ↑ 5.0 147 40,023

Seq Scan on bcom_prod p (cost=0.00..26.34 rows=734 width=26) (actual time=0.001..0.009 rows=147 loops=40,023)

20. 240.138 240.138 ↑ 2.7 101 40,023

Seq Scan on bcom_bal_types bt (cost=0.00..8.72 rows=272 width=26) (actual time=0.001..0.006 rows=101 loops=40,023)

21. 200.115 200.115 ↑ 2.7 102 40,023

Seq Scan on bcom_bal_types bt_cont (cost=0.00..8.72 rows=272 width=26) (actual time=0.001..0.005 rows=102 loops=40,023)

22. 520.299 520.299 ↑ 1.0 1 40,023

Index Scan using pk_bcom_acnt_accrual on bcom_acnt_accrual aa (cost=0.42..1.29 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=40,023)

  • Index Cond: (((acnt_code)::text = (ai.acnt_code)::text) AND ((int_type_code)::text = (ai.int_type_code)::text) AND (item_no = '0'::numeric))
23. 120.069 120.069 ↑ 1.0 63 40,023

Seq Scan on bcom_prod_ccy_int pci (cost=0.00..1.63 rows=63 width=39) (actual time=0.001..0.003 rows=63 loops=40,023)

24. 600.345 600.345 ↑ 3.0 124 40,023

Seq Scan on bcom_prod_limit pl (cost=0.00..10.25 rows=371 width=24) (actual time=0.002..0.015 rows=124 loops=40,023)

  • Filter: ((company_code)::text = '30'::text)
  • Rows Removed by Filter: 6
25. 40.023 40.023 ↓ 0.0 0 40,023

Seq Scan on bcom_default_limit dl (cost=0.00..1.10 rows=1 width=148) (actual time=0.001..0.001 rows=0 loops=40,023)

  • Filter: ((company_code)::text = '30'::text)
  • Rows Removed by Filter: 8