explain.depesz.com

PostgreSQL's explain analyze made readable

Result: isV5

Settings
# exclusive inclusive rows x rows loops node
1. 3,052.087 49,494.602 ↓ 207,609.0 207,609 1

Nested Loop Left Join (cost=1,360,250.84..1,562,288.24 rows=1 width=831) (actual time=26,774.328..49,494.602 rows=207,609 loops=1)

  • Buffers: shared hit=3412218 read=6437, temp read=20415 written=20347
2. 306.595 45,404.470 ↓ 207,609.0 207,609 1

Nested Loop Left Join (cost=1,360,250.70..1,562,287.96 rows=1 width=543) (actual time=26,774.232..45,404.470 rows=207,609 loops=1)

  • Buffers: shared hit=2997000 read=6437, temp read=20415 written=20347
3. 319.933 44,682.657 ↓ 207,609.0 207,609 1

Nested Loop Left Join (cost=1,360,250.55..1,562,287.78 rows=1 width=534) (actual time=26,774.219..44,682.657 rows=207,609 loops=1)

  • Buffers: shared hit=2581781 read=6437, temp read=20415 written=20347
4. 10,006.678 43,117.070 ↓ 207,609.0 207,609 1

Nested Loop Left Join (cost=1,360,250.13..1,562,285.92 rows=1 width=534) (actual time=26,774.195..43,117.070 rows=207,609 loops=1)

  • Join Filter: ((c.code)::text = (sm.opol_country_code)::text)
  • Rows Removed by Join Filter: 51694654
  • Buffers: shared hit=1749647 read=6437, temp read=20415 written=20347
5. 536.566 27,297.340 ↓ 207,609.0 207,609 1

Hash Right Join (cost=1,360,250.13..1,562,276.30 rows=1 width=521) (actual time=26,774.127..27,297.340 rows=207,609 loops=1)

  • Hash Cond: (oms_trade_notification_esb.deal_no = oms.deal_no)
  • Filter: (oms_trade_notification_esb.id IS NULL)
  • Rows Removed by Filter: 857
  • Buffers: shared hit=919211 read=6437, temp read=20415 written=20347
6. 123.547 123.547 ↓ 1.1 36,088 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb (cost=0.43..171,170.04 rows=33,418 width=8) (actual time=0.026..123.547 rows=36,088 loops=1)

  • Index Cond: (deal_status = 7)
  • Filter: (deal_no IS NOT NULL)
  • Buffers: shared hit=15989
7. 762.877 26,637.227 ↓ 1.3 208,464 1

Hash (cost=1,347,659.66..1,347,659.66 rows=157,603 width=521) (actual time=26,637.227..26,637.227 rows=208,464 loops=1)

  • Buckets: 8192 Batches: 32 Memory Usage: 3567kB
  • Buffers: shared hit=903222 read=6437, temp read=7215 written=20183
8. 227.090 25,874.350 ↓ 1.3 208,464 1

