explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FgOB

Settings
# exclusive inclusive rows x rows loops node
1. 1,078.135 42,790.926 ↓ 1.1 63,241 1

Hash Left Join (cost=192,509.09..1,289,716.76 rows=59,356 width=787) (actual time=36,843.485..42,790.926 rows=63,241 loops=1)

  • Hash Cond: ((CASE WHEN ((t1.in_instrumenttype)::text = 'FXXXXX'::text) THEN 'FU'::text WHEN ((t1.in_instrumenttype)::text = ANY ('{OPXXXX,OCXXXX}'::text[])) THEN 'OP'::text ELSE NULL::text END = (ins_ulv.pu_productgroupcode)::text) AND ((t1.in_symbolloca (...)
2. 66.284 41,681.123 ↓ 1.1 63,241 1

Hash Left Join (cost=187,719.91..1,262,075.41 rows=59,356 width=785) (actual time=36,811.121..41,681.123 rows=63,241 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_sourcemovementcode)::numeric = t9.mc_movementcode)
3. 28.896 41,614.782 ↓ 1.1 63,241 1

Nested Loop Left Join (cost=187,717.58..1,261,527.01 rows=59,356 width=773) (actual time=36,810.992..41,614.782 rows=63,241 loops=1)

4. 91.700 41,016.717 ↓ 1.1 63,241 1

Nested Loop (cost=187,717.02..770,775.92 rows=59,356 width=778) (actual time=36,810.862..41,016.717 rows=63,241 loops=1)

5. 31.747 38,901.305 ↓ 1.1 63,241 1

Hash Left Join (cost=187,716.46..274,747.49 rows=59,356 width=742) (actual time=36,809.141..38,901.305 rows=63,241 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_instrument_techid)::text = (a.ec_instrument_techid)::text)
6. 556.077 38,551.068 ↓ 1.1 63,241 1

Hash Join (cost=164,942.14..251,750.25 rows=59,356 width=737) (actual time=36,490.555..38,551.068 rows=63,241 loops=1)

  • Hash Cond: (tb_f_fee_movement_fca_201911.fm_dummykey = tb_f_transaction_fca_201911.tr_dummykey)
7. 109.838 1,510.827 ↑ 1.0 2,297,875 1

Append (cost=0.00..77,597.39 rows=2,297,909 width=633) (actual time=0.038..1,510.827 rows=2,297,875 loops=1)

8. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on tb_f_fee_movement_fca_201911 (cost=0.00..0.00 rows=1 width=1,112) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191126) AND (fm_vstatus = 'A'::bpchar))
9. 1,400.988 1,400.988 ↑ 1.0 2,297,875 1

Seq Scan on tb_f_fee_movement_fca_20191126 (cost=0.00..77,597.39 rows=2,297,908 width=633) (actual time=0.037..1,400.988 rows=2,297,875 loops=1)

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191126) AND (fm_vstatus = 'A'::bpchar))
10. 216.670 36,484.164 ↓ 1.6 95,576 1

