explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wz3m

Settings
# exclusive inclusive rows x rows loops node
1. 244.196 4,133.612 ↓ 119,394.0 119,394 1

Nested Loop Left Join (cost=350,335.28..416,109.02 rows=1 width=619) (actual time=2,129.439..4,133.612 rows=119,394 loops=1)

  • Filter: (oms_trade_notification.order_no IS NULL)
  • Rows Removed by Filter: 182203
  • Buffers: shared hit=1050879, temp read=12825 written=12811
2. 65.486 3,517.311 ↓ 164.3 124,035 1

Hash Join (cost=350,334.85..413,107.67 rows=755 width=465) (actual time=2,129.396..3,517.311 rows=124,035 loops=1)

  • Hash Cond: (s.id = sgd.staff_id)
  • Buffers: shared hit=521004, temp read=12825 written=12811
3. 87.426 3,451.782 ↑ 1.1 161,283 1

Hash Join (cost=350,332.31..412,459.62 rows=170,198 width=469) (actual time=2,129.326..3,451.782 rows=161,283 loops=1)

  • Hash Cond: ((o.confirmed_by_staff_code)::text = (s.staff_code)::text)
  • Buffers: shared hit=520999, temp read=12825 written=12811
4. 99.602 3,363.897 ↑ 1.1 161,304 1

Hash Left Join (cost=350,285.94..410,073.03 rows=170,198 width=465) (actual time=2,128.833..3,363.897 rows=161,304 loops=1)

  • Hash Cond: (o.deal_no = fima.oms_deal_no)
  • Buffers: shared hit=520976, temp read=12825 written=12811
5. 66.799 3,234.321 ↑ 1.1 161,303 1

Hash Left Join (cost=346,719.64..405,624.42 rows=170,198 width=411) (actual time=2,098.736..3,234.321 rows=161,303 loops=1)

  • Hash Cond: (o.security_type_no = st.oms_no)
  • Buffers: shared hit=514828, temp read=12825 written=12811
6. 208.798 3,167.442 ↑ 1.1 161,303 1

Hash Left Join (cost=346,713.77..403,622.24 rows=170,198 width=380) (actual time=2,098.626..3,167.442 rows=161,303 loops=1)

  • Hash Cond: ((sk.isin)::text = (inst.isin)::text)
  • Buffers: shared hit=514826, temp read=12825 written=12811
7. 62.506 2,865.494 ↑ 1.1 161,260 1

Hash Left Join (cost=338,403.57..379,910.17 rows=170,198 width=253) (actual time=2,005.040..2,865.494 rows=161,260 loops=1)

  • Hash Cond: (o.order_reason_no = orn.reason_no)
  • Buffers: shared hit=502889, temp read=10053 written=10041
8. 58.807 2,802.961 ↑ 1.1 161,260 1

Hash Left Join (cost=338,401.17..377,622.18 rows=170,198 width=234) (actual time=2,004.998..2,802.961 rows=161,260 loops=1)

  • Hash Cond: (o.broker_no = b.oms_no)
  • Buffers: shared hit=502888, temp read=10053 written=10041
9. 243.528 2,743.885 ↑ 1.1 161,260 1

Hash Left Join (cost=338,379.10..375,259.89 rows=170,198 width=214) (actual time=2,004.710..2,743.885 rows=161,260 loops=1)

  • Hash Cond: ((o.sedol)::text = (sk.sedol)::text)
  • Buffers: shared hit=502881, temp read=10053 written=10041
10. 127.131 2,401.549 ↑ 1.1 161,260 1

Hash Left Join (cost=330,151.95..353,984.52 rows=170,198 width=197) (actual time=1,905.566..2,401.549 rows=161,260 loops=1)

  • Hash Cond: ((o.external_trade_reference)::text = (tw.oms_trade_reference)::text)
  • Buffers: shared hit=500148, temp read=5985 written=5979
11. 67.648 2,216.337 ↑ 1.1 161,260 1

Hash Left Join (cost=321,470.54..334,084.16 rows=170,198 width=207) (actual time=1,847.245..2,216.337 rows=161,260 loops=1)

  • Hash Cond: (o.deal_no = m.deal_no)
  • Buffers: shared hit=497480, temp read=4020 written=4020
12. 68.369 2,127.879 ↑ 1.1 161,219 1

