explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C5pZ

Settings
# exclusive inclusive rows x rows loops node
1. 554.343 3,433.723 ↑ 1.2 34,354 1

Hash Left Join (cost=162,417.65..942,065.52 rows=42,550 width=898) (actual time=435.809..3,433.723 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. 34.135 2,851.256 ↑ 1.2 34,354 1

Hash Left Join (cost=157,628.47..920,894.52 rows=42,550 width=896) (actual time=407.422..2,851.256 rows=34,354 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_sourcemovementcode)::numeric = t9.mc_movementcode)
3. 12.954 2,817.091 ↑ 1.2 34,354 1

Nested Loop Left Join (cost=157,626.14..920,500.74 rows=42,550 width=884) (actual time=407.374..2,817.091 rows=34,354 loops=1)

4. 53.689 2,357.535 ↑ 1.2 34,354 1

Nested Loop Left Join (cost=157,625.58..568,700.43 rows=42,550 width=889) (actual time=407.341..2,357.535 rows=34,354 loops=1)

5. 17.949 1,582.412 ↑ 1.2 34,354 1

Hash Left Join (cost=157,625.02..212,001.56 rows=42,550 width=854) (actual time=407.291..1,582.412 rows=34,354 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_201911.tr_instrument_techid)::text = (a.ec_instrument_techid)::text)
6. 319.991 1,528.691 ↑ 1.2 34,354 1

Hash Join (cost=134,896.80..189,113.57 rows=42,550 width=849) (actual time=371.499..1,528.691 rows=34,354 loops=1)

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

Append (cost=0.00..48,422.45 rows=1,431,684 width=744) (actual time=0.019..850.237 rows=1,431,683 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 = 20191128) AND (fm_vstatus = 'A'::bpchar))
9. 777.850 777.850 ↑ 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.017..777.850 rows=1,431,683 loops=1)

  • Filter: (((fm_bccentity)::text = 'FCA'::text) AND (fm_businessdate = 20191128) AND (fm_vstatus = 'A'::bpchar))
10. 25.006 358.463 ↓ 1.4 61,685 1

Hash (cost=134,364.92..134,364.92 rows=42,550 width=122) (actual time=358.463..358.463 rows=61,685 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 9833kB
11. 5.220 333.457 ↓ 1.4 61,685 1

Append (cost=0.00..134,364.92 rows=42,550 width=122) (actual time=7.374..333.457 rows=61,685 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 = '20191128'::numeric) AND ((tr_vstatus)::text = 'ACTIVE'::text) AND ((tr_ (...)
13. 328.237 328.237 ↓ 1.4 61,685 1

Index Scan using ix_tr_clientaccinfo_clientid01_techid01_fca_20191128 on tb_f_transaction_fca_20191128 (cost=0.56..134,364.92 rows=42,549 width=122) (actual time=7.374..328.237 rows=61,685 loops=1)

  • Index Cond: ((tr_businessdate = '20191128'::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.625 35.772 ↓ 1,045.1 7,316 1

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

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

Hash Join (cost=21,850.30..22,728.13 rows=7 width=44) (actual time=28.758..34.147 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. 9.736 15.230 ↑ 1.8 7,316 1

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

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
17. 0.691 5.494 ↓ 1.0 13,895 1

Append (cost=0.00..10,705.71 rows=13,504 width=45) (actual time=1.583..5.494 rows=13,895 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 = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
19. 3.277 4.802 ↓ 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.582..4.802 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.525 1.525 ↓ 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.525..1.525 rows=13,895 loops=1)

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

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

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

Append (cost=0.00..10,705.71 rows=13,504 width=50) (actual time=2.697..10.954 rows=13,895 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 = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
24. 7.614 10.254 ↓ 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.696..10.254 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.640 2.640 ↓ 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.640..2.640 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.37 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.016 0.030 ↑ 1.0 59 1

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

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

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

30. 6.091 28.124 ↑ 1.0 26,282 1

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

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

Append (cost=0.00..4,329.23 rows=26,283 width=13) (actual time=0.016..22.033 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. 20.792 20.792 ↑ 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.014..20.792 rows=26,282 loops=1)

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