explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0vi

Settings
# exclusive inclusive rows x rows loops node
1. 0.199 34.991 ↑ 7.0 1 1

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

2. 0.010 34.784 ↑ 7.0 1 1

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

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

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

4. 0.007 34.749 ↑ 7.0 1 1

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

5. 0.631 34.694 ↑ 7.0 1 1

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

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

Hash Join (cost=21,850.30..22,728.13 rows=7 width=44) (actual time=29.449..33.918 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.349 14.933 ↑ 1.8 7,316 1

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

  • Group Key: tb_f_energy_future_contract.ec_instrument_techid
8. 0.633 5.584 ↓ 1.0 13,895 1

Append (cost=0.00..10,705.71 rows=13,504 width=45) (actual time=1.587..5.584 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.410 4.951 ↓ 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.586..4.951 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.541 1.541 ↓ 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.541..1.541 rows=13,895 loops=1)

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

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

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

Append (cost=0.00..10,705.71 rows=13,504 width=50) (actual time=3.115..12.513 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. 8.765 11.825 ↓ 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.112..11.825 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.060 3.060 ↓ 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.060..3.060 rows=13,895 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.005 0.140 ↑ 7.0 1 1

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

19. 0.090 0.090 ↑ 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.053..0.090 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.045 0.045 ↓ 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.014..0.015 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.048 0.048 ↑ 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.047..0.048 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.010 0.023 ↑ 1.0 59 1

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

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

25. 0.007 0.008 ↓ 0.0 0 1

Append (cost=0.00..8.44 rows=2 width=38) (actual time=0.008..0.008 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 : 48.724 ms
Execution time : 35.764 ms