explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i8cO

Settings
# exclusive inclusive rows x rows loops node
1. 1,078.616 52,509.611 ↑ 1.0 62,872 1

Hash Left Join (cost=200,925.55..1,381,914.44 rows=64,071 width=788) (actual time=46,984.202..52,509.611 rows=62,872 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. 64.079 51,398.063 ↑ 1.0 62,872 1

Hash Left Join (cost=196,136.37..1,352,457.81 rows=64,071 width=786) (actual time=46,947.516..51,398.063 rows=62,872 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_sourcemovementcode)::numeric = t9.mc_movementcode)
3. 62.508 51,333.942 ↑ 1.0 62,872 1

Nested Loop Left Join (cost=196,134.04..1,351,866.03 rows=64,071 width=774) (actual time=46,947.436..51,333.942 rows=62,872 loops=1)

4. 66.635 50,768.458 ↑ 1.0 62,872 1

Nested Loop Left Join (cost=196,133.48..822,131.67 rows=64,071 width=779) (actual time=46,947.412..50,768.458 rows=62,872 loops=1)

5. 30.226 49,004.279 ↑ 1.0 62,872 1

Hash Left Join (cost=196,132.92..287,117.60 rows=64,071 width=744) (actual time=46,947.298..49,004.279 rows=62,872 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_instrument_techid)::text = (a.ec_instrument_techid)::text)
6. 529.104 48,702.723 ↑ 1.0 62,872 1

Hash Join (cost=173,514.57..264,258.67 rows=64,071 width=739) (actual time=46,675.912..48,702.723 rows=62,872 loops=1)

  • Hash Cond: (tb_f_fee_movement_fca_201911.fm_dummykey = tb_f_transaction_fca_201911.tr_dummykey)
7. 114.495 1,503.606 ↑ 1.0 2,402,542 1

Append (cost=0.00..81,093.61 rows=2,402,607 width=635) (actual time=0.041..1,503.606 rows=2,402,542 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 = 20191119) AND (fm_vstatus = 'A'::bpchar))
9. 1,389.109 1,389.109 ↑ 1.0 2,402,542 1

Seq Scan on tb_f_fee_movement_fca_20191119 (cost=0.00..81,093.61 rows=2,402,606 width=635) (actual time=0.039..1,389.109 rows=2,402,542 loops=1)

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191119) AND (fm_vstatus = 'A'::bpchar))
10. 236.795 46,670.013 ↓ 1.5 97,756 1

Hash (cost=172,713.69..172,713.69 rows=64,071 width=121) (actual time=46,670.013..46,670.013 rows=97,756 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 15688kB
11. 34.272 46,433.218 ↓ 1.5 97,756 1

Append (cost=0.00..172,713.69 rows=64,071 width=121) (actual time=10.385..46,433.218 rows=97,756 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 = '20191119'::numeric) AND ((tr_vstatus)::text = 'ACTIVE'::text) AND ((tr_ (...)
13. 46,398.946 46,398.946 ↓ 1.5 97,756 1

Index Scan using ix_tr_clientaccinfo_clientid01_techid01_fca_20191119 on tb_f_transaction_fca_20191119 (cost=0.56..172,713.69 rows=64,070 width=121) (actual time=10.384..46,398.946 rows=97,756 loops=1)

  • Index Cond: ((tr_businessdate = '20191119'::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.626 271.330 ↓ 962.1 6,735 1

Hash (cost=22,618.26..22,618.26 rows=7 width=44) (actual time=271.330..271.330 rows=6,735 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 564kB
15. 4.053 269.704 ↓ 962.1 6,735 1

Hash Join (cost=21,803.75..22,618.26 rows=7 width=44) (actual time=264.398..269.704 rows=6,735 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 = '20191119'::numeric) THEN tb_f_energy_f (...)
16. 10.503 16.133 ↑ 1.9 6,735 1

HashAggregate (cost=10,918.16..11,048.47 rows=13,031 width=45) (actual time=14.803..16.133 rows=6,735 loops=1)

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
17. 0.573 5.630 ↓ 1.0 13,173 1

Append (cost=0.00..10,690.12 rows=13,031 width=45) (actual time=1.707..5.630 rows=13,173 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 = '20191119'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
19. 3.424 5.055 ↓ 1.0 13,173 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 (cost=701.98..10,690.12 rows=13,030 width=45) (actual time=1.699..5.055 rows=13,173 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191119'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=445
20. 1.631 1.631 ↓ 1.0 13,173 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..698.72 rows=13,030 width=0) (actual time=1.631..1.631 rows=13,173 loops=1)

  • Index Cond: ((ec_businessdate = '20191119'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
21. 9.163 249.518 ↓ 1.0 13,173 1

Hash (cost=10,690.12..10,690.12 rows=13,031 width=50) (actual time=249.518..249.518 rows=13,173 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1197kB
22. 1.293 240.355 ↓ 1.0 13,173 1

Append (cost=0.00..10,690.12 rows=13,031 width=50) (actual time=8.556..240.355 rows=13,173 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 = '20191119'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
24. 231.621 239.060 ↓ 1.0 13,173 1

Bitmap Heap Scan on tb_f_energy_future_contract_fca_201911 a_1 (cost=701.98..10,690.12 rows=13,030 width=50) (actual time=8.553..239.060 rows=13,173 loops=1)

  • Recheck Cond: ((ec_businessdate = '20191119'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
  • Filter: ((ec_vstatus)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=445
25. 7.439 7.439 ↓ 1.0 13,173 1

Bitmap Index Scan on ix_pk_f_eng_fut_cont01_fca_201911 (cost=0.00..698.72 rows=13,030 width=0) (actual time=7.439..7.439 rows=13,173 loops=1)

  • Index Cond: ((ec_businessdate = '20191119'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
26. 1,697.544 1,697.544 ↑ 1.0 1 62,872

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.027..0.027 rows=1 loops=62,872)

  • 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. 502.976 502.976 ↑ 1.0 1 62,872

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.008..0.008 rows=1 loops=62,872)

  • 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.025 0.042 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=20) (actual time=0.042..0.042 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.011..0.017 rows=59 loops=1)

30. 6.246 32.932 ↑ 1.0 26,282 1

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

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

Append (cost=0.00..4,329.23 rows=26,283 width=13) (actual time=0.032..26.686 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. 25.549 25.549 ↑ 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.026..25.549 rows=26,282 loops=1)

  • Filter: (((pu_bccentity)::text = 'FCA'::text) AND ((pu_vstatus)::text = 'ACTIVE'::text))
Planning time : 87.135 ms
Execution time : 52,527.789 ms