explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mNR7

Settings
# exclusive inclusive rows x rows loops node
1. 97.824 1,160.416 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=37,811.99..190,303.20 rows=1 width=831) (actual time=269.709..1,160.416 rows=7,826 loops=1)

  • Buffers: shared hit=185815, temp read=846 written=840
2. 9.819 1,031.288 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=37,811.85..190,302.91 rows=1 width=543) (actual time=269.646..1,031.288 rows=7,826 loops=1)

  • Buffers: shared hit=170163, temp read=846 written=840
3. 11.723 1,005.817 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=37,811.70..190,302.74 rows=1 width=534) (actual time=269.637..1,005.817 rows=7,826 loops=1)

  • Buffers: shared hit=154511, temp read=846 written=840
4. 341.353 962.790 ↓ 7,826.0 7,826 1

Nested Loop Left Join (cost=37,811.27..190,296.98 rows=1 width=534) (actual time=269.614..962.790 rows=7,826 loops=1)

  • Join Filter: ((c.code)::text = (sm.opol_country_code)::text)
  • Rows Removed by Join Filter: 1948678
  • Buffers: shared hit=123141, temp read=846 written=840
5. 9.134 433.613 ↓ 7,826.0 7,826 1

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

  • Filter: (oms_trade_notification_esb.id IS NULL)
  • Rows Removed by Filter: 47
  • Buffers: shared hit=91837, temp read=846 written=840
6. 8.269 400.860 ↑ 1.0 7,873 1

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

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=68156, temp read=846 written=840
7. 8.305 391.961 ↑ 1.0 7,873 1

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

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=68122, temp read=846 written=840
8. 6.769 383.169 ↑ 1.0 7,873 1

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

  • Merge Cond: (oms.trade_date = fx_eur.valuation_point)
  • Buffers: shared hit=68092, temp read=846 written=840
9. 7.547 334.204 ↑ 1.0 7,873 1

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

  • Buffers: shared hit=66639, temp read=846 written=840
10. 9.021 287.292 ↑ 1.0 7,873 1

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

  • Buffers: shared hit=36406, temp read=846 written=840
11. 7.323 238.906 ↑ 1.0 7,873 1

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

  • Merge Cond: (oms.trade_date = fx_jpy.valuation_point)
  • Buffers: shared hit=4914, temp read=846 written=840
12. 10.932 195.138 ↑ 1.0 7,873 1

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

  • Sort Key: oms.trade_date
  • Sort Method: quicksort Memory: 2284kB
  • Buffers: shared hit=3461, temp read=846 written=840
13. 4.221 184.206 ↑ 1.0 7,873 1

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

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=3461, temp read=846 written=840
14. 33.879 179.053 ↑ 1.0 7,873 1

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

  • Hash Cond: ((oms.sedol)::text = (sm.sedol)::text)
  • Buffers: shared hit=3437, temp read=846 written=840
15. 10.304 10.304 ↑ 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.035..10.304 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=296
16. 83.278 134.870 ↑ 1.0 252,540 1

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

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

  • Buffers: shared hit=3141
18. 0.164 0.932 ↑ 1.0 691 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=24
19. 0.288 0.768 ↑ 1.0 691 1

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

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

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

  • Buffers: shared hit=12
21. 0.230 0.407 ↑ 1.0 691 1

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

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

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

  • Buffers: shared hit=12
23. 2.039 36.445 ↓ 3.3 5,259 1

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

  • Sort Key: fx_jpy.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1453
24. 34.406 34.406 ↓ 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.029..34.406 rows=1,948 loops=1)

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

Nested Loop Anti Join (cost=0.56..9.25 rows=1 width=324) (actual time=0.005..0.005 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
26. 31.492 31.492 ↓ 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.004..0.004 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
27. 0.000 0.000 ↓ 0.0 0

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

28. 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)

29. 39.365 39.365 ↑ 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.004..0.005 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
30. 2.648 42.196 ↓ 3.3 5,259 1

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

  • Sort Key: fx_eur.valuation_point
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=1453
31. 39.548 39.548 ↓ 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.012..39.548 rows=1,948 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=30
33. 0.098 0.438 ↓ 1.0 253 1

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

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

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

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

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

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

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

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

  • Buffers: shared hit=4
38. 0.040 0.081 ↑ 1.0 250 1

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

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

  • Buffers: shared hit=3
40. 0.072 0.630 ↑ 1.0 250 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=34
41. 0.142 0.558 ↑ 1.0 250 1

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

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

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

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

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

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

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

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

  • Buffers: shared hit=4
46. 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
47. 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
48. 23.619 23.619 ↓ 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.003..0.003 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=23681
49. 187.824 187.824 ↑ 1.0 250 7,826

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

  • Buffers: shared hit=31304
50. 31.304 31.304 ↑ 1.0 1 7,826

Index Scan using idx_oms_execution_line on oms_execution_line el (cost=0.43..5.75 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=7,826)

  • Index Cond: ((deal_no = oms.deal_no) AND (order_line_id = oms.order_line_id))
  • Buffers: shared hit=31370
51. 15.652 15.652 ↑ 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.002..0.002 rows=1 loops=7,826)

  • Index Cond: (trade_flag_no = el.trade_flag_no)
  • Buffers: shared hit=15652
52. 31.304 31.304 ↑ 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.004..0.004 rows=1 loops=7,826)

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