Hash Left Join (cost=318,223.98..327,391.89 rows=170,198 width=207) (actual time=1,826.411..2,127.879 rows=161,219 loops=1)

  • Hash Cond: ((o.transaction_type)::text = (tt.code)::text)
  • Buffers: shared hit=494330, temp read=4020 written=4020
13. 137.680 2,059.499 ↑ 1.1 161,219 1

Merge Right Join (cost=318,222.86..325,050.56 rows=170,198 width=205) (actual time=1,826.375..2,059.499 rows=161,219 loops=1)

  • Merge Cond: ((fx.valuation_point = o.trade_date) AND ((fx.iso_currency_code)::text = (o.instrument_currency)::text))
  • Buffers: shared hit=494329, temp read=4020 written=4020
14. 44.300 44.300 ↑ 1.0 202,765 1

Index Scan using pk__exchange_rate on exchange_rate fx (cost=0.42..8,761.96 rows=202,765 width=15) (actual time=0.007..44.300 rows=202,765 loops=1)

  • Buffers: shared hit=2183
15. 37.424 1,877.519 ↑ 1.1 161,219 1

Materialize (cost=312,271.12..313,122.11 rows=170,198 width=198) (actual time=1,792.732..1,877.519 rows=161,219 loops=1)

  • Buffers: shared hit=492146, temp read=4020 written=4020
16. 507.400 1,840.095 ↑ 1.1 161,219 1

Sort (cost=312,271.12..312,696.62 rows=170,198 width=198) (actual time=1,792.728..1,840.095 rows=161,219 loops=1)

  • Sort Key: o.trade_date, o.instrument_currency
  • Sort Method: external merge Disk: 32152kB
  • Buffers: shared hit=492146, temp read=4020 written=4020
17. 1,332.695 1,332.695 ↑ 1.1 161,219 1

Seq Scan on oms_trade_notification o (cost=0.00..281,194.59 rows=170,198 width=198) (actual time=0.144..1,332.695 rows=161,219 loops=1)

  • Filter: ((deal_status <> 7) AND (instrument_type = ANY ('{1504,502,60}'::integer[])))
  • Rows Removed by Filter: 1988531
  • Buffers: shared hit=492137
