explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uI8U

Settings
# exclusive inclusive rows x rows loops node
1. 331.955 823,156.125 ↓ 53.1 38,860 1

Hash Left Join (cost=26,993.58..2,046,324.29 rows=732 width=675) (actual time=985.821..823,156.125 rows=38,860 loops=1)

  • Hash Cond: (tn.broker_no = b.oms_no)
  • Buffers: shared hit=1,955,107, temp read=8,082,672 written=207
2. 175.672 822,823.938 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,966.03..2,046,251.90 rows=732 width=538) (actual time=985.544..822,823.938 rows=38,860 loops=1)

  • Join Filter: (((tw.settlementdate = bsd.settlement_date) AND (y_ask.price = tw.comp_ask)) OR ((tw.settlementdate <> bsd.settlement_date) AND (y_ask.price = bfwd_ask.forward_price)))
  • Rows Removed by Join Filter: 248,441
  • Buffers: shared hit=1,955,095, temp read=8,082,672 written=207
3. 148.434 822,337.386 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,965.61..2,045,764.83 rows=732 width=543) (actual time=985.518..822,337.386 rows=38,860 loops=1)

  • Join Filter: (((tw.settlementdate = bsd.settlement_date) AND (y_bid.price = tw.comp_bid)) OR ((tw.settlementdate <> bsd.settlement_date) AND (y_bid.price = bfwd_bid.forward_price)))
  • Rows Removed by Join Filter: 248,441
  • Buffers: shared hit=1,698,898, temp read=8,082,672 written=207
4. 192.521 821,839.212 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,965.19..2,045,277.75 rows=732 width=535) (actual time=985.489..821,839.212 rows=38,860 loops=1)

  • Join Filter: (((tw.settlementdate = bsd.settlement_date) AND (y_mid.price = tw.comp_mid)) OR ((tw.settlementdate <> bsd.settlement_date) AND (y_mid.price = bfwd_mid.forward_price)))
  • Rows Removed by Join Filter: 239,408
  • Buffers: shared hit=1,442,701, temp read=8,082,672 written=207
5. 489,525.909 820,947.211 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,964.76..2,044,790.67 rows=732 width=527) (actual time=985.439..820,947.211 rows=38,860 loops=1)

  • Join Filter: (((bfwd_bid.isin)::text = (tw.isin)::text) AND (bfwd_bid.trade_date = tw.tradedate) AND (bfwd_bid.settlement_date = tw.settlementdate) AND (bfwd_bid.forward_price = tw.price))
  • Rows Removed by Join Filter: 3,788,927,720
  • Buffers: shared hit=1,186,504, temp read=8,082,672 written=207
6. 57.086 4,414.402 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,964.76..436,889.96 rows=732 width=541) (actual time=948.535..4,414.402 rows=38,860 loops=1)

  • Buffers: shared hit=1,185,680
7. 63.176 4,201.876 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,964.35..436,527.31 rows=732 width=533) (actual time=948.524..4,201.876 rows=38,860 loops=1)

  • Buffers: shared hit=1,090,659
8. 63.396 3,983.260 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,963.93..436,164.67 rows=732 width=504) (actual time=948.512..3,983.260 rows=38,860 loops=1)

  • Buffers: shared hit=995,646
9. 78.642 3,608.984 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,963.51..435,802.02 rows=732 width=496) (actual time=948.495..3,608.984 rows=38,860 loops=1)

  • Buffers: shared hit=870,394
10. 58.266 3,219.462 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,963.09..435,406.80 rows=732 width=492) (actual time=948.475..3,219.462 rows=38,860 loops=1)

  • Buffers: shared hit=724,111
11. 127.411 2,966.896 ↓ 53.1 38,860 1

Nested Loop Left Join (cost=26,962.67..434,987.82 rows=732 width=493) (actual time=948.458..2,966.896 rows=38,860 loops=1)

  • Filter: (((tw.productgroup)::text = ANY ('{EUGV,CAN}'::text[])) OR (tn.external_trading_platform IS NULL))
  • Rows Removed by Filter: 14,881
  • Buffers: shared hit=568,446
12. 67.850 2,248.334 ↓ 54.1 53,741 1

Hash Join (cost=26,962.25..433,829.27 rows=994 width=254) (actual time=948.423..2,248.334 rows=53,741 loops=1)

  • Hash Cond: ((tn.sedol)::text = (smkoms.sedol)::text)
  • Buffers: shared hit=373,270
13. 2,097.772 2,097.772 ↑ 1.1 67,215 1

Seq Scan on oms_trade_notification tn (cost=0.00..406,574.20 rows=75,434 width=224) (actual time=865.608..2,097.772 rows=67,215 loops=1)

  • Filter: ((trade_date >= '2019-01-01'::date) AND (trade_date <= '2020-05-01'::date) AND (instrument_type = ANY ('{50,502}'::integer[])))
  • Rows Removed by Filter: 2,993,134
  • Buffers: shared hit=353,007
14. 1.629 82.712 ↓ 1.6 5,716 1

