explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SXht

Settings
# exclusive inclusive rows x rows loops node
1. 482.560 3,182.839 ↑ 1.8 34,354 1

Hash Left Join (cost=136,909.03..1,223,821.87 rows=60,662 width=898) (actual time=262.795..3,182.839 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. 33.354 2,674.303 ↑ 1.8 34,354 1

Hash Left Join (cost=132,119.84..1,195,677.72 rows=60,662 width=896) (actual time=236.246..2,674.303 rows=34,354 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_sourcemovementcode)::numeric = t9.mc_movementcode)
3. 14.861 2,640.915 ↑ 1.8 34,354 1

Nested Loop Left Join (cost=132,117.52..1,195,117.30 rows=60,662 width=884) (actual time=236.174..2,640.915 rows=34,354 loops=1)

4. 50.373 2,179.452 ↑ 1.8 34,354 1

Nested Loop Left Join (cost=132,116.96..693,568.30 rows=60,662 width=889) (actual time=236.111..2,179.452 rows=34,354 loops=1)

5. 18.164 1,407.645 ↑ 1.8 34,354 1

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

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

Hash Join (cost=109,388.17..163,786.06 rows=60,662 width=849) (actual time=201.701..1,355.222 rows=34,354 loops=1)

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

Append (cost=0.00..48,422.45 rows=1,431,684 width=744) (actual time=0.022..833.956 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. 766.390 766.390 ↑ 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=0.020..766.390 rows=1,431,683 loops=1)

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

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

  • Buckets: 65536 Batches: 1 Memory Usage: 9827kB
11. 4.662 164.818 ↓ 1.0 61,642 1

Append (cost=0.00..108,629.90 rows=60,662 width=122) (actual time=0.059..164.818 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. 160.156 160.156 ↓ 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=0.059..160.156 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.552 34.259 ↓ 1,045.1 7,316 1

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

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

Hash Join (cost=21,850.30..22,728.13 rows=7 width=44) (actual time=28.364..32.707 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.204 15.665 ↑ 1.8 7,316 1

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

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
17. 0.653 5.461 ↓ 1.0 13,895 1

Append (cost=0.00..10,705.71 rows=13,504 width=45) (actual time=1.590..5.461 rows=13,895 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 = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
19. 3.277 4.806 ↓ 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.584..4.806 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.529 1.529 ↓ 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.529..1.529 rows=13,895 loops=1)

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

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

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

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

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

  • Filter: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
24. 6.937 9.521 ↓ 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=2.661..9.521 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. 2.584 2.584 ↓ 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=2.584..2.584 rows=13,895 loops=1)

  • Index Cond: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
26. 721.434 721.434 ↑ 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.021..0.021 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. 446.602 446.602 ↑ 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.012..0.013 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.021 0.034 ↑ 1.0 59 1

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

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

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

30. 6.077 25.976 ↑ 1.0 26,282 1

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

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

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

32. 0.000 0.000 ↓ 0.0 0 1

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

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

  • Filter: (((pu_bccentity)::text = 'FCA'::text) AND ((pu_vstatus)::text = 'ACTIVE'::text))
Planning time : 67.714 ms
Execution time : 3,187.072 ms