explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xqFT

Settings
# exclusive inclusive rows x rows loops node
1. 149.219 436,212.640 ↓ 11,198.0 11,198 1

Nested Loop Left Join (cost=465,309.38..991,872.14 rows=1 width=728) (actual time=4,935.993..436,212.640 rows=11,198 loops=1)

  • Buffers: shared hit=1,075,819, temp read=3,002,435 written=1,469
2. 36.740 436,018.629 ↓ 11,198.0 11,198 1

Nested Loop Left Join (cost=465,309.11..991,871.78 rows=1 width=561) (actual time=4,935.944..436,018.629 rows=11,198 loops=1)

  • Join Filter: (tw.settlementdate = bsd.settlement_date)
  • Rows Removed by Join Filter: 12
  • Buffers: shared hit=1,042,225, temp read=3,002,435 written=1,469
3. 33.462 435,836.315 ↓ 11,198.0 11,198 1

Nested Loop (cost=465,308.68..991,871.12 rows=1 width=566) (actual time=4,935.939..435,836.315 rows=11,198 loops=1)

  • Buffers: shared hit=967,506, temp read=3,002,435 written=1,469
4. 29.391 435,739.453 ↓ 1,408.9 12,680 1

Merge Join (cost=465,308.54..991,869.51 rows=9 width=569) (actual time=4,935.921..435,739.453 rows=12,680 loops=1)

  • Merge Cond: ((tn.instrument_currency)::text = (ccy.code)::text)
  • Buffers: shared hit=942,146, temp read=3,002,435 written=1,469
5. 39.456 435,704.274 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,303.62..1,104,044.11 rows=279 width=573) (actual time=4,585.786..435,704.274 rows=13,960 loops=1)

  • Join Filter: (tw.settlementdate = bsd.settlement_date)
  • Rows Removed by Join Filter: 30
  • Buffers: shared hit=942,143, temp read=3,002,435 written=1,469
6. 28.836 435,525.218 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,303.20..1,103,859.53 rows=279 width=565) (actual time=4,585.783..435,525.218 rows=13,960 loops=1)

  • Join Filter: (tw.settlementdate = bsd.settlement_date)
  • Rows Removed by Join Filter: 55
  • Buffers: shared hit=866,982, temp read=3,002,435 written=1,469
7. 57.900 435,328.862 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,302.77..1,103,674.95 rows=279 width=557) (actual time=4,585.778..435,328.862 rows=13,960 loops=1)

  • Join Filter: ((tw.settlementdate <> bsd.settlement_date) AND (y_fwd_ask.price = bfwd_ask.forward_price))
  • Rows Removed by Join Filter: 78,219
  • Buffers: shared hit=791,821, temp read=3,002,435 written=1,469
8. 64.406 435,145.322 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,302.35..1,103,490.36 rows=279 width=549) (actual time=4,585.774..435,145.322 rows=13,960 loops=1)

  • Join Filter: ((tw.settlementdate <> bsd.settlement_date) AND (y_fwd_bid.price = bfwd_bid.forward_price))
  • Rows Removed by Join Filter: 78,219
  • Buffers: shared hit=716,660, temp read=3,002,435 written=1,469
9. 72.195 434,941.316 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,301.93..1,103,305.78 rows=279 width=541) (actual time=4,585.770..434,941.316 rows=13,960 loops=1)

  • Join Filter: ((tw.settlementdate <> bsd.settlement_date) AND (y_fwd_mid.price = bfwd_mid.forward_price))
  • Rows Removed by Join Filter: 77,089
  • Buffers: shared hit=641,499, temp read=3,002,435 written=1,469
10. 250,873.990 434,617.841 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,301.51..1,103,121.20 rows=279 width=533) (actual time=4,585.765..434,617.841 rows=13,960 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,405,060,040
  • Buffers: shared hit=566,338, temp read=3,002,435 written=1,469
11. 23.870 5,530.491 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,301.51..469,187.99 rows=279 width=547) (actual time=4,510.465..5,530.491 rows=13,960 loops=1)

  • Buffers: shared hit=565,487, temp read=1,250 written=1,255
12. 31.567 5,436.821 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,301.09..469,049.54 rows=279 width=539) (actual time=4,510.460..5,436.821 rows=13,960 loops=1)

  • Buffers: shared hit=537,493, temp read=1,250 written=1,255
13. 29.875 5,335.454 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,300.67..468,911.09 rows=279 width=510) (actual time=4,510.455..5,335.454 rows=13,960 loops=1)

  • Buffers: shared hit=509,520, temp read=1,250 written=1,255