Hash (cost=26,918.01..26,918.01 rows=3,539 width=37) (actual time=82.712..82.712 rows=5,716 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 500kB
  • Buffers: shared hit=20,260
15. 20.530 81.083 ↓ 1.6 5,716 1

Hash Join (cost=18,032.09..26,918.01 rows=3,539 width=37) (actual time=39.669..81.083 rows=5,716 loops=1)

  • Hash Cond: (smkoms.instrument_id = inst.instrument_id)
  • Buffers: shared hit=20,260
16. 20.983 20.983 ↓ 1.0 268,792 1

Seq Scan on security_master_key smkoms (cost=0.00..5,827.84 rows=268,684 width=28) (actual time=0.004..20.983 rows=268,792 loops=1)

  • Buffers: shared hit=3,141
17. 0.749 39.570 ↑ 1.0 3,884 1

Hash (cost=17,981.81..17,981.81 rows=4,022 width=17) (actual time=39.570..39.570 rows=3,884 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 242kB
  • Buffers: shared hit=17,116
18. 38.821 38.821 ↑ 1.0 3,884 1

Seq Scan on instrument inst (cost=0.00..17,981.81 rows=4,022 width=17) (actual time=0.026..38.821 rows=3,884 loops=1)

  • Filter: ((lgim_asset_type_l2)::text = ANY ('{Government,Municipals}'::text[]))
  • Rows Removed by Filter: 65,381
  • Buffers: shared hit=17,116
19. 591.151 591.151 ↑ 1.0 1 53,741

Index Scan using idx_fi_tradeweb_trades_oms_trade_reference on fi_tradeweb_trades tw (cost=0.42..1.15 rows=1 width=261) (actual time=0.010..0.011 rows=1 loops=53,741)

  • Index Cond: ((oms_trade_reference)::text = (tn.external_trade_reference)::text)
  • Buffers: shared hit=195,176
20. 194.300 194.300 ↑ 1.0 1 38,860

Index Scan using pk__exchange_rate on exchange_rate fx (cost=0.42..0.56 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=38,860)

  • Index Cond: ((valuation_point = tn.trade_date) AND ((tn.instrument_currency)::text = (iso_currency_code)::text))
  • Buffers: shared hit=155,665
21. 310.880 310.880 ↑ 1.0 1 38,860

Index Scan using bond_settlement_date_pkey on bond_settlement_date bsd (cost=0.42..0.53 rows=1 width=21) (actual time=0.008..0.008 rows=1 loops=38,860)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (trade_date = tw.tradedate))
  • Buffers: shared hit=146,283
22. 310.880 310.880 ↓ 0.0 0 38,860

Index Scan using bond_forward_price_pkey on bond_forward_price bfwd_mid (cost=0.42..0.49 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=38,860)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (trade_date = tw.tradedate) AND (settlement_date = tw.settlementdate) AND (price = tw.comp_mid))
  • Buffers: shared hit=125,252
23. 155.440 155.440 ↓ 0.0 0 38,860

Index Scan using bond_forward_price_pkey on bond_forward_price bfwd_bid (cost=0.42..0.49 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=38,860)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (trade_date = tw.tradedate) AND (settlement_date = tw.settlementdate) AND (price = tw.comp_bid))
  • Buffers: shared hit=95,013
24. 155.440 155.440 ↓ 0.0 0 38,860

Index Scan using bond_forward_price_pkey on bond_forward_price bfwd_ask (cost=0.42..0.49 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=38,860)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (trade_date = tw.tradedate) AND (settlement_date = tw.settlementdate) AND (price = tw.comp_ask))
  • Buffers: shared hit=95,021
25. 326,994.874 327,006.900 ↑ 1.0 97,502 38,860

Materialize (cost=0.00..2,286.53 rows=97,502 width=7) (actual time=0.003..8.415 rows=97,502 loops=38,860)

  • Buffers: shared hit=824, temp read=8,082,672 written=207
26. 12.026 12.026 ↑ 1.0 97,502 1

Seq Scan on bond_forward_price bfwd_std (cost=0.00..1,799.02 rows=97,502 width=7) (actual time=0.006..12.026 rows=97,502 loops=1)

  • Buffers: shared hit=824
27. 699.480 699.480 ↓ 7.0 7 38,860

Index Scan using idx_bond_yield_yield on bond_yield y_mid (cost=0.42..0.65 rows=1 width=32) (actual time=0.014..0.018 rows=7 loops=38,860)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=256,197
28. 349.740 349.740 ↓ 7.0 7 38,860

Index Scan using idx_bond_yield_yield on bond_yield y_bid (cost=0.42..0.65 rows=1 width=32) (actual time=0.006..0.009 rows=7 loops=38,860)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=256,197
29. 310.880 310.880 ↓ 7.0 7 38,860

Index Scan using idx_bond_yield_yield on bond_yield y_ask (cost=0.42..0.65 rows=1 width=32) (actual time=0.006..0.008 rows=7 loops=38,860)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=256,197
30. 0.144 0.232 ↑ 1.0 691 1

Hash (cost=18.91..18.91 rows=691 width=24) (actual time=0.232..0.232 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=12
31. 0.088 0.088 ↑ 1.0 691 1

Seq Scan on broker b (cost=0.00..18.91 rows=691 width=24) (actual time=0.005..0.088 rows=691 loops=1)

  • Buffers: shared hit=12
Planning time : 14.628 ms
Execution time : 823,166.189 ms