explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2uWb

Settings
# exclusive inclusive rows x rows loops node
1. 1,193.163 54,664.605 ↑ 1.7 67,407 1

Hash Left Join (cost=238,158.06..2,320,805.79 rows=116,879 width=787) (actual time=48,073.132..54,664.605 rows=67,407 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. 69.149 53,439.454 ↑ 1.7 67,407 1

Hash Left Join (cost=233,368.88..2,271,017.99 rows=116,879 width=785) (actual time=48,040.547..53,439.454 rows=67,407 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_sourcemovementcode)::numeric = t9.mc_movementcode)
3. 54.566 53,370.261 ↑ 1.7 67,407 1

Nested Loop Left Join (cost=233,366.55..2,269,940.38 rows=116,879 width=773) (actual time=48,040.439..53,370.261 rows=67,407 loops=1)

4. 86.853 52,709.032 ↑ 1.7 67,407 1

Nested Loop (cost=233,365.99..1,303,593.32 rows=116,879 width=778) (actual time=48,040.371..52,709.032 rows=67,407 loops=1)

5. 38.577 50,599.969 ↑ 1.7 67,407 1

Hash Left Join (cost=233,365.43..331,986.51 rows=116,879 width=743) (actual time=48,040.260..50,599.969 rows=67,407 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_instrument_techid)::text = (a.ec_instrument_techid)::text)
6. 898.684 50,209.766 ↑ 1.7 67,407 1

Hash Join (cost=210,610.69..308,792.89 rows=116,879 width=738) (actual time=47,688.565..50,209.766 rows=67,407 loops=1)

  • Hash Cond: (tb_f_fee_movement_fca_201911.fm_dummykey = tb_f_transaction_fca_201911.tr_dummykey)
7. 124.123 1,656.438 ↑ 1.0 2,583,053 1

Append (cost=0.00..87,326.69 rows=2,583,126 width=633) (actual time=0.039..1,656.438 rows=2,583,053 loops=1)

8. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=1)

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191122) AND (fm_vstatus = 'A'::bpchar))
9. 1,532.315 1,532.315 ↑ 1.0 2,583,053 1

Seq Scan on tb_f_fee_movement_fca_20191122 (cost=0.00..87,326.69 rows=2,583,125 width=633) (actual time=0.039..1,532.315 rows=2,583,053 loops=1)

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191122) AND (fm_vstatus = 'A'::bpchar))
10. 243.240 47,654.644 ↑ 1.2 101,574 1

Hash (cost=209,149.70..209,149.70 rows=116,879 width=122) (actual time=47,654.644..47,654.644 rows=101,574 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16338kB
11. 37.822 47,411.404 ↑ 1.2 101,574 1

Append (cost=0.00..209,149.70 rows=116,879 width=122) (actual time=3.337..47,411.404 rows=101,574 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 = '20191122'::numeric) AND ((tr_vstatus)::text = 'ACTIVE'::text) AND ((tr_ (...)
13. 47,373.582 47,373.582 ↑ 1.2 101,574 1

Index Scan using ix_tr_clientaccinfo_clientid01_fca_20191122 on tb_f_transaction_fca_20191122 (cost=0.43..209,149.70 rows=116,878 width=122) (actual time=3.336..47,373.582 rows=101,574 loops=1)

  • Index Cond: ((tr_businessdate = '20191122'::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.769 351.626 ↓ 969.3 6,785 1

Hash (cost=22,754.66..22,754.66 rows=7 width=44) (actual time=351.626..351.626 rows=6,785 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 568kB
15. 5.255 349.857 ↓ 969.3 6,785 1

Hash Join (cost=21,865.32..22,754.66 rows=7 width=44) (actual time=343.191..349.857 rows=6,785 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 = '20191122'::numeric) THEN tb_f_energy_f (...)
16. 10.371 16.375 ↑ 2.0 6,785 1

HashAggregate (cost=10,949.80..11,086.61 rows=13,681 width=45) (actual time=14.853..16.375 rows=6,785 loops=1)

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
17. 0.579 6.004 ↑ 1.0 13,224 1

Append (cost=0.00..10,709.91 rows=13,708 width=45) (actual time=1.896..6.004 rows=13,224 loops=1)

18. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((ec_businessdate = '20191122'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
19. 3.611 5.423 ↑ 1.0 13,224 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 (cost=736.92..10,709.91 rows=13,707 width=45) (actual time=1.890..5.423 rows=13,224 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191122'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=448
20. 1.812 1.812 ↑ 1.0 13,224 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..733.49 rows=13,707 width=0) (actual time=1.812..1.812 rows=13,224 loops=1)

  • Index Cond: ((ec_businessdate = '20191122'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
21. 8.552 328.227 ↑ 1.0 13,224 1

Hash (cost=10,709.91..10,709.91 rows=13,708 width=50) (actual time=328.227..328.227 rows=13,224 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1201kB
22. 1.627 319.675 ↑ 1.0 13,224 1

Append (cost=0.00..10,709.91 rows=13,708 width=50) (actual time=10.815..319.675 rows=13,224 loops=1)

23. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Filter: ((ec_businessdate = '20191122'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
24. 308.384 318.046 ↑ 1.0 13,224 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 a_1 (cost=736.92..10,709.91 rows=13,707 width=50) (actual time=10.809..318.046 rows=13,224 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191122'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=448
25. 9.662 9.662 ↑ 1.0 13,224 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..733.49 rows=13,707 width=0) (actual time=9.662..9.662 rows=13,224 loops=1)

  • Index Cond: ((ec_businessdate = '20191122'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
26. 2,022.210 2,022.210 ↑ 1.0 1 67,407

Index Scan using ix_ak_d_instrument2_01_fca on tb_d_instrument2_fca t1 (cost=0.56..8.30 rows=1 width=96) (actual time=0.030..0.030 rows=1 loops=67,407)

  • 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. 606.663 606.663 ↑ 1.0 1 67,407

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=67,407)

  • 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.024 0.044 ↑ 1.0 59 1

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

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

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

30. 6.092 31.988 ↑ 1.0 26,282 1

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

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

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

32. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (((pu_bccentity)::text = 'FCA'::text) AND ((pu_vstatus)::text = 'ACTIVE'::text))
33. 24.665 24.665 ↑ 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.031..24.665 rows=26,282 loops=1)

  • Filter: (((pu_bccentity)::text = 'FCA'::text) AND ((pu_vstatus)::text = 'ACTIVE'::text))
Planning time : 64.263 ms
Execution time : 54,671.416 ms