14. 40.456 5,179.939 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,300.25..468,772.65 rows=279 width=502) (actual time=4,510.450..5,179.939 rows=13,960 loops=1)

  • Buffers: shared hit=471,612, temp read=1,250 written=1,255
15. 52.148 5,013.843 ↓ 50.0 13,960 1

Nested Loop Left Join (cost=465,299.83..468,622.25 rows=279 width=498) (actual time=4,510.446..5,013.843 rows=13,960 loops=1)

  • Buffers: shared hit=427,681, temp read=1,250 written=1,255
16. 71.910 4,752.295 ↓ 50.0 13,960 1

Merge Left Join (cost=465,299.41..467,618.06 rows=279 width=259) (actual time=4,510.440..4,752.295 rows=13,960 loops=1)

  • Merge Cond: (((tn.instrument_currency)::text = (fx.iso_currency_code)::text) AND (tn.trade_date = fx.valuation_point))
  • Buffers: shared hit=376,883, temp read=1,250 written=1,255
17. 122.011 3,615.047 ↓ 50.0 13,960 1

Sort (cost=436,819.01..436,819.70 rows=279 width=256) (actual time=3,599.913..3,615.047 rows=13,960 loops=1)

  • Sort Key: tn.instrument_currency, tn.trade_date
  • Sort Method: external merge Disk: 3,952kB
  • Buffers: shared hit=375,301, temp read=495 written=495
18. 11.394 3,493.036 ↓ 50.5 14,087 1

Hash Join (cost=27,646.15..436,807.67 rows=279 width=256) (actual time=2,738.558..3,493.036 rows=14,087 loops=1)

  • Hash Cond: ((tn.sedol)::text = (smkoms.sedol)::text)
  • Buffers: shared hit=375,292
19. 3,332.844 3,332.844 ↑ 1.2 18,467 1

Seq Scan on oms_trade_notification tn (cost=0.00..409,076.37 rows=21,965 width=223) (actual time=2,589.674..3,332.844 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,058,877
  • Buffers: shared hit=355,029
20. 3.047 148.798 ↓ 1.7 5,728 1

Hash (cost=27,603.40..27,603.40 rows=3,420 width=40) (actual time=148.798..148.798 rows=5,728 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 517kB
  • Buffers: shared hit=20,260
21. 40.834 145.751 ↓ 1.7 5,728 1

Hash Join (cost=18,031.80..27,603.40 rows=3,420 width=40) (actual time=64.107..145.751 rows=5,728 loops=1)

  • Hash Cond: (smkoms.instrument_id = inst.instrument_id)
  • Buffers: shared hit=20,260
22. 40.981 40.981 ↑ 1.0 269,322 1

Seq Scan on security_master_key smkoms (cost=0.00..5,834.22 rows=269,322 width=28) (actual time=0.006..40.981 rows=269,322 loops=1)

  • Buffers: shared hit=3,141
23. 1.471 63.936 ↓ 1.0 3,893 1

Hash (cost=17,983.71..17,983.71 rows=3,847 width=20) (actual time=63.936..63.936 rows=3,893 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 254kB
  • Buffers: shared hit=17,116
24. 62.465 62.465 ↓ 1.0 3,893 1

Seq Scan on instrument inst (cost=0.00..17,983.71 rows=3,847 width=20) (actual time=0.086..62.465 rows=3,893 loops=1)

  • Filter: ((lgim_asset_type_l2)::text = ANY ('{Government,Municipals}'::text[]))
  • Rows Removed by Filter: 65,524
  • Buffers: shared hit=17,116
25. 32.183 1,065.338 ↑ 1.0 222,760 1

Materialize (cost=28,480.25..29,637.62 rows=231,475 width=15) (actual time=904.231..1,065.338 rows=222,760 loops=1)

  • Buffers: shared hit=1,582, temp read=755 written=760
26. 974.317 1,033.155 ↑ 1.1 209,698 1

Sort (cost=28,480.25..29,058.94 rows=231,475 width=15) (actual time=904.226..1,033.155 rows=209,698 loops=1)

  • Sort Key: fx.iso_currency_code, fx.valuation_point
  • Sort Method: external merge Disk: 6,048kB
  • Buffers: shared hit=1,582, temp read=755 written=760
27. 58.838 58.838 ↑ 1.0 231,475 1

Seq Scan on exchange_rate fx (cost=0.00..3,896.75 rows=231,475 width=15) (actual time=0.009..58.838 rows=231,475 loops=1)

  • Buffers: shared hit=1,582
28. 209.400 209.400 ↑ 1.0 1 13,960

Index Scan using idx_fi_tradeweb_trades_oms_trade_reference on fi_tradeweb_trades tw (cost=0.42..3.59 rows=1 width=261) (actual time=0.015..0.015 rows=1 loops=13,960)

  • Index Cond: ((oms_trade_reference)::text = (tn.external_trade_reference)::text)
  • Buffers: shared hit=50,798
29. 125.640 125.640 ↑ 1.0 1 13,960

Index Scan using bond_settlement_date_pkey on bond_settlement_date bsd (cost=0.42..0.53 rows=1 width=21) (actual time=0.009..0.009 rows=1 loops=13,960)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (trade_date = tw.tradedate))
  • Buffers: shared hit=43,931
30. 125.640 125.640 ↓ 0.0 0 13,960

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.009..0.009 rows=0 loops=13,960)

  • 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,908
31. 69.800 69.800 ↓ 0.0 0 13,960

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.005..0.005 rows=0 loops=13,960)

  • 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
