explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dp93

Settings
# exclusive inclusive rows x rows loops node
1. 1,367.497 6,450.595 ↓ 1.1 175,666 1

Hash Left Join (cost=428,765.80..1,489,911.04 rows=159,485 width=832) (actual time=1,941.905..6,450.595 rows=175,666 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c.code)::text)
  • Buffers: shared hit=987,425, temp read=22,684 written=22,616
2. 74.005 5,083.003 ↓ 1.1 175,666 1

Hash Left Join (cost=428,756.18..1,475,864.60 rows=159,485 width=533) (actual time=1,941.755..5,083.003 rows=175,666 loops=1)

  • Hash Cond: (el.trade_flag_no = otf.trade_flag_no)
  • Buffers: shared hit=987,421, temp read=22,684 written=22,616
3. 669.635 5,008.771 ↓ 1.1 175,666 1

Hash Left Join (cost=428,740.42..1,473,656.67 rows=159,485 width=522) (actual time=1,941.513..5,008.771 rows=175,666 loops=1)

  • Hash Cond: ((oms.deal_no = el.deal_no) AND (oms.order_line_id = el.order_line_id))
  • Buffers: shared hit=987,417, temp read=22,684 written=22,616
4. 103.643 3,779.087 ↓ 1.1 175,666 1

Hash Left Join (cost=341,122.52..1,357,559.62 rows=159,485 width=522) (actual time=1,379.554..3,779.087 rows=175,666 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=935,224, temp read=7,205 written=7,199
5. 102.612 3,675.039 ↓ 1.1 175,666 1

Hash Left Join (cost=341,070.13..1,356,627.93 rows=159,485 width=490) (actual time=1,379.143..3,675.039 rows=175,666 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=935,190, temp read=7,205 written=7,199
6. 90.667 3,571.961 ↓ 1.1 175,666 1

Merge Left Join (cost=341,016.60..1,355,692.86 rows=159,485 width=458) (actual time=1,378.657..3,571.961 rows=175,666 loops=1)

  • Merge Cond: (oms.trade_date = fx_eur.valuation_point)
  • Buffers: shared hit=935,160, temp read=7,205 written=7,199
7. 90.696 3,436.263 ↓ 1.1 175,666 1

Merge Left Join (cost=336,586.74..1,349,279.17 rows=159,485 width=451) (actual time=1,357.066..3,436.263 rows=175,666 loops=1)

  • Merge Cond: (oms.trade_date = fx_jpy.valuation_point)
  • Buffers: shared hit=933,706, temp read=7,205 written=7,199
8. 104.721 3,297.754 ↓ 1.1 175,666 1

Nested Loop Left Join (cost=332,156.88..1,342,865.49 rows=159,485 width=444) (actual time=1,330.176..3,297.754 rows=175,666 loops=1)

  • Buffers: shared hit=932,252, temp read=7,205 written=7,199
9. 174.075 1,802.417 ↓ 1.1 173,827 1

Merge Left Join (cost=332,156.32..335,698.69 rows=159,485 width=147) (actual time=1,330.112..1,802.417 rows=173,827 loops=1)

  • Merge Cond: ((oms.trade_date = fx.valuation_point) AND ((oms.instrument_currency)::text = (fx.iso_currency_code)::text))
  • Buffers: shared hit=8,931, temp read=7,205 written=7,199
10. 685.536 1,215.463 ↓ 1.1 173,827 1

Sort (cost=304,757.83..305,156.54 rows=159,485 width=144) (actual time=908.486..1,215.463 rows=173,827 loops=1)

  • Sort Key: oms.trade_date, oms.instrument_currency
  • Sort Method: external merge Disk: 28,656kB
  • Buffers: shared hit=7,477, temp read=6,498 written=6,492
11. 50.775 529.927 ↓ 1.1 173,827 1

Hash Left Join (cost=10,368.18..279,527.38 rows=159,485 width=144) (actual time=91.595..529.927 rows=173,827 loops=1)

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=7,472, temp read=2,906 written=2,900
12. 170.926 478.329 ↓ 1.1 173,827 1

Hash Left Join (cost=10,303.58..277,269.87 rows=159,485 width=128) (actual time=90.745..478.329 rows=173,827 loops=1)

  • Hash Cond: ((oms.sedol)::text = (sm.sedol)::text)
  • Buffers: shared hit=7,448, temp read=2,906 written=2,900
13. 217.073 217.073 ↓ 1.1 173,827 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb oms (cost=0.43..257,997.80 rows=159,485 width=112) (actual time=0.035..217.073 rows=173,827 loops=1)

  • Index Cond: ((trade_date > '2019-10-01'::date) AND (instrument_type = ANY ('{10,2509}'::integer[])))
  • Buffers: shared hit=4,304
14. 51.466 90.330 ↑ 1.0 252,540 1

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

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,244kB
  • Buffers: shared hit=3,141, temp written=739
15. 38.864 38.864 ↑ 1.0 252,540 1

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

  • Buffers: shared hit=3,141
16. 0.118 0.823 ↑ 1.0 691 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=24
17. 0.248 0.705 ↑ 1.0 691 1

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

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

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

  • Buffers: shared hit=12
19. 0.217 0.403 ↑ 1.0 691 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=12
20. 0.186 0.186 ↑ 1.0 691 1

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

  • Buffers: shared hit=12
21. 45.374 412.879 ↓ 1.8 387,495 1

Materialize (cost=26,475.52..27,556.81 rows=216,257 width=15) (actual time=332.315..412.879 rows=387,495 loops=1)

  • Buffers: shared hit=1,454, temp read=707 written=707
22. 337.521 367.505 ↑ 1.0 216,235 1

Sort (cost=26,475.52..27,016.16 rows=216,257 width=15) (actual time=332.309..367.505 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
23. 29.984 29.984 ↑ 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.984 rows=216,235 loops=1)

  • Buffers: shared hit=1,454
24. 173.827 1,390.616 ↑ 1.0 1 173,827

Nested Loop Anti Join (cost=0.56..6.31 rows=1 width=325) (actual time=0.007..0.008 rows=1 loops=173,827)

  • 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=923,321
25. 1,216.789 1,216.789 ↑ 1.0 1 173,827

Index Scan using idx_equity_tca_virtu_omskey on equity_tca_virtu virtu (cost=0.56..5.22 rows=1 width=325) (actual time=0.006..0.007 rows=1 loops=173,827)

  • 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=923,320
26. 0.000 0.000 ↑ 1.0 2 167,199

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

  • Buffers: shared hit=1
27. 0.004 0.004 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
28. 21.677 47.813 ↓ 110.3 175,632 1

Sort (cost=4,241.94..4,245.92 rows=1,593 width=11) (actual time=26.593..47.813 rows=175,632 loops=1)

  • Sort Key: fx_jpy.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1,454
29. 26.136 26.136 ↓ 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.011..26.136 rows=1,949 loops=1)

  • Filter: ((iso_currency_code)::text = 'JPY'::text)
  • Rows Removed by Filter: 214,286
  • Buffers: shared hit=1,454
30. 24.140 45.031 ↓ 110.3 175,632 1

Sort (cost=4,241.94..4,245.92 rows=1,593 width=11) (actual time=21.314..45.031 rows=175,632 loops=1)

  • Sort Key: fx_eur.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1,454
31. 20.891 20.891 ↓ 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.010..20.891 rows=1,949 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=30
33. 0.081 0.423 ↓ 1.0 253 1

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

  • Hash Cond: ((c_2.code)::text = (cr_1.country_code)::text)
  • Buffers: shared hit=30
34. 0.072 0.265 ↓ 1.0 253 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=4
37. 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.005..0.042 rows=250 loops=1)

  • Buffers: shared hit=4
