explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PcQ3

Settings
# exclusive inclusive rows x rows loops node
1. 4,937.835 17,687.266 ↓ 1.0 397,923 1

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

  • Hash Cond: ((sm.opol_country_code)::text = (c.code)::text)
  • Buffers: shared hit=2107721, temp read=48847 written=48779
2. 287.945 12,749.313 ↓ 1.0 397,923 1

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

  • Hash Cond: (el.trade_flag_no = otf.trade_flag_no)
  • Buffers: shared hit=2107717, temp read=48847 written=48779
3. 2,061.834 12,461.135 ↓ 1.0 397,923 1

Hash Left Join (cost=577,074.47..2,752,440.57 rows=381,964 width=522) (actual time=3,736.594..12,461.135 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=48847 written=48779
4. 326.721 9,615.441 ↓ 1.0 397,923 1

Hash Left Join (cost=489,456.57..2,604,692.93 rows=381,964 width=522) (actual time=2,951.563..9,615.441 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=2055520, temp read=19373 written=19367
5. 324.031 9,288.310 ↓ 1.0 397,923 1

Hash Left Join (cost=489,404.17..2,602,534.64 rows=381,964 width=490) (actual time=2,951.145..9,288.310 rows=397,923 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=2055486, temp read=19373 written=19367
6. 347.136 8,963.816 ↓ 1.0 397,923 1

Merge Left Join (cost=489,350.64..2,600,369.83 rows=381,964 width=458) (actual time=2,950.666..8,963.816 rows=397,923 loops=1)

  • Merge Cond: (oms.trade_date = exchange_rate_1.valuation_point)
  • Buffers: shared hit=2055456, temp read=19373 written=19367
7. 291.287 8,530.143 ↓ 1.0 397,923 1

Merge Left Join (cost=484,661.80..2,590,929.75 rows=381,964 width=451) (actual time=2,929.299..8,530.143 rows=397,923 loops=1)

  • Merge Cond: (oms.trade_date = exchange_rate.valuation_point)
  • Buffers: shared hit=2054002, temp read=19373 written=19367
8. 214.514 8,162.978 ↓ 1.0 397,923 1

Nested Loop Left Join (cost=479,972.95..2,581,489.67 rows=381,964 width=444) (actual time=2,907.656..8,162.978 rows=397,923 loops=1)

  • Buffers: shared hit=2052548, temp read=19373 written=19367
9. 439.285 3,987.924 ↓ 1.0 396,054 1

Merge Left Join (cost=479,972.40..486,954.15 rows=381,964 width=147) (actual time=2,907.591..3,987.924 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=19373 written=19367
10. 1,723.649 3,102.569 ↓ 1.0 396,054 1

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

  • Sort Key: oms.trade_date, oms.instrument_currency
  • Sort Method: external merge Disk: 65560kB
  • Buffers: shared hit=68687, temp read=18666 written=18660
11. 121.017 1,378.920 ↓ 1.0 396,054 1

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

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=68687, temp read=5676 written=5670
12. 386.053 1,257.276 ↓ 1.0 396,054 1

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

  • Hash Cond: ((oms.sedol)::text = (sm.sedol)::text)
  • Buffers: shared hit=68663, temp read=5676 written=5670
13. 669.878 779.760 ↓ 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=117.346..779.760 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. 109.882 109.882 ↓ 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=109.882..109.882 rows=787,719 loops=1)

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

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3247kB
  • Buffers: shared hit=3141, temp written=741
16. 39.222 39.222 ↓ 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.007..39.222 rows=252,732 loops=1)

  • Buffers: shared hit=3141
17. 0.116 0.627 ↑ 1.0 691 1

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

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

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

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

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

  • Buffers: shared hit=12
20. 0.127 0.233 ↑ 1.0 691 1

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

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

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

  • Buffers: shared hit=12
22. 71.071 446.070 ↓ 2.8 607,085 1

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

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

Sort (cost=26,475.52..27,016.16 rows=216,257 width=15) (actual time=338.547..374.999 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.881 29.881 ↑ 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.881 rows=216,235 loops=1)

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

Nested Loop Anti Join (cost=0.56..5.47 rows=1 width=325) (actual time=0.009..0.010 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. 3,168.432 3,168.432 ↑ 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.008..0.008 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.006 0.006 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
29. 54.907 75.878 ↓ 249.7 397,773 1

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

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

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

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

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

  • Sort Key: exchange_rate_1.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1454
32. 20.691 20.691 ↓ 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.013..20.691 rows=1,949 loops=1)

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

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

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

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

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

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

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

  • Index Cond: (group_code = ANY ('{Americas,APAC,EMEA}'::text[]))
  • Heap Fetches: 122
  • Buffers: shared hit=23
37. 0.039 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.033 0.033 ↑ 1.0 250 1

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

  • Buffers: shared hit=4
39. 0.041 0.080 ↑ 1.0 250 1

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

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

  • Buffers: shared hit=3
41. 0.043 0.410 ↑ 1.0 250 1

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

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

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

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

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

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

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

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

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

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

  • Buffers: shared hit=4
47. 0.036 0.072 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=3
49. 351.534 783.860 ↑ 1.0 1,185,048 1

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

  • Buckets: 131072 Batches: 32 Memory Usage: 2618kB
  • Buffers: shared hit=52193, temp written=4470
50. 432.326 432.326 ↑ 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..432.326 rows=1,185,048 loops=1)

  • Buffers: shared hit=52193
51. 0.055 0.233 ↑ 1.0 330 1

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

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

Hash Left Join (cost=2.28..11.63 rows=330 width=19) (actual time=0.049..0.178 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.018 0.029 ↑ 1.0 57 1

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

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

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

  • Buffers: shared hit=1
56. 0.065 0.118 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=4
Planning time : 17.381 ms
Execution time : 17,755.133 ms