explain.depesz.com

PostgreSQL's explain analyze made readable

Result: heH

Settings
# exclusive inclusive rows x rows loops node
1. 62.408 1,204.744 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=190,287.65..263,126.40 rows=1 width=831) (actual time=735.740..1,204.744 rows=7,826 loops=1)

  • Buffers: shared hit=206674 read=27, temp read=846 written=840
2. 7.336 1,126.684 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=190,287.51..263,126.11 rows=1 width=543) (actual time=735.683..1,126.684 rows=7,826 loops=1)

  • Buffers: shared hit=191022 read=27, temp read=846 written=840
3. 221.648 1,111.522 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=190,287.37..263,125.94 rows=1 width=534) (actual time=735.673..1,111.522 rows=7,826 loops=1)

  • Join Filter: ((c.code)::text = (sm.opol_country_code)::text)
  • Rows Removed by Join Filter: 1948678
  • Buffers: shared hit=175370 read=27, temp read=846 written=840
4. 295.988 749.006 ↓ 7,826.0 7,826 1

Hash Right Join (cost=190,287.37..263,116.31 rows=1 width=521) (actual time=735.606..749.006 rows=7,826 loops=1)

  • Hash Cond: ((el.deal_no = oms.deal_no) AND (el.order_line_id = oms.order_line_id))
  • Buffers: shared hit=144066 read=27, temp read=846 written=840
5. 149.931 149.931 ↓ 1.0 1,184,501 1

Seq Scan on oms_execution_line el (cost=0.00..63,950.25 rows=1,183,825 width=12) (actual time=0.005..149.931 rows=1,184,501 loops=1)

  • Buffers: shared hit=52112
6. 7.052 303.087 ↓ 7,826.0 7,826 1

Hash (cost=190,287.35..190,287.35 rows=1 width=521) (actual time=303.087..303.087 rows=7,826 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1795kB
  • Buffers: shared hit=91954 read=27, temp read=846 written=840
7. 3.700 296.035 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=37,811.27..190,287.35 rows=1 width=521) (actual time=179.075..296.035 rows=7,826 loops=1)

  • Filter: (oms_trade_notification_esb.id IS NULL)
  • Rows Removed by Filter: 47
  • Buffers: shared hit=91954 read=27, temp read=846 written=840
8. 4.098 245.097 ↑ 1.0 7,873 1

Hash Left Join (cost=37,810.84..125,852.43 rows=8,162 width=521) (actual time=178.231..245.097 rows=7,873 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=68216, temp read=846 written=840
9. 4.390 240.555 ↑ 1.0 7,873 1

Hash Left Join (cost=37,758.45..125,755.03 rows=8,162 width=489) (actual time=177.777..240.555 rows=7,873 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=68182, temp read=846 written=840
10. 3.608 235.641 ↑ 1.0 7,873 1

Merge Left Join (cost=37,704.92..125,656.39 rows=8,162 width=457) (actual time=177.235..235.641 rows=7,873 loops=1)

  • Merge Cond: (oms.trade_date = fx_eur.valuation_point)
  • Buffers: shared hit=68152, temp read=846 written=840
11. 2.381 211.976 ↑ 1.0 7,873 1

Nested Loop Left Join (cost=33,454.15..121,304.12 rows=8,162 width=450) (actual time=157.550..211.976 rows=7,873 loops=1)

  • Buffers: shared hit=66699, temp read=846 written=840
12. 6.486 185.976 ↑ 1.0 7,873 1

Nested Loop Left Join (cost=33,453.73..109,123.22 rows=8,162 width=447) (actual time=157.534..185.976 rows=7,873 loops=1)

  • Buffers: shared hit=36466, temp read=846 written=840
13. 3.827 163.744 ↑ 1.0 7,873 1

Merge Left Join (cost=33,453.17..33,575.06 rows=8,162 width=151) (actual time=157.512..163.744 rows=7,873 loops=1)

  • Merge Cond: (oms.trade_date = fx_jpy.valuation_point)
  • Buffers: shared hit=4974, temp read=846 written=840
14. 6.206 138.989 ↑ 1.0 7,873 1

Sort (cost=29,202.40..29,222.80 rows=8,162 width=144) (actual time=136.983..138.989 rows=7,873 loops=1)

  • Sort Key: oms.trade_date
  • Sort Method: quicksort Memory: 2284kB
  • Buffers: shared hit=3521, temp read=846 written=840
15. 2.235 132.783 ↑ 1.0 7,873 1

Hash Left Join (cost=10,368.18..28,672.08 rows=8,162 width=144) (actual time=95.718..132.783 rows=7,873 loops=1)

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=3517, temp read=846 written=840
16. 23.809 129.910 ↑ 1.0 7,873 1

Hash Left Join (cost=10,303.58..28,495.26 rows=8,162 width=128) (actual time=95.063..129.910 rows=7,873 loops=1)

  • Hash Cond: ((oms.sedol)::text = (sm.sedol)::text)
  • Buffers: shared hit=3493, temp read=846 written=840
17. 11.432 11.432 ↑ 1.0 7,873 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb oms (cost=0.43..16,327.88 rows=8,162 width=112) (actual time=0.034..11.432 rows=7,873 loops=1)

  • Index Cond: ((trade_date > '2020-01-01'::date) AND (instrument_type = ANY ('{10,2509}'::integer[])))
  • Filter: (deal_status <> 7)
  • Rows Removed by Filter: 47
  • Buffers: shared hit=352
18. 53.478 94.669 ↑ 1.0 252,540 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3244kB
  • Buffers: shared hit=3141, temp written=739
19. 41.191 41.191 ↑ 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.016..41.191 rows=252,540 loops=1)

  • Buffers: shared hit=3141
20. 0.118 0.638 ↑ 1.0 691 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=24
21. 0.229 0.520 ↑ 1.0 691 1

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

  • Hash Cond: (b.lead_oms_no = lb.oms_no)
  • Buffers: shared hit=24
22. 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.005..0.054 rows=691 loops=1)

  • Buffers: shared hit=12
