explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CLDr

Settings
# exclusive inclusive rows x rows loops node
1. 35.307 2,641.523 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,805.34..31,034.21 rows=1 width=662) (actual time=33.749..2,641.523 rows=3,441 loops=1)

  • Filter: (oms_trade_notification_esb.id IS NULL)
  • Buffers: shared hit=260196
2. 144.687 2,595.893 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,804.91..31,025.98 rows=1 width=459) (actual time=33.694..2,595.893 rows=3,441 loops=1)

  • Join Filter: ((c.code)::text = (sm.opol_country_code)::text)
  • Rows Removed by Join Filter: 856809
  • Buffers: shared hit=249856
3. 140.215 2,368.622 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,804.91..31,016.35 rows=1 width=446) (actual time=33.653..2,368.622 rows=3,441 loops=1)

  • Join Filter: ((c_1.code)::text = (sm.opol_country_code)::text)
  • Rows Removed by Join Filter: 856809
  • Buffers: shared hit=236092
4. 141.441 1,299.337 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,786.38..30,963.96 rows=1 width=414) (actual time=33.146..1,299.337 rows=3,441 loops=1)

  • Join Filter: ((c_2.code)::text = (sm.opol_country_code)::text)
  • Rows Removed by Join Filter: 867132
  • Buffers: shared hit=143178
5. 4.444 160.006 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,767.85..30,910.43 rows=1 width=382) (actual time=32.643..160.006 rows=3,441 loops=1)

  • Buffers: shared hit=64028
6. 4.308 141.798 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,767.43..30,907.85 rows=1 width=375) (actual time=32.632..141.798 rows=3,441 loops=1)

  • Buffers: shared hit=50264
7. 4.814 120.285 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,767.01..30,905.28 rows=1 width=372) (actual time=32.614..120.285 rows=3,441 loops=1)

  • Buffers: shared hit=36499
8. 3.422 108.589 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,766.74..30,904.86 rows=1 width=356) (actual time=32.606..108.589 rows=3,441 loops=1)

  • Buffers: shared hit=25222
9. 3.649 94.844 ↓ 3,441.0 3,441 1

Nested Loop Left Join (cost=30,766.46..30,904.53 rows=1 width=356) (actual time=32.595..94.844 rows=3,441 loops=1)

  • Buffers: shared hit=13945
10. 6.109 53.344 ↓ 3,441.0 3,441 1

Nested Loop Anti Join (cost=30,766.04..30,899.65 rows=1 width=353) (actual time=32.568..53.344 rows=3,441 loops=1)

  • 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: 6882
  • Buffers: shared hit=144
11. 11.429 47.235 ↓ 3,441.0 3,441 1

Merge Join (cost=30,766.04..30,898.56 rows=1 width=377) (actual time=32.551..47.235 rows=3,441 loops=1)

  • Merge Cond: ((oms.deal_no = virtu.deal_no) AND (oms.order_no = virtu.order_number) AND (oms.fund_code = virtu.account) AND ((oms.sedol)::text = (virtu.symbol)::text) AND (oms.allocation_match_id = virtu.order_id) AND (oms.trade_date = virtu.trade_date))
  • Buffers: shared hit=143
12. 3.860 10.461 ↑ 1.1 3,448 1

Sort (cost=8,312.40..8,322.24 rows=3,939 width=74) (actual time=9.213..10.461 rows=3,448 loops=1)

  • Sort Key: oms.deal_no, oms.order_no, oms.fund_code, oms.sedol, oms.allocation_match_id, oms.trade_date
  • Sort Method: quicksort Memory: 581kB
  • Buffers: shared hit=108
13. 6.601 6.601 ↑ 1.1 3,448 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb oms (cost=0.43..8,077.17 rows=3,939 width=74) (actual time=0.038..6.601 rows=3,448 loops=1)

  • Index Cond: ((trade_date >= '2019-01-01'::date) AND (trade_date <= '2019-01-07'::date) AND (instrument_type = ANY ('{10,2509}'::integer[])))
  • Filter: (deal_status <> 7)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=108
14. 9.901 25.345 ↑ 1.7 3,441 1

Sort (cost=22,449.43..22,464.05 rows=5,849 width=303) (actual time=23.328..25.345 rows=3,441 loops=1)

  • Sort Key: virtu.deal_no, virtu.order_number, virtu.account, virtu.symbol, virtu.order_id, virtu.trade_date
  • Sort Method: quicksort Memory: 1871kB
  • Buffers: shared hit=35
15. 15.444 15.444 ↑ 1.7 3,441 1