Hash (cost=164,200.19..164,200.19 rows=59,356 width=121) (actual time=36,484.164..36,484.164 rows=95,576 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 15452kB
11. 29.940 36,267.494 ↓ 1.6 95,576 1

Append (cost=0.00..164,200.19 rows=59,356 width=121) (actual time=12.055..36,267.494 rows=95,576 loops=1)

12. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on tb_f_transaction_fca_201911 (cost=0.00..0.00 rows=1 width=1,084) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((((tr_runtype)::text = 'EOD'::text) OR (tr_runtype IS NULL)) AND ((tr_bccentity)::text = 'FCA'::text) AND (tr_businessdate = '20191126'::numeric) AND ((tr_vstatus)::text = 'ACTIVE'::text) AND ((tr_ (...)
13. 36,237.554 36,237.554 ↓ 1.6 95,576 1

Index Scan using ix_tr_clientaccinfo_clientid01_techid01_fca_20191126 on tb_f_transaction_fca_20191126 (cost=0.56..164,200.19 rows=59,355 width=121) (actual time=12.053..36,237.554 rows=95,576 loops=1)

  • Index Cond: ((tr_businessdate = '20191126'::numeric) AND ((tr_bccentity)::text = 'FCA'::text) AND ((tr_clientaccinfo_clientid)::text = ANY ('{FCL|L|IMA,FCL|M|IES,FCA|500|,FCA|248|,FCA|3248|,FCL|M|IMA}'::tex (...)
  • Filter: ((((tr_runtype)::text = 'EOD'::text) OR (tr_runtype IS NULL)) AND ((tr_vstatus)::text = 'ACTIVE'::text))
14. 1.587 318.490 ↓ 909.6 7,277 1

Hash (cost=22,774.22..22,774.22 rows=8 width=44) (actual time=318.490..318.490 rows=7,277 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 605kB
15. 6.509 316.903 ↓ 909.6 7,277 1

Hash Join (cost=21,884.88..22,774.22 rows=8 width=44) (actual time=308.640..316.903 rows=7,277 loops=1)

  • Hash Cond: (((tb_f_energy_future_contract.ec_instrument_techid)::text = (a.ec_instrument_techid)::text) AND ((COALESCE(max(CASE WHEN (tb_f_energy_future_contract.ec_processdate = '20191126'::numeric) THEN tb_f_energy_f (...)
16. 11.962 23.693 ↑ 1.9 7,277 1

HashAggregate (cost=10,959.91..11,096.72 rows=13,681 width=45) (actual time=21.780..23.693 rows=7,277 loops=1)

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
17. 0.668 11.731 ↑ 1.0 13,716 1

Append (cost=0.00..10,715.31 rows=13,977 width=45) (actual time=1.815..11.731 rows=13,716 loops=1)

18. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on tb_f_energy_future_contract (cost=0.00..0.00 rows=1 width=232) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((ec_businessdate = '20191126'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
19. 9.328 11.062 ↑ 1.0 13,716 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 (cost=751.68..10,715.31 rows=13,976 width=45) (actual time=1.807..11.062 rows=13,716 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191126'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=464
20. 1.734 1.734 ↑ 1.0 13,716 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..748.18 rows=13,976 width=0) (actual time=1.734..1.734 rows=13,716 loops=1)

  • Index Cond: ((ec_businessdate = '20191126'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
21. 8.744 286.701 ↑ 1.0 13,716 1

Hash (cost=10,715.31..10,715.31 rows=13,977 width=50) (actual time=286.701..286.701 rows=13,716 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1241kB
22. 1.551 277.957 ↑ 1.0 13,716 1

Append (cost=0.00..10,715.31 rows=13,977 width=50) (actual time=8.681..277.957 rows=13,716 loops=1)

23. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on tb_f_energy_future_contract a (cost=0.00..0.00 rows=1 width=246) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((ec_businessdate = '20191126'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
24. 268.942 276.405 ↑ 1.0 13,716 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 a_1 (cost=751.68..10,715.31 rows=13,976 width=50) (actual time=8.678..276.405 rows=13,716 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191126'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=464
25. 7.463 7.463 ↑ 1.0 13,716 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..748.18 rows=13,976 width=0) (actual time=7.463..7.463 rows=13,716 loops=1)

  • Index Cond: ((ec_businessdate = '20191126'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
26. 2,023.712 2,023.712 ↑ 1.0 1 63,241

Index Scan using ix_ak_d_instrument2_01_fca on tb_d_instrument2_fca t1 (cost=0.56..8.35 rows=1 width=96) (actual time=0.032..0.032 rows=1 loops=63,241)

  • Index Cond: (((in_instrument_techid)::text = (tb_f_transaction_fca_201911.tr_instrument_techid)::text) AND ((in_bccentity)::text = 'FCA'::text))
  • Filter: ((in_vstatus)::text = 'ACTIVE'::text)
27. 569.169 569.169 ↑ 1.0 1 63,241

Index Scan using ix_ak_d_instrument2_01_fca on tb_d_instrument2_fca ulv (cost=0.56..8.26 rows=1 width=47) (actual time=0.009..0.009 rows=1 loops=63,241)

  • Index Cond: (((in_instrument_techid)::text = (t1.in_instrument_techidulv)::text) AND ((in_bccentity)::text = 'FCA'::text))
  • Filter: ((in_vstatus)::text = 'ACTIVE'::text)
28. 0.040 0.057 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=20) (actual time=0.057..0.057 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
29. 0.017 0.017 ↑ 1.0 59 1

Seq Scan on tb_d_movement_code t9 (cost=0.00..1.59 rows=59 width=20) (actual time=0.009..0.017 rows=59 loops=1)

30. 6.188 31.668 ↑ 1.0 26,282 1

Hash (cost=4,329.23..4,329.23 rows=26,283 width=13) (actual time=31.668..31.668 rows=26,282 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1444kB
31. 1.253 25.480 ↑ 1.0 26,282 1

Append (cost=0.00..4,329.23 rows=26,283 width=13) (actual time=0.043..25.480 rows=26,282 loops=1)

32. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on tb_d_productulv ins_ulv (cost=0.00..0.00 rows=1 width=64) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (((pu_bccentity)::text = 'FCA'::text) AND ((pu_vstatus)::text = 'ACTIVE'::text))
33. 24.223 24.223 ↑ 1.0 26,282 1

Seq Scan on tb_d_productulv_fca ins_ulv_1 (cost=0.00..4,329.23 rows=26,282 width=13) (actual time=0.035..24.223 rows=26,282 loops=1)

  • Filter: (((pu_bccentity)::text = 'FCA'::text) AND ((pu_vstatus)::text = 'ACTIVE'::text))
Planning time : 89.361 ms
Execution time : 42,809.045 ms