23. 0.129 0.237 ↑ 1.0 691 1

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

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

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

  • Buffers: shared hit=12
25. 1.093 20.928 ↓ 3.3 5,259 1

Sort (cost=4,239.02..4,243.00 rows=1,592 width=11) (actual time=20.240..20.928 rows=5,259 loops=1)

  • Sort Key: fx_jpy.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1453
26. 19.835 19.835 ↓ 1.2 1,948 1

Seq Scan on exchange_rate fx_jpy (cost=0.00..4,154.35 rows=1,592 width=11) (actual time=0.017..19.835 rows=1,948 loops=1)

  • Filter: ((iso_currency_code)::text = 'JPY'::text)
  • Rows Removed by Filter: 214160
  • Buffers: shared hit=1453
27. 0.000 15.746 ↓ 0.0 0 7,873

Nested Loop Anti Join (cost=0.56..9.25 rows=1 width=324) (actual time=0.002..0.002 rows=0 loops=7,873)

  • 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))
  • Buffers: shared hit=31492
28. 15.746 15.746 ↓ 0.0 0 7,873

Index Scan using idx_equity_tca_virtu_omskey on equity_tca_virtu virtu (cost=0.56..8.16 rows=1 width=324) (actual time=0.002..0.002 rows=0 loops=7,873)

  • 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)
  • Buffers: shared hit=31492
29. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.03 rows=2 width=28) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on equity_tca_virtu_exclusion exc (cost=0.00..1.02 rows=2 width=28) (never executed)

31. 23.619 23.619 ↑ 1.0 1 7,873

Index Scan using pk__exchange_rate on exchange_rate fx (cost=0.42..1.48 rows=1 width=15) (actual time=0.002..0.003 rows=1 loops=7,873)

  • Index Cond: ((valuation_point = oms.trade_date) AND ((oms.instrument_currency)::text = (iso_currency_code)::text))
  • Buffers: shared hit=30233
32. 1.081 20.057 ↓ 3.3 5,259 1

Sort (cost=4,239.02..4,243.00 rows=1,592 width=11) (actual time=19.394..20.057 rows=5,259 loops=1)

  • Sort Key: fx_eur.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1453
33. 18.976 18.976 ↓ 1.2 1,948 1

Seq Scan on exchange_rate fx_eur (cost=0.00..4,154.35 rows=1,592 width=11) (actual time=0.009..18.976 rows=1,948 loops=1)

  • Filter: ((iso_currency_code)::text = 'EUR'::text)
  • Rows Removed by Filter: 214160
  • Buffers: shared hit=1453
34. 0.049 0.524 ↓ 1.0 253 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=30
35. 0.101 0.475 ↓ 1.0 253 1

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

  • Hash Cond: ((c_2.code)::text = (cr_1.country_code)::text)
  • Buffers: shared hit=30
36. 0.090 0.292 ↓ 1.0 253 1

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

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

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

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

  • Buffers: shared hit=4
40. 0.042 0.082 ↑ 1.0 250 1

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

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

  • Buffers: shared hit=3
42. 0.046 0.444 ↑ 1.0 250 1

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

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

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

  • Hash Cond: ((c_1.code)::text = (cr.country_code)::text)
  • Buffers: shared hit=34
44. 0.076 0.226 ↑ 1.0 250 1

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

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

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

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

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

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

  • Buffers: shared hit=4
48. 0.044 0.076 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=3
50. 47.238 47.238 ↓ 0.0 0 7,873

Index Scan using idx_oms_trade_notification_esb_cancel on oms_trade_notification_esb (cost=0.43..7.88 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=7,873)

  • Index Cond: ((oms.deal_no = deal_no) AND (deal_no IS NOT NULL) AND (deal_status = 7))
  • Buffers: shared hit=23738 read=27
51. 140.868 140.868 ↑ 1.0 250 7,826

Seq Scan on country c (cost=0.00..6.50 rows=250 width=16) (actual time=0.001..0.018 rows=250 loops=7,826)

  • Buffers: shared hit=31304
52. 7.826 7.826 ↑ 1.0 1 7,826

Index Scan using "PK_oms_trade_flag" on oms_trade_flag otf (cost=0.15..0.17 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=7,826)

  • Index Cond: (trade_flag_no = el.trade_flag_no)
  • Buffers: shared hit=15652
53. 15.652 15.652 ↑ 1.0 1 7,826

Index Scan using "PK_trade_flag" on trade_flag tf (cost=0.14..0.19 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=7,826)

  • Index Cond: ((name)::text = (otf.trade_flag_name)::text)
  • Buffers: shared hit=15652
Planning time : 12.306 ms
Execution time : 1,206.779 ms