explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kHwH

Settings
# exclusive inclusive rows x rows loops node
1. 4,124.775 39,641.213 ↓ 1.0 397,923 1

Hash Left Join (cost=577,099.85..2,791,334.10 rows=381,964 width=832) (actual time=3,935.706..39,641.213 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c.code)::text)
  • Buffers: shared hit=2,088,566 read=19,170, temp read=48,847 written=48,779
2. 230.714 35,516.346 ↓ 1.0 397,923 1

Hash Left Join (cost=577,090.22..2,757,706.55 rows=381,964 width=533) (actual time=3,935.565..35,516.346 rows=397,923 loops=1)

  • Hash Cond: (el.trade_flag_no = otf.trade_flag_no)
  • Buffers: shared hit=2,088,562 read=19,170, temp read=48,847 written=48,779
3. 1,953.914 35,285.342 ↓ 1.0 397,923 1

Hash Left Join (cost=577,074.47..2,752,440.57 rows=381,964 width=522) (actual time=3,935.254..35,285.342 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=2,088,558 read=19,170, temp read=48,847 written=48,779
4. 300.489 32,629.810 ↓ 1.0 397,923 1

Hash Left Join (cost=489,456.57..2,604,692.93 rows=381,964 width=522) (actual time=3,232.310..32,629.810 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=2,036,365 read=19,170, temp read=19,373 written=19,367
5. 313.009 32,328.712 ↓ 1.0 397,923 1

Hash Left Join (cost=489,404.17..2,602,534.64 rows=381,964 width=490) (actual time=3,231.682..32,328.712 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=2,036,331 read=19,170, temp read=19,373 written=19,367
6. 278.018 32,015.087 ↓ 1.0 397,923 1

Merge Left Join (cost=489,350.64..2,600,369.83 rows=381,964 width=458) (actual time=3,231.050..32,015.087 rows=397,923 loops=1)

  • Merge Cond: (oms.trade_date = fx_eur.valuation_point)
  • Buffers: shared hit=2,036,301 read=19,170, temp read=19,373 written=19,367
7. 321.007 31,647.581 ↓ 1.0 397,923 1

Merge Left Join (cost=484,661.80..2,590,929.75 rows=381,964 width=451) (actual time=3,205.286..31,647.581 rows=397,923 loops=1)

  • Merge Cond: (oms.trade_date = fx_jpy.valuation_point)
  • Buffers: shared hit=2,034,847 read=19,170, temp read=19,373 written=19,367
8. 436.224 31,251.374 ↓ 1.0 397,923 1

Nested Loop Left Join (cost=479,972.95..2,581,489.67 rows=381,964 width=444) (actual time=3,182.266..31,251.374 rows=397,923 loops=1)

  • Buffers: shared hit=2,033,393 read=19,170, temp read=19,373 written=19,367
9. 444.030 4,279.532 ↓ 1.0 396,054 1

Merge Left Join (cost=479,972.40..486,954.15 rows=381,964 width=147) (actual time=3,182.192..4,279.532 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=70,156, temp read=19,373 written=19,367
10. 1,867.614 3,323.656 ↓ 1.0 396,054 1

Sort (cost=452,435.11..453,390.02 rows=381,964 width=144) (actual time=2,697.060..3,323.656 rows=396,054 loops=1)

  • Sort Key: oms.trade_date, oms.instrument_currency
  • Sort Method: external merge Disk: 65,560kB
  • Buffers: shared hit=68,702, temp read=18,666 written=18,660
11. 132.208 1,456.042 ↓ 1.0 396,054 1

Hash Left Join (cost=23,555.83..362,183.17 rows=381,964 width=144) (actual time=224.691..1,456.042 rows=396,054 loops=1)

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=68,693, temp read=5,676 written=5,670
12. 439.446 1,322.986 ↓ 1.0 396,054 1

Hash Left Join (cost=23,491.24..356,866.57 rows=381,964 width=128) (actual time=223.791..1,322.986 rows=396,054 loops=1)

  • Hash Cond: ((oms.sedol)::text = (sm.sedol)::text)
  • Buffers: shared hit=68,666, temp read=5,676 written=5,670
13. 669.566 774.084 ↓ 1.0 396,054 1

Bitmap Heap Scan on oms_trade_notification_esb oms (cost=13,188.09..327,147.42 rows=381,964 width=112) (actual time=113.623..774.084 rows=396,054 loops=1)

  • Recheck Cond: (trade_date > '2019-06-01'::date)
  • Rows Removed by Index Recheck: 19,137
  • Filter: (instrument_type = ANY ('{10,2509}'::integer[]))
  • Rows Removed by Filter: 228,691
  • Heap Blocks: exact=35,785 lossy=26,724
  • Buffers: shared hit=65,522
14. 104.518 104.518 ↓ 1.3 787,719 1

Bitmap Index Scan on oms_trade_notification_esb_currency (cost=0.00..13,092.60 rows=627,222 width=0) (actual time=104.518..104.518 rows=787,719 loops=1)

  • Index Cond: (trade_date > '2019-06-01'::date)
  • Buffers: shared hit=3,013
15. 62.046 109.456 ↓ 1.0 252,732 1

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

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,247kB
  • Buffers: shared hit=3,141, temp written=741
16. 47.410 47.410 ↓ 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.005..47.410 rows=252,732 loops=1)

  • Buffers: shared hit=3,141
17. 0.193 0.848 ↑ 1.0 691 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=24
18. 0.341 0.655 ↑ 1.0 691 1

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

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

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

  • Buffers: shared hit=12
20. 0.135 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
21. 0.097 0.097 ↑ 1.0 691 1

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

  • Buffers: shared hit=12
22. 78.462 511.846 ↓ 2.8 607,085 1

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

  • Buffers: shared hit=1,454, temp read=707 written=707
23. 396.788 433.384 ↑ 1.0 216,235 1

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

  • Sort Key: fx.valuation_point, fx.iso_currency_code
  • Sort Method: external sort Disk: 5,656kB
  • Buffers: shared hit=1,454, temp read=707 written=707
24. 36.596 36.596 ↑ 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.009..36.596 rows=216,235 loops=1)

  • Buffers: shared hit=1,454
25. 396.054 26,535.618 ↑ 1.0 1 396,054

Nested Loop Anti Join (cost=0.56..5.47 rows=1 width=325) (actual time=0.066..0.067 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=1,963,237 read=19,170
26. 26,139.564 26,139.564 ↑ 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.065..0.066 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=1,963,236 read=19,170
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. 53.131 75.200 ↓ 249.7 397,773 1

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

  • Sort Key: fx_jpy.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1,454
30. 22.069 22.069 ↓ 1.2 1,949 1

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

  • Filter: ((iso_currency_code)::text = 'JPY'::text)
  • Rows Removed by Filter: 214,286
  • Buffers: shared hit=1,454
31. 64.546 89.488 ↓ 249.7 397,773 1

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

  • Sort Key: fx_eur.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1,454
32. 24.942 24.942 ↓ 1.2 1,949 1

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

  • Filter: ((iso_currency_code)::text = 'EUR'::text)
  • Rows Removed by Filter: 214,286
  • Buffers: shared hit=1,454
33. 0.071 0.616 ↓ 1.0 253 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=30
34. 0.127 0.545 ↓ 1.0 253 1

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

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

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

  • Hash Cond: ((country_group_1.country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=27
36. 0.144 0.144 ↑ 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.041..0.144 rows=252 loops=1)

  • Index Cond: (group_code = ANY ('{Americas,APAC,EMEA}'::text[]))
  • Heap Fetches: 122
  • Buffers: shared hit=23
37. 0.033 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: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=4
38. 0.042 0.042 ↑ 1.0 250 1

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

  • Buffers: shared hit=4
39. 0.053 0.098 ↑ 1.0 250 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=3
40. 0.045 0.045 ↑ 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.045 rows=250 loops=1)

  • Buffers: shared hit=3
41. 0.046 0.609 ↑ 1.0 250 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=34
42. 0.110 0.563 ↑ 1.0 250 1

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

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

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

  • Hash Cond: ((country_group.country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=31
44. 0.103 0.103 ↑ 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.036..0.103 rows=197 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=4
46. 0.052 0.052 ↑ 1.0 250 1

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

  • Buffers: shared hit=4
47. 0.075 0.131 ↑ 1.0 250 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=3
48. 0.056 0.056 ↑ 1.0 250 1

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

  • Buffers: shared hit=3
49. 294.096 701.618 ↑ 1.0 1,185,048 1

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

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,618kB
  • Buffers: shared hit=52,193, temp written=4,470
50. 407.522 407.522 ↑ 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.006..407.522 rows=1,185,048 loops=1)

  • Buffers: shared hit=52,193
51. 0.065 0.290 ↑ 1.0 330 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=4
52. 0.162 0.225 ↑ 1.0 330 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
55. 0.013 0.013 ↑ 1.0 57 1

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

  • Buffers: shared hit=1
56. 0.049 0.092 ↑ 1.0 250 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=4
57. 0.043 0.043 ↑ 1.0 250 1

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

  • Buffers: shared hit=4
Planning time : 16.236 ms
Execution time : 39,694.936 ms