Hash Left Join (cost=341,070.31..1,347,659.66 rows=157,603 width=521) (actual time=2,527.734..25,874.350 rows=208,464 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=903222 read=6437, temp read=7215 written=7209
9. 225.704 25,646.480 ↓ 1.3 208,464 1

Hash Left Join (cost=341,017.92..1,346,738.35 rows=157,603 width=489) (actual time=2,526.941..25,646.480 rows=208,464 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=903188 read=6437, temp read=7215 written=7209
10. 202.084 25,419.525 ↓ 1.3 208,464 1

Merge Left Join (cost=340,964.39..1,345,813.67 rows=157,603 width=457) (actual time=2,525.639..25,419.525 rows=208,464 loops=1)

  • Merge Cond: (oms.trade_date = fx_eur.valuation_point)
  • Buffers: shared hit=903158 read=6437, temp read=7215 written=7209
11. 199.580 25,138.309 ↓ 1.3 208,464 1

Merge Left Join (cost=336,539.84..1,339,429.52 rows=157,603 width=450) (actual time=2,502.198..25,138.309 rows=208,464 loops=1)

  • Merge Cond: (oms.trade_date = fx_jpy.valuation_point)
  • Buffers: shared hit=901705 read=6437, temp read=7215 written=7209
12. 219.930 24,880.768 ↓ 1.3 208,464 1

Nested Loop Left Join (cost=332,115.30..1,333,045.37 rows=157,603 width=443) (actual time=2,478.960..24,880.768 rows=208,464 loops=1)

  • Buffers: shared hit=900252 read=6437, temp read=7215 written=7209
13. 291.778 3,242.233 ↓ 1.1 174,135 1

Merge Left Join (cost=332,114.75..335,628.33 rows=157,603 width=147) (actual time=2,467.556..3,242.233 rows=174,135 loops=1)

  • Merge Cond: ((oms.trade_date = fx.valuation_point) AND ((oms.instrument_currency)::text = (fx.iso_currency_code)::text))
  • Buffers: shared hit=8763, temp read=7215 written=7209
14. 1,171.208 2,117.896 ↓ 1.1 174,135 1

Sort (cost=304,733.66..305,127.66 rows=157,603 width=144) (actual time=1,623.615..2,117.896 rows=174,135 loops=1)

  • Sort Key: oms.trade_date, oms.instrument_currency
  • Sort Method: external merge Disk: 28704kB
  • Buffers: shared hit=7310, temp read=6508 written=6502
15. 94.231 946.688 ↓ 1.1 174,135 1

Hash Left Join (cost=10,368.18..279,812.34 rows=157,603 width=144) (actual time=156.281..946.688 rows=174,135 loops=1)

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=7310, temp read=2910 written=2904
16. 327.276 851.321 ↓ 1.1 174,135 1

Hash Left Join (cost=10,303.58..277,580.70 rows=157,603 width=128) (actual time=155.094..851.321 rows=174,135 loops=1)

  • Hash Cond: ((oms.sedol)::text = (sm.sedol)::text)
  • Buffers: shared hit=7286, temp read=2910 written=2904
17. 369.571 369.571 ↓ 1.1 174,135 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb oms (cost=0.43..258,396.51 rows=157,603 width=112) (actual time=0.058..369.571 rows=174,135 loops=1)

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

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

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

  • Buffers: shared hit=3141
20. 0.225 1.136 ↑ 1.0 691 1

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

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

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

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

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

  • Buffers: shared hit=12
23. 0.234 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
24. 0.173 0.173 ↑ 1.0 691 1

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

  • Buffers: shared hit=12
25. 66.648 832.559 ↓ 1.8 386,919 1

Materialize (cost=26,458.75..27,539.29 rows=216,108 width=15) (actual time=706.376..832.559 rows=386,919 loops=1)

  • Buffers: shared hit=1453, temp read=707 written=707
26. 698.953 765.911 ↑ 1.0 216,108 1

Sort (cost=26,458.75..26,999.02 rows=216,108 width=15) (actual time=706.371..765.911 rows=216,108 loops=1)

  • Sort Key: fx.valuation_point, fx.iso_currency_code
  • Sort Method: external sort Disk: 5656kB
  • Buffers: shared hit=1453, temp read=707 written=707
27. 66.958 66.958 ↑ 1.0 216,108 1

Seq Scan on exchange_rate fx (cost=0.00..3,614.08 rows=216,108 width=15) (actual time=0.009..66.958 rows=216,108 loops=1)

  • Buffers: shared hit=1453
28. 522.405 21,418.605 ↑ 1.0 1 174,135

Nested Loop Anti Join (cost=0.56..6.32 rows=1 width=324) (actual time=0.097..0.123 rows=1 loops=174,135)

  • 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=891489 read=6437
29. 20,896.200 20,896.200 ↑ 1.0 1 174,135

Index Scan using idx_equity_tca_virtu_omskey on equity_tca_virtu virtu (cost=0.56..5.23 rows=1 width=324) (actual time=0.095..0.120 rows=1 loops=174,135)

  • 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=891488 read=6437
30. 0.000 0.000 ↑ 1.0 2 199,898

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

  • Buffers: shared hit=1
31. 0.009 0.009 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
32. 35.470 57.961 ↓ 129.2 205,727 1

Sort (cost=4,239.02..4,243.00 rows=1,592 width=11) (actual time=22.938..57.961 rows=205,727 loops=1)

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

  • Filter: ((iso_currency_code)::text = 'JPY'::text)
  • Rows Removed by Filter: 214160
  • Buffers: shared hit=1453
34. 56.878 79.132 ↓ 129.2 205,727 1

Sort (cost=4,239.02..4,243.00 rows=1,592 width=11) (actual time=22.736..79.132 rows=205,727 loops=1)

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

  • Filter: ((iso_currency_code)::text = 'EUR'::text)
  • Rows Removed by Filter: 214160
  • Buffers: shared hit=1453
36. 0.111 1.251 ↓ 1.0 253 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=30
37. 0.195 1.140 ↓ 1.0 253 1

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

  • Hash Cond: ((c_2.code)::text = (cr_1.country_code)::text)
  • Buffers: shared hit=30
38. 0.178 0.734 ↓ 1.0 253 1

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

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

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

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

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

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

  • Buffers: shared hit=4
42. 0.118 0.211 ↑ 1.0 250 1

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

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

Seq Scan on country_region cr_1 (cost=0.00..5.50 rows=250 width=10) (actual time=0.006..0.093 rows=250 loops=1)

  • Buffers: shared hit=3
44. 0.084 0.780 ↑ 1.0 250 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=34
45. 0.164 0.696 ↑ 1.0 250 1

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

  • Hash Cond: ((c_1.code)::text = (cr.country_code)::text)
  • Buffers: shared hit=34
46. 0.107 0.390 ↑ 1.0 250 1

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

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

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

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

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

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

  • Buffers: shared hit=4
50. 0.087 0.142 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=3
52. 5,813.052 5,813.052 ↑ 1.0 250 207,609

Seq Scan on country c (cost=0.00..6.50 rows=250 width=16) (actual time=0.002..0.028 rows=250 loops=207,609)

  • Buffers: shared hit=830436
53. 1,245.654 1,245.654 ↑ 1.0 1 207,609

Index Scan using idx_oms_execution_line on oms_execution_line el (cost=0.43..1.85 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=207,609)

  • Index Cond: ((deal_no = oms.deal_no) AND (order_line_id = oms.order_line_id))
  • Buffers: shared hit=832134
54. 415.218 415.218 ↑ 1.0 1 207,609

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=207,609)

  • Index Cond: (trade_flag_no = el.trade_flag_no)
  • Buffers: shared hit=415219
55. 1,038.045 1,038.045 ↑ 1.0 1 207,609

Index Scan using "PK_trade_flag" on trade_flag tf (cost=0.14..0.19 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=207,609)

  • Index Cond: ((name)::text = (otf.trade_flag_name)::text)
  • Buffers: shared hit=415218
Planning time : 22.543 ms
Execution time : 49,538.544 ms