38. 0.038 0.077 ↑ 1.0 250 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=3
39. 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
40. 0.036 0.405 ↑ 1.0 250 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=34
41. 0.085 0.369 ↑ 1.0 250 1

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

  • Hash Cond: ((c_1.code)::text = (cr.country_code)::text)
  • Buffers: shared hit=34
42. 0.079 0.209 ↑ 1.0 250 1

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

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

  • Index Cond: (group_code = ANY ('{Americas,APAC,MEA}'::text[]))
  • Heap Fetches: 134
  • Buffers: shared hit=27
44. 0.031 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: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=4
45. 0.035 0.035 ↑ 1.0 250 1

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

  • Buffers: shared hit=4
46. 0.036 0.075 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=3
48. 230.881 560.049 ↑ 1.0 1,185,048 1

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

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

  • Buffers: shared hit=52,193
50. 0.045 0.227 ↑ 1.0 330 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=4
51. 0.127 0.182 ↑ 1.0 330 1

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

  • Hash Cond: ((otf.trade_flag_name)::text = (tf.name)::text)
  • Buffers: shared hit=4
52. 0.030 0.030 ↑ 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.030 rows=330 loops=1)

  • Buffers: shared hit=3
53. 0.015 0.025 ↑ 1.0 57 1

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

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

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

  • Buffers: shared hit=1
55. 0.057 0.095 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=4
Planning time : 10.951 ms
Execution time : 6,469.758 ms