Index Scan using idx_equity_tca_virtu_trade_date on equity_tca_virtu virtu (cost=0.43..22,083.46 rows=5,849 width=303) (actual time=0.069..15.444 rows=3,441 loops=1)

  • Index Cond: ((trade_date >= '2019-01-01'::date) AND (trade_date <= '2019-01-07'::date))
  • Buffers: shared hit=35
16. 0.000 0.000 ↑ 1.0 2 3,441

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

  • Buffers: shared hit=1
17. 0.007 0.007 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
18. 37.851 37.851 ↑ 1.0 1 3,441

Index Scan using security_master_key_pkey on security_master_key sm (cost=0.42..4.88 rows=1 width=10) (actual time=0.010..0.011 rows=1 loops=3,441)

  • Index Cond: ((sedol)::text = (oms.sedol)::text)
  • Buffers: shared hit=13801
19. 10.323 10.323 ↑ 1.0 1 3,441

Index Scan using broker_pkey on broker b (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=3,441)

  • Index Cond: (oms_no = oms.broker_no)
  • Buffers: shared hit=11277
20. 6.882 6.882 ↑ 1.0 1 3,441

Index Scan using broker_pkey on broker lb (cost=0.28..0.41 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=3,441)

  • Index Cond: (oms_no = b.lead_oms_no)
  • Buffers: shared hit=11277
21. 17.205 17.205 ↑ 1.0 1 3,441

Index Scan using pk__exchange_rate on exchange_rate fx (cost=0.42..2.57 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=3,441)

  • Index Cond: ((valuation_point = oms.trade_date) AND ((oms.instrument_currency)::text = (iso_currency_code)::text))
  • Buffers: shared hit=13765
22. 13.764 13.764 ↑ 1.0 1 3,441

Index Scan using pk__exchange_rate on exchange_rate fx_jpy (cost=0.42..2.57 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=3,441)

  • Index Cond: ((valuation_point = oms.trade_date) AND ((iso_currency_code)::text = 'JPY'::text))
  • Buffers: shared hit=13764
23. 364.596 997.890 ↓ 1.0 253 3,441

Hash Left Join (cost=18.53..50.38 rows=252 width=35) (actual time=0.014..0.290 rows=253 loops=3,441)

  • Hash Cond: ((c_2.code)::text = (cr_1.country_code)::text)
  • Buffers: shared hit=79150
24. 319.876 633.144 ↓ 1.0 253 3,441

Hash Right Join (cost=9.90..38.29 rows=252 width=8) (actual time=0.013..0.184 rows=253 loops=3,441)

  • Hash Cond: ((country_group_1.country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=79147
25. 313.131 313.131 ↑ 1.0 252 3,441

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.012..0.091 rows=252 loops=3,441)

  • Index Cond: (group_code = ANY ('{Americas,APAC,EMEA}'::text[]))
  • Heap Fetches: 419802
  • Buffers: shared hit=79143
26. 0.080 0.137 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=4
28. 0.094 0.150 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=3
30. 368.036 929.070 ↑ 1.0 250 3,441

Hash Left Join (cost=18.53..49.27 rows=250 width=35) (actual time=0.013..0.270 rows=250 loops=3,441)

  • Hash Cond: ((c_1.code)::text = (cr.country_code)::text)
  • Buffers: shared hit=92914
31. 278.569 560.883 ↑ 1.0 250 3,441

Hash Right Join (cost=9.90..36.58 rows=250 width=8) (actual time=0.012..0.163 rows=250 loops=3,441)

  • Hash Cond: ((country_group.country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=92911
32. 282.162 282.162 ↑ 1.0 197 3,441

Index Only Scan using "PK_country_group" on country_group (cost=0.28..24.25 rows=197 width=8) (actual time=0.011..0.082 rows=197 loops=3,441)

  • Index Cond: (group_code = ANY ('{Americas,APAC,MEA}'::text[]))
  • Heap Fetches: 461094
  • Buffers: shared hit=92907
33. 0.086 0.152 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=4
35. 0.080 0.151 ↑ 1.0 250 1

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

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

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

  • Buffers: shared hit=3
37. 82.584 82.584 ↑ 1.0 250 3,441

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

  • Buffers: shared hit=13764
38. 10.323 10.323 ↓ 0.0 0 3,441

Index Scan using idx_oms_trade_notification_esb_cancel on oms_trade_notification_esb (cost=0.43..8.16 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=3,441)

  • Index Cond: ((oms.deal_no = deal_no) AND (deal_no IS NOT NULL) AND (deal_status = 7))
  • Buffers: shared hit=10340
Planning time : 20.459 ms
Execution time : 2,642.900 ms