explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B2kT

Settings
# exclusive inclusive rows x rows loops node
1. 638.352 28,502.310 ↑ 1.8 34,354 1

Hash Left Join (cost=136,909.03..1,223,821.87 rows=60,662 width=898) (actual time=22,495.704..28,502.310 rows=34,354 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. 44.441 27,784.035 ↑ 1.8 34,354 1

Hash Left Join (cost=132,119.84..1,195,677.72 rows=60,662 width=896) (actual time=22,400.270..27,784.035 rows=34,354 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_sourcemovementcode)::numeric = t9.mc_movementcode)
3. 18.531 27,738.867 ↑ 1.8 34,354 1

Nested Loop Left Join (cost=132,117.52..1,195,117.30 rows=60,662 width=884) (actual time=22,399.462..27,738.867 rows=34,354 loops=1)

4. 57.239 27,101.964 ↑ 1.8 34,354 1

Nested Loop Left Join (cost=132,116.96..693,568.30 rows=60,662 width=889) (actual time=22,395.381..27,101.964 rows=34,354 loops=1)

5. 22.436 23,952.865 ↑ 1.8 34,354 1

Hash Left Join (cost=132,116.40..186,742.07 rows=60,662 width=854) (actual time=22,390.153..23,952.865 rows=34,354 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_instrument_techid)::text = (a.ec_instrument_techid)::text)
6. 456.882 23,667.808 ↑ 1.8 34,354 1

Hash Join (cost=109,388.17..163,786.06 rows=60,662 width=849) (actual time=22,127.402..23,667.808 rows=34,354 loops=1)

  • Hash Cond: (tb_f_fee_movement_fca_201911.fm_dummykey = tb_f_transaction_fca_201911.tr_dummykey)
7. 73.065 1,116.463 ↑ 1.0 1,431,683 1

Append (cost=0.00..48,422.45 rows=1,431,684 width=744) (actual time=1.324..1,116.463 rows=1,431,683 loops=1)

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

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191128) AND (fm_vstatus = 'A'::bpchar))
9. 1,043.396 1,043.396 ↑ 1.0 1,431,683 1

Seq Scan on tb_f_fee_movement_fca_20191128 (cost=0.00..48,422.45 rows=1,431,683 width=744) (actual time=1.322..1,043.396 rows=1,431,683 loops=1)

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191128) AND (fm_vstatus = 'A'::bpchar))
10. 130.794 22,094.463 ↓ 1.0 61,642 1

Hash (cost=108,629.90..108,629.90 rows=60,662 width=122) (actual time=22,094.463..22,094.463 rows=61,642 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 9827kB
11. 18.405 21,963.669 ↓ 1.0 61,642 1

Append (cost=0.00..108,629.90 rows=60,662 width=122) (actual time=2.871..21,963.669 rows=61,642 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_clientaccinfo_clientid)::text = ANY ('{FCA|248|,FCA|500|}'::text[])) AND (((tr_runtype)::text = 'EOD'::text) OR (tr_runtype IS NULL)) AND ((tr_bccentity)::text = 'FCA'::text) AND (tr_businessd (...)
13. 21,945.264 21,945.264 ↓ 1.0 61,642 1

Index Scan using ix_tr_clientaccinfo_clientid01_fca_20191128 on tb_f_transaction_fca_20191128 (cost=0.43..108,629.90 rows=60,661 width=122) (actual time=2.870..21,945.264 rows=61,642 loops=1)

  • Index Cond: ((tr_businessdate = '20191128'::numeric) AND ((tr_bccentity)::text = 'FCA'::text) AND ((tr_clientaccinfo_clientid)::text = ANY ('{FCA|248|,FCA|500|}'::text[])))
  • Filter: ((((tr_runtype)::text = 'EOD'::text) OR (tr_runtype IS NULL)) AND ((tr_vstatus)::text = 'ACTIVE'::text))
14. 1.893 262.621 ↓ 1,045.1 7,316 1

Hash (cost=22,728.13..22,728.13 rows=7 width=44) (actual time=262.621..262.621 rows=7,316 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 607kB
15. 3.525 260.728 ↓ 1,045.1 7,316 1

Hash Join (cost=21,850.30..22,728.13 rows=7 width=44) (actual time=255.867..260.728 rows=7,316 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 = '20191128'::numeric) THEN tb_f_energy_f (...)
16. 10.790 17.018 ↑ 1.8 7,316 1

HashAggregate (cost=10,942.03..11,077.07 rows=13,504 width=45) (actual time=15.505..17.018 rows=7,316 loops=1)

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
17. 0.663 6.228 ↓ 1.0 13,895 1

Append (cost=0.00..10,705.71 rows=13,504 width=45) (actual time=1.955..6.228 rows=13,895 loops=1)

18. 0.005 0.005 ↓ 0.0 0 1

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

  • Filter: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
19. 3.694 5.560 ↓ 1.0 13,895 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 (cost=726.83..10,705.71 rows=13,503 width=45) (actual time=1.941..5.560 rows=13,895 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=465
20. 1.866 1.866 ↓ 1.0 13,895 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..723.45 rows=13,503 width=0) (actual time=1.866..1.866 rows=13,895 loops=1)

  • Index Cond: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
21. 7.991 240.185 ↓ 1.0 13,895 1

Hash (cost=10,705.71..10,705.71 rows=13,504 width=50) (actual time=240.185..240.185 rows=13,895 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1255kB
22. 1.326 232.194 ↓ 1.0 13,895 1

Append (cost=0.00..10,705.71 rows=13,504 width=50) (actual time=12.612..232.194 rows=13,895 loops=1)

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

  • Filter: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
24. 219.546 230.856 ↓ 1.0 13,895 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 a_1 (cost=726.83..10,705.71 rows=13,503 width=50) (actual time=12.594..230.856 rows=13,895 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=465
25. 11.310 11.310 ↓ 1.0 13,895 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..723.45 rows=13,503 width=0) (actual time=11.310..11.310 rows=13,895 loops=1)

  • Index Cond: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
26. 3,091.860 3,091.860 ↑ 1.0 1 34,354

Index Scan using ix_ak_d_instrument2_01_fca on tb_d_instrument2_fca t1 (cost=0.56..8.34 rows=1 width=96) (actual time=0.090..0.090 rows=1 loops=34,354)

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

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.017..0.018 rows=1 loops=34,354)

  • 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.034 0.727 ↑ 1.0 59 1

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

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

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

30. 7.359 79.923 ↑ 1.0 26,282 1

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

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

Append (cost=0.00..4,329.23 rows=26,283 width=13) (actual time=1.231..72.564 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. 71.174 71.174 ↑ 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=1.224..71.174 rows=26,282 loops=1)

  • Filter: (((pu_bccentity)::text = 'FCA'::text) AND ((pu_vstatus)::text = 'ACTIVE'::text))
Planning time : 652.366 ms
Execution time : 28,507.491 ms