explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vh5VV

Settings
# exclusive inclusive rows x rows loops node
1. 97.919 274,656.719 ↓ 11,198.0 11,198 1

Nested Loop (cost=26,976.13..456,833.41 rows=1 width=672) (actual time=1,647.986..274,656.719 rows=11,198 loops=1)

  • Buffers: shared hit=907,725 read=353, temp read=2,725,985 written=214
2. 25.129 274,508.080 ↓ 1,585.0 12,680 1

Nested Loop Left Join (cost=26,975.99..456,831.93 rows=8 width=554) (actual time=1,647.947..274,508.080 rows=12,680 loops=1)

  • Buffers: shared hit=882,365 read=353, temp read=2,725,985 written=214
3. 42.350 274,444.911 ↓ 1,585.0 12,680 1

Nested Loop Left Join (cost=26,975.71..456,829.48 rows=8 width=538) (actual time=1,647.937..274,444.911 rows=12,680 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: 74,280
  • Buffers: shared hit=844,328 read=353, temp read=2,725,985 written=214
4. 50.064 274,301.121 ↓ 1,585.0 12,680 1

Nested Loop Left Join (cost=26,975.29..456,824.15 rows=8 width=543) (actual time=1,647.918..274,301.121 rows=12,680 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: 74,280
  • Buffers: shared hit=769,592 read=351, temp read=2,725,985 written=214
5. 57.335 274,149.617 ↓ 1,585.0 12,680 1

Nested Loop Left Join (cost=26,974.87..456,818.81 rows=8 width=535) (actual time=1,647.875..274,149.617 rows=12,680 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: 71,366
  • Buffers: shared hit=694,858 read=347, temp read=2,725,985 written=214
6. 159,793.762 273,610.442 ↓ 1,585.0 12,680 1

Nested Loop Left Join (cost=26,974.44..456,813.48 rows=8 width=527) (actual time=1,647.829..273,610.442 rows=12,680 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: 1,276,229,320
  • Buffers: shared hit=620,341 read=126, temp read=2,725,985 written=214
7. 18.526 2,854.000 ↓ 1,585.0 12,680 1

Nested Loop Left Join (cost=26,974.44..436,587.55 rows=8 width=541) (actual time=1,588.310..2,854.000 rows=12,680 loops=1)

  • Buffers: shared hit=619,490 read=126
8. 16.446 2,784.754 ↓ 1,585.0 12,680 1

Hash Join (cost=26,974.03..436,583.58 rows=8 width=533) (actual time=1,588.296..2,784.754 rows=12,680 loops=1)

  • Hash Cond: ((tn.instrument_currency)::text = (ccy.code)::text)
  • Buffers: shared hit=591,863 read=123
9. 26.386 2,768.279 ↓ 55.0 14,087 1

Nested Loop Left Join (cost=26,969.12..436,577.63 rows=256 width=537) (actual time=1,588.232..2,768.279 rows=14,087 loops=1)

  • Buffers: shared hit=591,857 read=123
10. 19.515 2,699.632 ↓ 55.0 14,087 1

Nested Loop Left Join (cost=26,968.70..436,450.58 rows=256 width=508) (actual time=1,588.218..2,699.632 rows=14,087 loops=1)

  • Buffers: shared hit=563,884 read=123
11. 24.308 2,440.638 ↓ 55.0 14,087 1

Nested Loop Left Join (cost=26,968.28..436,323.53 rows=256 width=500) (actual time=1,588.196..2,440.638 rows=14,087 loops=1)

  • Buffers: shared hit=526,099
12. 14.560 2,317.721 ↓ 55.0 14,087 1

Nested Loop Left Join (cost=26,967.86..436,185.51 rows=256 width=496) (actual time=1,588.179..2,317.721 rows=14,087 loops=1)

  • Buffers: shared hit=482,168
13. 30.661 2,232.726 ↓ 55.0 14,087 1

Nested Loop Left Join (cost=26,967.44..435,954.71 rows=256 width=493) (actual time=1,588.164..2,232.726 rows=14,087 loops=1)

  • Buffers: shared hit=425,802
14. 21.011 2,047.108 ↓ 55.0 14,087 1

Hash Join (cost=26,967.02..435,212.82 rows=256 width=254) (actual time=1,588.129..2,047.108 rows=14,087 loops=1)

  • Hash Cond: ((tn.sedol)::text = (smkoms.sedol)::text)
  • Buffers: shared hit=374,707
15. 1,943.386 1,943.386 ↑ 1.1 18,467 1

Seq Scan on oms_trade_notification tn (cost=0.00..408,166.54 rows=20,453 width=221) (actual time=1,505.373..1,943.386 rows=18,467 loops=1)

  • Filter: ((trade_date >= '2020-01-01'::date) AND (trade_date <= '2020-05-01'::date) AND (instrument_type = ANY ('{50,502}'::integer[])))
  • Rows Removed by Filter: 3,054,652
  • Buffers: shared hit=354,447
16. 1.705 82.711 ↓ 1.7 5,721 1

Hash (cost=26,924.97..26,924.97 rows=3,364 width=40) (actual time=82.711..82.711 rows=5,721 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 517kB
  • Buffers: shared hit=20,260
17. 20.535 81.006 ↓ 1.7 5,721 1

Hash Join (cost=18,031.43..26,924.97 rows=3,364 width=40) (actual time=39.584..81.006 rows=5,721 loops=1)

  • Hash Cond: (smkoms.instrument_id = inst.instrument_id)
  • Buffers: shared hit=20,260
18. 21.028 21.028 ↑ 1.0 269,125 1

Seq Scan on security_master_key smkoms (cost=0.00..5,832.25 rows=269,125 width=28) (actual time=0.006..21.028 rows=269,125 loops=1)

  • Buffers: shared hit=3,141
19. 0.811 39.443 ↓ 1.0 3,887 1

Hash (cost=17,983.34..17,983.34 rows=3,847 width=20) (actual time=39.443..39.443 rows=3,887 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 253kB
  • Buffers: shared hit=17,116
20. 38.632 38.632 ↓ 1.0 3,887 1

Seq Scan on instrument inst (cost=0.00..17,983.34 rows=3,847 width=20) (actual time=0.060..38.632 rows=3,887 loops=1)

  • Filter: ((lgim_asset_type_l2)::text = ANY ('{Government,Municipals}'::text[]))
  • Rows Removed by Filter: 65,500
  • Buffers: shared hit=17,116
21. 154.957 154.957 ↑ 1.0 1 14,087

Index Scan using idx_fi_tradeweb_trades_oms_trade_reference on fi_tradeweb_trades tw (cost=0.42..2.89 rows=1 width=261) (actual time=0.010..0.011 rows=1 loops=14,087)

  • Index Cond: ((oms_trade_reference)::text = (tn.external_trade_reference)::text)
  • Buffers: shared hit=51,095
22. 70.435 70.435 ↑ 1.0 1 14,087

Index Scan using pk__exchange_rate on exchange_rate fx (cost=0.42..0.89 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=14,087)

  • Index Cond: ((valuation_point = tn.trade_date) AND ((tn.instrument_currency)::text = (iso_currency_code)::text))
  • Buffers: shared hit=56,366
23. 98.609 98.609 ↑ 1.0 1 14,087

Index Scan using bond_settlement_date_pkey on bond_settlement_date bsd (cost=0.42..0.53 rows=1 width=21) (actual time=0.007..0.007 rows=1 loops=14,087)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (trade_date = tw.tradedate))
  • Buffers: shared hit=43,931
24. 239.479 239.479 ↓ 0.0 0 14,087

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.017..0.017 rows=0 loops=14,087)

  • 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=37,785 read=123
25. 42.261 42.261 ↓ 0.0 0 14,087

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.003..0.003 rows=0 loops=14,087)

  • 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=27,973
26. 0.004 0.029 ↑ 1.0 7 1

Hash (cost=4.82..4.82 rows=7 width=4) (actual time=0.029..0.029 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
27. 0.025 0.025 ↑ 1.0 7 1

Seq Scan on currency ccy (cost=0.00..4.82 rows=7 width=4) (actual time=0.011..0.025 rows=7 loops=1)

  • Filter: hard_currency
  • Rows Removed by Filter: 175
  • Buffers: shared hit=3
28. 50.720 50.720 ↓ 0.0 0 12,680

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=12,680)

  • 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=27,627 read=3
29. 110,949.076 110,962.680 ↑ 1.0 100,649 12,680

Materialize (cost=0.00..2,360.74 rows=100,649 width=7) (actual time=0.003..8.751 rows=100,649 loops=12,680)

  • Buffers: shared hit=851, temp read=2,725,985 written=214
30. 13.604 13.604 ↑ 1.0 100,649 1

Seq Scan on bond_forward_price bfwd_std (cost=0.00..1,857.49 rows=100,649 width=7) (actual time=0.004..13.604 rows=100,649 loops=1)

  • Buffers: shared hit=851
31. 481.840 481.840 ↓ 6.0 6 12,680

Index Scan using idx_bond_yield_yield on bond_yield y_mid (cost=0.42..0.65 rows=1 width=32) (actual time=0.034..0.038 rows=6 loops=12,680)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=74,517 read=221
32. 101.440 101.440 ↓ 6.0 6 12,680

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.008 rows=6 loops=12,680)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=74,734 read=4
33. 101.440 101.440 ↓ 6.0 6 12,680

Index Scan using idx_bond_yield_yield on bond_yield y_ask (cost=0.42..0.65 rows=1 width=32) (actual time=0.005..0.008 rows=6 loops=12,680)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=74,736 read=2
34. 38.040 38.040 ↑ 1.0 1 12,680

Index Scan using broker_pkey on broker b (cost=0.28..0.30 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=12,680)

  • Index Cond: (oms_no = tn.broker_no)
  • Buffers: shared hit=38,037
35. 50.720 50.720 ↑ 1.0 1 12,680

Index Scan using "PK_country" on country (cost=0.14..0.17 rows=1 width=3) (actual time=0.004..0.004 rows=1 loops=12,680)

  • Index Cond: ((code)::text = (inst.country_of_risk)::text)
  • Filter: developed
  • Rows Removed by Filter: 0
  • Buffers: shared hit=25,360
Planning time : 17.805 ms
Execution time : 274,660.537 ms