18. 0.006 0.011 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=6) (actual time=0.011..0.011 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
19. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on transaction_type tt (cost=0.00..1.05 rows=5 width=6) (actual time=0.003..0.005 rows=5 loops=1)

  • Buffers: shared hit=1
20. 5.490 20.810 ↓ 1.0 29,202 1

Hash (cost=2,883.65..2,883.65 rows=29,033 width=4) (actual time=20.810..20.810 rows=29,202 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1283kB
  • Buffers: shared hit=3150
21. 15.320 15.320 ↓ 1.0 29,202 1

Seq Scan on oms_marketaxess_tca m (cost=0.00..2,883.65 rows=29,033 width=4) (actual time=0.475..15.320 rows=29,202 loops=1)

  • Filter: ((analysis_to)::text = 'CompositeMid'::text)
  • Rows Removed by Filter: 72370
  • Buffers: shared hit=3150
22. 33.946 58.081 ↑ 1.0 133,174 1

Hash (cost=6,235.74..6,235.74 rows=133,174 width=22) (actual time=58.081..58.081 rows=133,174 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2302kB
  • Buffers: shared hit=2668, temp written=518
23. 24.135 24.135 ↑ 1.0 133,174 1

Index Only Scan using idx_fi_tradeweb_trades_oms_trade_reference on fi_tradeweb_trades tw (cost=0.42..6,235.74 rows=133,174 width=22) (actual time=0.028..24.135 rows=133,174 loops=1)

  • Heap Fetches: 2984
  • Buffers: shared hit=2668
24. 59.052 98.808 ↑ 1.0 238,851 1

Hash (cost=3,841.51..3,841.51 rows=238,851 width=24) (actual time=98.808..98.808 rows=238,851 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3327kB
  • Buffers: shared hit=2733, temp written=786
25. 39.756 39.756 ↑ 1.0 238,851 1

Seq Scan on security_master_key sk (cost=0.00..3,841.51 rows=238,851 width=24) (actual time=0.119..39.756 rows=238,851 loops=1)

  • Buffers: shared hit=2733
26. 0.146 0.269 ↑ 1.0 670 1

Hash (cost=13.70..13.70 rows=670 width=24) (actual time=0.269..0.269 rows=670 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=7
27. 0.123 0.123 ↑ 1.0 670 1

Seq Scan on broker b (cost=0.00..13.70 rows=670 width=24) (actual time=0.004..0.123 rows=670 loops=1)

  • Buffers: shared hit=7
28. 0.019 0.027 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=23) (actual time=0.027..0.027 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
29. 0.008 0.008 ↑ 1.0 62 1

Seq Scan on order_reason_no orn (cost=0.00..1.62 rows=62 width=23) (actual time=0.003..0.008 rows=62 loops=1)

  • Buffers: shared hit=1
30. 26.716 93.150 ↑ 1.1 39,425 1

Hash (cost=6,929.37..6,929.37 rows=41,827 width=139) (actual time=93.150..93.150 rows=39,425 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2960kB
  • Buffers: shared hit=11937, temp written=309
31. 56.563 66.434 ↑ 1.0 41,827 1

Hash Left Join (cost=9.62..6,929.37 rows=41,827 width=139) (actual time=0.209..66.434 rows=41,827 loops=1)

  • Hash Cond: ((inst.country_of_risk)::text = (cem.code)::text)
  • Buffers: shared hit=11937
32. 9.767 9.767 ↑ 1.0 41,827 1

Seq Scan on instrument inst (cost=0.00..6,463.27 rows=41,827 width=141) (actual time=0.088..9.767 rows=41,827 loops=1)

  • Buffers: shared hit=11933
33. 0.061 0.104 ↑ 1.0 250 1

Hash (cost=6.50..6.50 rows=250 width=4) (actual time=0.104..0.104 rows=250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=4
34. 0.043 0.043 ↑ 1.0 250 1

Seq Scan on country cem (cost=0.00..6.50 rows=250 width=4) (actual time=0.005..0.043 rows=250 loops=1)

  • Buffers: shared hit=4
35. 0.048 0.080 ↑ 1.0 172 1

Hash (cost=3.72..3.72 rows=172 width=39) (actual time=0.080..0.080 rows=172 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=2
36. 0.032 0.032 ↑ 1.0 172 1

Seq Scan on security_type st (cost=0.00..3.72 rows=172 width=39) (actual time=0.007..0.032 rows=172 loops=1)

  • Buffers: shared hit=2
37. 6.373 29.974 ↓ 1.1 23,283 1

Hash (cost=3,293.91..3,293.91 rows=21,791 width=58) (actual time=29.974..29.974 rows=23,283 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1931kB
  • Buffers: shared hit=6148
38. 23.601 23.601 ↓ 1.1 23,300 1

Seq Scan on fi_marketaxess_trades fima (cost=0.00..3,293.91 rows=21,791 width=58) (actual time=0.107..23.601 rows=23,300 loops=1)

  • Buffers: shared hit=6148
39. 0.264 0.459 ↑ 1.0 1,172 1

Hash (cost=31.72..31.72 rows=1,172 width=12) (actual time=0.459..0.459 rows=1,172 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 67kB
  • Buffers: shared hit=20
40. 0.195 0.195 ↑ 1.0 1,172 1

Seq Scan on staff s (cost=0.00..31.72 rows=1,172 width=12) (actual time=0.002..0.195 rows=1,172 loops=1)

  • Buffers: shared hit=20
41. 0.006 0.043 ↑ 1.0 5 1

Hash (cost=2.48..2.48 rows=5 width=4) (actual time=0.043..0.043 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
42. 0.019 0.037 ↑ 1.0 5 1

Hash Join (cost=1.07..2.48 rows=5 width=4) (actual time=0.033..0.037 rows=5 loops=1)

  • Hash Cond: (sgd.gtt_desk_id = gd.id)
  • Buffers: shared hit=2
43. 0.007 0.007 ↑ 1.0 26 1

Seq Scan on staff_gtt_desk sgd (cost=0.00..1.26 rows=26 width=8) (actual time=0.004..0.007 rows=26 loops=1)

  • Buffers: shared hit=1
44. 0.003 0.011 ↑ 1.0 1 1

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
45. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on gtt_desk gd (cost=0.00..1.06 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Filter: ((name)::text = 'Credit'::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
46. 372.105 372.105 ↑ 1.0 1 124,035

Index Scan using idx_oms_trade_notification_cancel on oms_trade_notification (cost=0.43..3.97 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=124,035)

  • Index Cond: ((o.deal_no = deal_no) AND (deal_no IS NOT NULL) AND (deal_status = 7))
  • Buffers: shared hit=529875
Planning time : 11.561 ms
Execution time : 4,156.143 ms