32. 69.800 69.800 ↓ 0.0 0 13,960

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.005 rows=0 loops=13,960)

  • 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,994
33. 178,188.120 178,213.360 ↑ 1.0 100,649 13,960

Materialize (cost=0.00..2,360.74 rows=100,649 width=7) (actual time=0.003..12.766 rows=100,649 loops=13,960)

  • Buffers: shared hit=851, temp read=3,001,185 written=214
34. 25.240 25.240 ↑ 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.008..25.240 rows=100,649 loops=1)

  • Buffers: shared hit=851
35. 251.280 251.280 ↓ 6.0 6 13,960

Index Scan using idx_bond_yield_yield on bond_yield y_fwd_mid (cost=0.42..0.65 rows=1 width=32) (actual time=0.015..0.018 rows=6 loops=13,960)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=75,161
36. 139.600 139.600 ↓ 6.0 6 13,960

Index Scan using idx_bond_yield_yield on bond_yield y_fwd_bid (cost=0.42..0.65 rows=1 width=32) (actual time=0.007..0.010 rows=6 loops=13,960)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=75,161
37. 125.640 125.640 ↓ 6.0 6 13,960

Index Scan using idx_bond_yield_yield on bond_yield y_fwd_ask (cost=0.42..0.65 rows=1 width=32) (actual time=0.007..0.009 rows=6 loops=13,960)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Buffers: shared hit=75,161
38. 167.520 167.520 ↓ 0.0 0 13,960

Index Scan using idx_bond_yield_yield on bond_yield y_mid (cost=0.42..0.65 rows=1 width=32) (actual time=0.011..0.012 rows=0 loops=13,960)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Filter: (price = tw.comp_mid)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=75,161
39. 139.600 139.600 ↓ 0.0 0 13,960

Index Scan using idx_bond_yield_yield on bond_yield y_bid (cost=0.42..0.65 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=13,960)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Filter: (price = tw.comp_bid)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=75,161
40. 5.745 5.788 ↓ 1,439.1 10,074 1

Sort (cost=4.92..4.94 rows=7 width=4) (actual time=0.070..5.788 rows=10,074 loops=1)

  • Sort Key: ccy.code
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
41. 0.043 0.043 ↑ 1.0 7 1

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

  • Filter: hard_currency
  • Rows Removed by Filter: 175
  • Buffers: shared hit=3
42. 63.400 63.400 ↑ 1.0 1 12,680

Index Scan using "PK_country" on country (cost=0.14..0.17 rows=1 width=3) (actual time=0.005..0.005 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
43. 145.574 145.574 ↓ 0.0 0 11,198

Index Scan using idx_bond_yield_yield on bond_yield y_ask (cost=0.42..0.65 rows=1 width=32) (actual time=0.012..0.013 rows=0 loops=11,198)

  • Index Cond: (((isin)::text = (tw.isin)::text) AND (settlement_date = tw.settlementdate))
  • Filter: (price = tw.comp_ask)
  • Rows Removed by Filter: 7
  • Buffers: shared hit=74,719
44. 44.792 44.792 ↑ 1.0 1 11,198

Index Scan using broker_pkey on broker b (cost=0.28..0.30 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=11,198)

  • Index Cond: (oms_no = tn.broker_no)
  • Buffers: shared hit=33,594
Planning time : 38.728 ms
Execution time : 436,219.016 ms