explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sbO1

Settings
# exclusive inclusive rows x rows loops node
1. 3,227.756 13,644.976 ↓ 1.0 397,923 1

Hash Left Join (cost=577,301.59..2,792,783.57 rows=382,228 width=832) (actual time=3,665.059..13,644.976 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c.code)::text)
  • Buffers: shared hit=2107721, temp read=49107 written=49039
2. 176.183 10,417.126 ↓ 1.0 397,923 1

Hash Left Join (cost=577,291.96..2,759,132.78 rows=382,228 width=533) (actual time=3,664.912..10,417.126 rows=397,923 loops=1)

  • Hash Cond: (el.trade_flag_no = otf.trade_flag_no)
  • Buffers: shared hit=2107717, temp read=49107 written=49039
3. 1,446.257 10,240.693 ↓ 1.0 397,923 1

Hash Left Join (cost=577,276.21..2,753,863.17 rows=382,228 width=522) (actual time=3,664.642..10,240.693 rows=397,923 loops=1)

  • Hash Cond: ((oms.deal_no = el.deal_no) AND (oms.order_line_id = el.order_line_id))
  • Buffers: shared hit=2107713, temp read=49107 written=49039
4. 246.215 8,086.929 ↓ 1.0 397,923 1

Hash Left Join (cost=489,658.31..2,606,077.55 rows=382,228 width=522) (actual time=2,955.865..8,086.929 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=2055520, temp read=19633 written=19627
5. 250.252 7,840.266 ↓ 1.0 397,923 1

Hash Left Join (cost=489,605.91..2,603,917.80 rows=382,228 width=490) (actual time=2,955.403..7,840.266 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=2055486, temp read=19633 written=19627
6. 218.245 7,589.543 ↓ 1.0 397,923 1

Merge Left Join (cost=489,552.38..2,601,751.52 rows=382,228 width=458) (actual time=2,954.918..7,589.543 rows=397,923 loops=1)

  • Merge Cond: (oms.trade_date = exchange_rate_1.valuation_point)
  • Buffers: shared hit=2055456, temp read=19633 written=19627
7. 234.249 7,303.754 ↓ 1.0 397,923 1

Merge Left Join (cost=484,864.75..2,592,322.37 rows=382,228 width=451) (actual time=2,934.728..7,303.754 rows=397,923 loops=1)

  • Merge Cond: (oms.trade_date = exchange_rate.valuation_point)
  • Buffers: shared hit=2054002, temp read=19633 written=19627
8. 108.758 7,005.434 ↓ 1.0 397,923 1

Nested Loop Left Join (cost=480,177.12..2,582,893.21 rows=382,228 width=444) (actual time=2,914.379..7,005.434 rows=397,923 loops=1)

  • Buffers: shared hit=2052548, temp read=19633 written=19627
9. 334.052 3,728.244 ↓ 1.0 396,054 1

Merge Left Join (cost=480,176.57..487,138.17 rows=382,228 width=147) (actual time=2,914.314..3,728.244 rows=396,054 loops=1)

  • Merge Cond: ((oms.trade_date = fx.valuation_point) AND ((oms.instrument_currency)::text = (fx.iso_currency_code)::text))
  • Buffers: shared hit=70141, temp read=19633 written=19627
10. 1,608.426 2,966.265 ↓ 1.0 396,054 1

Sort (cost=452,643.57..453,599.14 rows=382,228 width=144) (actual time=2,508.738..2,966.265 rows=396,054 loops=1)

  • Sort Key: oms.trade_date, oms.instrument_currency
  • Sort Method: external merge Disk: 65584kB
  • Buffers: shared hit=68687, temp read=18926 written=18920
11. 118.824 1,357.839 ↓ 1.0 396,054 1

Hash Left Join (cost=23,722.48..362,330.25 rows=382,228 width=144) (actual time=204.736..1,357.839 rows=396,054 loops=1)

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=68687, temp read=5728 written=5722
12. 389.474 1,238.339 ↓ 1.0 396,054 1

Hash Left Join (cost=23,657.89..357,010.02 rows=382,228 width=128) (actual time=204.046..1,238.339 rows=396,054 loops=1)

  • Hash Cond: ((oms.sedol)::text = (sm.sedol)::text)
  • Buffers: shared hit=68663, temp read=5728 written=5722
13. 652.874 754.805 ↓ 1.0 396,054 1

Bitmap Heap Scan on oms_trade_notification_esb oms (cost=13,354.74..327,279.24 rows=382,228 width=112) (actual time=109.432..754.805 rows=396,054 loops=1)

  • Recheck Cond: (trade_date > '2019-06-01'::date)
  • Rows Removed by Index Recheck: 19137
  • Filter: (instrument_type = ANY ('{10,2509}'::integer[]))
  • Rows Removed by Filter: 228691
  • Heap Blocks: exact=35785 lossy=26724
  • Buffers: shared hit=65522
14. 101.931 101.931 ↓ 1.3 787,719 1

Bitmap Index Scan on oms_trade_notification_esb_currency (cost=0.00..13,259.18 rows=624,900 width=0) (actual time=101.931..101.931 rows=787,719 loops=1)

  • Index Cond: (trade_date > '2019-06-01'::date)
  • Buffers: shared hit=3013
15. 53.524 94.060 ↓ 1.0 252,732 1

Hash (cost=5,666.40..5,666.40 rows=252,540 width=23) (actual time=94.060..94.060 rows=252,732 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3247kB
  • Buffers: shared hit=3141, temp written=741
16. 40.536 40.536 ↓ 1.0 252,732 1

Seq Scan on security_master_key sm (cost=0.00..5,666.40 rows=252,540 width=23) (actual time=0.010..40.536 rows=252,732 loops=1)

  • Buffers: shared hit=3141
17. 0.148 0.676 ↑ 1.0 691 1

Hash (cost=55.96..55.96 rows=691 width=24) (actual time=0.676..0.676 rows=691 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=24
18. 0.210 0.528 ↑ 1.0 691 1

Hash Left Join (cost=27.55..55.96 rows=691 width=24) (actual time=0.286..0.528 rows=691 loops=1)

  • Hash Cond: (b.lead_oms_no = lb.oms_no)
  • Buffers: shared hit=24
19. 0.052 0.052 ↑ 1.0 691 1

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

  • Buffers: shared hit=12
20. 0.141 0.266 ↑ 1.0 691 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=12
21. 0.125 0.125 ↑ 1.0 691 1

Seq Scan on broker lb (cost=0.00..18.91 rows=691 width=24) (actual time=0.003..0.125 rows=691 loops=1)

  • Buffers: shared hit=12
22. 62.468 427.927 ↓ 2.8 607,085 1

Materialize (cost=26,475.52..27,556.81 rows=216,257 width=15) (actual time=332.299..427.927 rows=607,085 loops=1)

  • Buffers: shared hit=1454, temp read=707 written=707
23. 335.993 365.459 ↑ 1.0 216,235 1

Sort (cost=26,475.52..27,016.16 rows=216,257 width=15) (actual time=332.293..365.459 rows=216,235 loops=1)

  • Sort Key: fx.valuation_point, fx.iso_currency_code
  • Sort Method: external sort Disk: 5656kB
  • Buffers: shared hit=1454, temp read=707 written=707
24. 29.466 29.466 ↑ 1.0 216,235 1

Seq Scan on exchange_rate fx (cost=0.00..3,616.57 rows=216,257 width=15) (actual time=0.008..29.466 rows=216,235 loops=1)

  • Buffers: shared hit=1454
25. 792.108 3,168.432 ↑ 1.0 1 396,054

Nested Loop Anti Join (cost=0.56..5.47 rows=1 width=325) (actual time=0.007..0.008 rows=1 loops=396,054)

  • Join Filter: ((virtu.order_number = exc.order_number) AND (virtu.deal_no = exc.deal_no) AND (virtu.account = exc.account) AND ((virtu.symbol)::text = (exc.sedol)::text) AND (virtu.order_id = exc.order_id) AND (virtu.trade_date = exc.trade_date))
  • Rows Removed by Join Filter: 2
  • Buffers: shared hit=1982407
26. 2,376.324 2,376.324 ↑ 1.0 1 396,054

Index Scan using idx_equity_tca_virtu_omskey on equity_tca_virtu virtu (cost=0.56..4.39 rows=1 width=325) (actual time=0.006..0.006 rows=1 loops=396,054)

  • Index Cond: ((oms.order_no = order_number) AND (oms.allocation_match_id = order_id) AND (oms.deal_no = deal_no) AND (oms.fund_code = account) AND ((oms.sedol)::text = (symbol)::text))
  • Filter: (oms.trade_date = trade_date)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1982406
27. 0.000 0.000 ↑ 1.0 2 383,979

Materialize (cost=0.00..1.03 rows=2 width=28) (actual time=0.000..0.000 rows=2 loops=383,979)

  • Buffers: shared hit=1
28. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on equity_tca_virtu_exclusion exc (cost=0.00..1.02 rows=2 width=28) (actual time=0.005..0.005 rows=2 loops=1)

  • Buffers: shared hit=1
29. 44.413 64.071 ↓ 249.7 397,773 1

Sort (cost=4,241.94..4,245.92 rows=1,593 width=11) (actual time=20.083..64.071 rows=397,773 loops=1)

  • Sort Key: exchange_rate.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1454
30. 19.658 19.658 ↓ 1.2 1,949 1

Seq Scan on exchange_rate (cost=0.00..4,157.21 rows=1,593 width=11) (actual time=0.012..19.658 rows=1,949 loops=1)

  • Filter: ((iso_currency_code)::text = 'JPY'::text)
  • Rows Removed by Filter: 214286
  • Buffers: shared hit=1454
31. 47.970 67.544 ↓ 249.7 397,773 1

Sort (cost=4,241.94..4,245.92 rows=1,593 width=11) (actual time=19.924..67.544 rows=397,773 loops=1)

  • Sort Key: exchange_rate_1.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1454
32. 19.574 19.574 ↓ 1.2 1,949 1

Seq Scan on exchange_rate exchange_rate_1 (cost=0.00..4,157.21 rows=1,593 width=11) (actual time=0.012..19.574 rows=1,949 loops=1)

  • Filter: ((iso_currency_code)::text = 'EUR'::text)
  • Rows Removed by Filter: 214286
  • Buffers: shared hit=1454
33. 0.055 0.471 ↓ 1.0 253 1

Hash (cost=50.38..50.38 rows=252 width=35) (actual time=0.471..0.471 rows=253 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=30
34. 0.094 0.416 ↓ 1.0 253 1

Hash Left Join (cost=18.53..50.38 rows=252 width=35) (actual time=0.212..0.416 rows=253 loops=1)

  • Hash Cond: ((c_2.code)::text = (cr_1.country_code)::text)
  • Buffers: shared hit=30
35. 0.062 0.237 ↓ 1.0 253 1

Hash Right Join (cost=9.90..38.29 rows=252 width=8) (actual time=0.111..0.237 rows=253 loops=1)

  • Hash Cond: ((country_group_1.country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=27
36. 0.103 0.103 ↑ 1.0 252 1

Index Only Scan using "PK_country_group" on country_group country_group_1 (cost=0.28..25.20 rows=252 width=8) (actual time=0.032..0.103 rows=252 loops=1)

  • Index Cond: (group_code = ANY ('{Americas,APAC,EMEA}'::text[]))
  • Heap Fetches: 122
  • Buffers: shared hit=23
37. 0.037 0.072 ↑ 1.0 250 1

Hash (cost=6.50..6.50 rows=250 width=3) (actual time=0.072..0.072 rows=250 loops=1)

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

Seq Scan on country c_2 (cost=0.00..6.50 rows=250 width=3) (actual time=0.004..0.035 rows=250 loops=1)

  • Buffers: shared hit=4
39. 0.042 0.085 ↑ 1.0 250 1

Hash (cost=5.50..5.50 rows=250 width=10) (actual time=0.085..0.085 rows=250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=3
40. 0.043 0.043 ↑ 1.0 250 1

Seq Scan on country_region cr_1 (cost=0.00..5.50 rows=250 width=10) (actual time=0.003..0.043 rows=250 loops=1)

  • Buffers: shared hit=3
41. 0.039 0.448 ↑ 1.0 250 1

Hash (cost=49.27..49.27 rows=250 width=35) (actual time=0.448..0.448 rows=250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=34
42. 0.096 0.409 ↑ 1.0 250 1

Hash Left Join (cost=18.53..49.27 rows=250 width=35) (actual time=0.201..0.409 rows=250 loops=1)

  • Hash Cond: ((c_1.code)::text = (cr.country_code)::text)
  • Buffers: shared hit=34
43. 0.072 0.235 ↑ 1.0 250 1

Hash Right Join (cost=9.90..36.58 rows=250 width=8) (actual time=0.107..0.235 rows=250 loops=1)

  • Hash Cond: ((country_group.country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=31
44. 0.088 0.088 ↑ 1.0 197 1

Index Only Scan using "PK_country_group" on country_group (cost=0.28..24.25 rows=197 width=8) (actual time=0.021..0.088 rows=197 loops=1)

  • Index Cond: (group_code = ANY ('{Americas,APAC,MEA}'::text[]))
  • Heap Fetches: 134
  • Buffers: shared hit=27
45. 0.044 0.075 ↑ 1.0 250 1

Hash (cost=6.50..6.50 rows=250 width=3) (actual time=0.075..0.075 rows=250 loops=1)

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

Seq Scan on country c_1 (cost=0.00..6.50 rows=250 width=3) (actual time=0.002..0.031 rows=250 loops=1)

  • Buffers: shared hit=4
47. 0.047 0.078 ↑ 1.0 250 1

Hash (cost=5.50..5.50 rows=250 width=10) (actual time=0.078..0.078 rows=250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=3
48. 0.031 0.031 ↑ 1.0 250 1

Seq Scan on country_region cr (cost=0.00..5.50 rows=250 width=10) (actual time=0.002..0.031 rows=250 loops=1)

  • Buffers: shared hit=3
49. 293.195 707.507 ↑ 1.0 1,185,048 1

Hash (cost=64,047.36..64,047.36 rows=1,185,436 width=12) (actual time=707.507..707.507 rows=1,185,048 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2618kB
  • Buffers: shared hit=52193, temp written=4470
50. 414.312 414.312 ↑ 1.0 1,185,048 1

Seq Scan on oms_execution_line el (cost=0.00..64,047.36 rows=1,185,436 width=12) (actual time=0.005..414.312 rows=1,185,048 loops=1)

  • Buffers: shared hit=52193
51. 0.061 0.250 ↑ 1.0 330 1

Hash (cost=11.63..11.63 rows=330 width=19) (actual time=0.250..0.250 rows=330 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=4
52. 0.126 0.189 ↑ 1.0 330 1

Hash Left Join (cost=2.28..11.63 rows=330 width=19) (actual time=0.052..0.189 rows=330 loops=1)

  • Hash Cond: ((otf.trade_flag_name)::text = (tf.name)::text)
  • Buffers: shared hit=4
53. 0.036 0.036 ↑ 1.0 330 1

Seq Scan on oms_trade_flag otf (cost=0.00..6.30 rows=330 width=17) (actual time=0.007..0.036 rows=330 loops=1)

  • Buffers: shared hit=3
54. 0.015 0.027 ↑ 1.0 57 1

Hash (cost=1.57..1.57 rows=57 width=28) (actual time=0.027..0.027 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
55. 0.012 0.012 ↑ 1.0 57 1

Seq Scan on trade_flag tf (cost=0.00..1.57 rows=57 width=28) (actual time=0.003..0.012 rows=57 loops=1)

  • Buffers: shared hit=1
56. 0.050 0.094 ↑ 1.0 250 1

Hash (cost=6.50..6.50 rows=250 width=16) (actual time=0.094..0.094 rows=250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=4
57. 0.044 0.044 ↑ 1.0 250 1

Seq Scan on country c (cost=0.00..6.50 rows=250 width=16) (actual time=0.004..0.044 rows=250 loops=1)

  • Buffers: shared hit=4
Planning time : 11.295 ms
Execution time : 13,686.876 ms