explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w4Iu

Settings
# exclusive inclusive rows x rows loops node
1. 0.201 38.376 ↑ 7.0 1 1

Nested Loop Left Join (cost=22,029.64..23,079.25 rows=7 width=898) (actual time=38.373..38.376 rows=1 loops=1)

2. 0.011 38.168 ↑ 7.0 1 1

Hash Left Join (cost=22,029.64..23,018.05 rows=7 width=896) (actual time=38.165..38.168 rows=1 loops=1)

  • Hash Cond: ((tb_f_transaction_fca_20191128.tr_sourcemovementcode)::numeric = t9.mc_movementcode)
3. 0.002 38.120 ↑ 7.0 1 1

Nested Loop Left Join (cost=22,027.31..23,015.66 rows=7 width=884) (actual time=38.117..38.120 rows=1 loops=1)

4. 0.006 38.118 ↑ 7.0 1 1

Nested Loop Left Join (cost=22,026.75..22,964.26 rows=7 width=889) (actual time=38.115..38.118 rows=1 loops=1)

5. 0.606 38.061 ↑ 7.0 1 1

Hash Right Join (cost=22,026.19..22,904.11 rows=7 width=854) (actual time=38.059..38.061 rows=1 loops=1)

  • Hash Cond: ((a.ec_instrument_techid)::text = (tb_f_transaction_fca_20191128.tr_instrument_techid)::text)
6. 3.130 37.294 ↓ 1,045.1 7,316 1

Hash Join (cost=21,850.30..22,728.13 rows=7 width=44) (actual time=32.766..37.294 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_future_ (...)
7. 9.558 15.297 ↑ 1.8 7,316 1

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

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
8. 0.678 5.739 ↓ 1.0 13,895 1

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

9. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text) AND ((ec_vstatus)::text = 'ACTIVE'::text))
10. 3.505 5.061 ↓ 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.601..5.061 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
11. 1.556 1.556 ↓ 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.556..1.556 rows=13,895 loops=1)

  • Index Cond: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
12. 3.315 18.867 ↓ 1.0 13,895 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 1255kB
13. 0.765 15.552 ↓ 1.0 13,895 1

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

14. 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))
15. 11.384 14.786 ↓ 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=3.460..14.786 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
16. 3.402 3.402 ↓ 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=3.402..3.402 rows=13,895 loops=1)

  • Index Cond: ((ec_businessdate = '20191128'::numeric) AND ((ec_bccentity)::text = 'FCA'::text))
17. 0.004 0.161 ↑ 7.0 1 1

Hash (cost=175.80..175.80 rows=7 width=849) (actual time=0.161..0.161 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.006 0.157 ↑ 7.0 1 1

Nested Loop (cost=0.98..175.80 rows=7 width=849) (actual time=0.093..0.157 rows=1 loops=1)

19. 0.103 0.103 ↑ 5.3 3 1

Index Scan using ix_tr_clientaccinfo_clientid01_techid01_fca_20191128 on tb_f_transaction_fca_20191128 (cost=0.56..40.40 rows=16 width=122) (actual time=0.059..0.103 rows=3 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[])) AND ((tr_clientaccinfo_techid)::text = ' (...)
  • Filter: ((((tr_runtype)::text = 'EOD'::text) OR (tr_runtype IS NULL)) AND ((tr_vstatus)::text = 'ACTIVE'::text))
20. 0.048 0.048 ↓ 0.0 0 3

Index Scan using ix_fee_movement_dummykey01_fca_20191128 on tb_f_fee_movement_fca_20191128 (cost=0.43..8.45 rows=1 width=744) (actual time=0.016..0.016 rows=0 loops=3)

  • Index Cond: ((fm_businessdate = 20191128) AND ((fm_bccentity)::text = 'FCA'::text) AND (fm_dummykey = tb_f_transaction_fca_20191128.tr_dummykey))
  • Filter: (fm_vstatus = 'A'::bpchar)
21. 0.051 0.051 ↑ 1.0 1 1

Index Scan using ix_ak_d_instrument2_01_fca on tb_d_instrument2_fca t1 (cost=0.56..8.58 rows=1 width=96) (actual time=0.050..0.051 rows=1 loops=1)

  • Index Cond: (((tb_f_transaction_fca_20191128.tr_instrument_techid)::text = (in_instrument_techid)::text) AND ((in_bccentity)::text = 'FCA'::text))
  • Filter: ((in_vstatus)::text = 'ACTIVE'::text)
22. 0.000 0.000 ↓ 0.0 0 1

Index Scan using ix_ak_d_instrument2_01_fca on tb_d_instrument2_fca ulv (cost=0.56..7.33 rows=1 width=47) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (((in_instrument_techid)::text = (t1.in_instrument_techidulv)::text) AND ((in_bccentity)::text = 'FCA'::text))
  • Filter: ((in_vstatus)::text = 'ACTIVE'::text)
23. 0.018 0.037 ↑ 1.0 59 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 0.019 0.019 ↑ 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.019 rows=59 loops=1)

25. 0.006 0.007 ↓ 0.0 0 1

Append (cost=0.00..8.44 rows=2 width=38) (actual time=0.007..0.007 rows=0 loops=1)

26. 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) AND ((t1.in_symbollocal)::text = (pu_symbollocal)::text) AND ((tb_f_transaction_fca_20191128.tr_placeoftradelocal)::text = (pu_placeoftradelocal)::text) A (...)
27. 0.001 0.001 ↓ 0.0 0 1

Index Scan using ix_d_productulv_fca02 on tb_d_productulv_fca ins_ulv_1 (cost=0.42..8.44 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (((tb_f_transaction_fca_20191128.tr_placeoftradelocal)::text = (pu_placeoftradelocal)::text) AND ((pu_bccentity)::text = 'FCA'::text) AND ((t1.in_symbollocal)::text = (pu_symbollocal)::text) AND (CASE WHEN ((t1.in_instrumenttype): (...)
  • Filter: ((pu_vstatus)::text = 'ACTIVE'::text)
Planning time : 45.033 ms
Execution time : 39.112 ms