explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R8Wl

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.062 ↓ 0.0 0 1

Nested Loop Left Join (cost=32,126.07..104,964.82 rows=1 width=831) (actual time=0.062..0.062 rows=0 loops=1)

  • Buffers: shared hit=18
2. 0.000 0.061 ↓ 0.0 0 1

Nested Loop Left Join (cost=32,125.93..104,964.53 rows=1 width=543) (actual time=0.061..0.061 rows=0 loops=1)

  • Buffers: shared hit=18
3. 0.000 0.061 ↓ 0.0 0 1

Nested Loop Left Join (cost=32,125.78..104,964.35 rows=1 width=534) (actual time=0.061..0.061 rows=0 loops=1)

  • Join Filter: ((c.code)::text = (sm.opol_country_code)::text)
  • Buffers: shared hit=18
4. 0.019 0.061 ↓ 0.0 0 1

Hash Right Join (cost=32,125.78..104,954.73 rows=1 width=521) (actual time=0.061..0.061 rows=0 loops=1)

  • Hash Cond: ((el.deal_no = oms.deal_no) AND (el.order_line_id = oms.order_line_id))
  • Buffers: shared hit=18
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on oms_execution_line el (cost=0.00..63,950.25 rows=1,183,825 width=12) (never executed)

6. 0.000 0.042 ↓ 0.0 0 1

Hash (cost=32,125.77..32,125.77 rows=1 width=521) (actual time=0.042..0.042 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=15
7. 0.000 0.042 ↓ 0.0 0 1

Nested Loop Left Join (cost=9,482.63..32,125.77 rows=1 width=521) (actual time=0.042..0.042 rows=0 loops=1)

  • Filter: (oms_trade_notification_esb.id IS NULL)
  • Buffers: shared hit=15
8. 0.001 0.042 ↓ 0.0 0 1

Hash Left Join (cost=9,482.20..19,485.54 rows=1,514 width=521) (actual time=0.042..0.042 rows=0 loops=1)

  • Hash Cond: (oms.trade_date = fx_eur.valuation_point)
  • Buffers: shared hit=15
9. 0.000 0.041 ↓ 0.0 0 1

Hash Left Join (cost=9,473.74..19,456.27 rows=1,514 width=514) (actual time=0.041..0.041 rows=0 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_1.code)::text)
  • Buffers: shared hit=15
10. 0.001 0.041 ↓ 0.0 0 1

Hash Left Join (cost=9,421.35..19,395.53 rows=1,514 width=482) (actual time=0.041..0.041 rows=0 loops=1)

  • Hash Cond: ((sm.opol_country_code)::text = (c_2.code)::text)
  • Buffers: shared hit=15
11. 0.000 0.040 ↓ 0.0 0 1

Nested Loop Left Join (cost=9,367.82..19,333.63 rows=1,514 width=450) (actual time=0.040..0.040 rows=0 loops=1)

  • Buffers: shared hit=15
12. 0.000 0.040 ↓ 0.0 0 1

Merge Left Join (cost=9,367.40..9,404.12 rows=1,514 width=434) (actual time=0.040..0.040 rows=0 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))
  • Join Filter: (oms.trade_date = virtu.trade_date)
  • Buffers: shared hit=15
13. 0.026 0.040 ↓ 0.0 0 1

Sort (cost=3,565.47..3,569.26 rows=1,514 width=138) (actual time=0.040..0.040 rows=0 loops=1)

  • Sort Key: oms.deal_no, oms.order_no, oms.fund_code, oms.sedol, oms.allocation_match_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=15
14. 0.000 0.014 ↓ 0.0 0 1

Hash Left Join (cost=3,418.41..3,485.50 rows=1,514 width=138) (actual time=0.014..0.014 rows=0 loops=1)

  • Hash Cond: (oms.trade_date = fx_jpy.valuation_point)
  • Buffers: shared hit=6
15. 0.000 0.014 ↓ 0.0 0 1

Hash Left Join (cost=3,409.96..3,456.23 rows=1,514 width=131) (actual time=0.014..0.014 rows=0 loops=1)

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=6
16. 0.000 0.014 ↓ 0.0 0 1

Merge Left Join (cost=3,345.36..3,370.82 rows=1,514 width=115) (actual time=0.014..0.014 rows=0 loops=1)

  • Merge Cond: ((oms.instrument_currency)::text = (fx.iso_currency_code)::text)
  • Join Filter: (fx.valuation_point = oms.trade_date)
  • Buffers: shared hit=6
17. 0.006 0.014 ↓ 0.0 0 1

Sort (cost=3,148.61..3,152.39 rows=1,514 width=112) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: oms.instrument_currency
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6
18. 0.008 0.008 ↓ 0.0 0 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb oms (cost=0.43..3,068.64 rows=1,514 width=112) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((trade_date = '2019-01-06'::date) AND (instrument_type = ANY ('{10,2509}'::integer[])))
  • Filter: (deal_status <> 7)
  • Buffers: shared hit=6
19. 0.000 0.000 ↓ 0.0 0

Sort (cost=196.76..197.03 rows=108 width=15) (never executed)

  • Sort Key: fx.iso_currency_code
20. 0.000 0.000 ↓ 0.0 0

Index Scan using pk__exchange_rate on exchange_rate fx (cost=0.42..193.11 rows=108 width=15) (never executed)

  • Index Cond: (valuation_point = '2019-01-06'::date)
21. 0.000 0.000 ↓ 0.0 0

Hash (cost=55.96..55.96 rows=691 width=24) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=27.55..55.96 rows=691 width=24) (never executed)

  • Hash Cond: (b.lead_oms_no = lb.oms_no)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on broker b (cost=0.00..18.91 rows=691 width=8) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash (cost=18.91..18.91 rows=691 width=24) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on broker lb (cost=0.00..18.91 rows=691 width=24) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.44..8.44 rows=1 width=11) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using pk__exchange_rate on exchange_rate fx_jpy (cost=0.42..8.44 rows=1 width=11) (never executed)

  • Index Cond: ((valuation_point = '2019-01-06'::date) AND ((iso_currency_code)::text = 'JPY'::text))
28. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,801.15..5,804.80 rows=1,461 width=324) (never executed)

  • Sort Key: virtu.deal_no, virtu.order_number, virtu.account, virtu.symbol, virtu.order_id
29. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=0.43..5,724.35 rows=1,461 width=324) (never executed)

  • Join Filter: ((virtu.trade_date = exc.trade_date) AND (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))
30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_equity_tca_virtu_trade_date on equity_tca_virtu virtu (cost=0.43..5,683.14 rows=1,461 width=324) (never executed)

  • Index Cond: (trade_date = '2019-01-06'::date)
31. 0.000 0.000 ↓ 0.0 0

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

32. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (trade_date = '2019-01-06'::date)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using security_master_key_pkey on security_master_key sm (cost=0.42..6.55 rows=1 width=23) (never executed)

  • Index Cond: ((sedol)::text = (oms.sedol)::text)
34. 0.000 0.000 ↓ 0.0 0

Hash (cost=50.38..50.38 rows=252 width=35) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=18.53..50.38 rows=252 width=35) (never executed)

  • Hash Cond: ((c_2.code)::text = (cr_1.country_code)::text)
36. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=9.90..38.29 rows=252 width=8) (never executed)

  • Hash Cond: ((country_group_1.country_code)::text = (c_2.code)::text)
37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "PK_country_group" on country_group country_group_1 (cost=0.28..25.20 rows=252 width=8) (never executed)

  • Index Cond: (group_code = ANY ('{Americas,APAC,EMEA}'::text[]))
  • Heap Fetches: 0
38. 0.000 0.000 ↓ 0.0 0

Hash (cost=6.50..6.50 rows=250 width=3) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on country c_2 (cost=0.00..6.50 rows=250 width=3) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Hash (cost=5.50..5.50 rows=250 width=10) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Seq Scan on country_region cr_1 (cost=0.00..5.50 rows=250 width=10) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Hash (cost=49.27..49.27 rows=250 width=35) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=18.53..49.27 rows=250 width=35) (never executed)

  • Hash Cond: ((c_1.code)::text = (cr.country_code)::text)
44. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=9.90..36.58 rows=250 width=8) (never executed)

  • Hash Cond: ((country_group.country_code)::text = (c_1.code)::text)
45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "PK_country_group" on country_group (cost=0.28..24.25 rows=197 width=8) (never executed)

  • Index Cond: (group_code = ANY ('{Americas,APAC,MEA}'::text[]))
  • Heap Fetches: 0
46. 0.000 0.000 ↓ 0.0 0

Hash (cost=6.50..6.50 rows=250 width=3) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Seq Scan on country c_1 (cost=0.00..6.50 rows=250 width=3) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Hash (cost=5.50..5.50 rows=250 width=10) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Seq Scan on country_region cr (cost=0.00..5.50 rows=250 width=10) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.44..8.44 rows=1 width=11) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using pk__exchange_rate on exchange_rate fx_eur (cost=0.42..8.44 rows=1 width=11) (never executed)

  • Index Cond: ((valuation_point = '2019-01-06'::date) AND ((iso_currency_code)::text = 'EUR'::text))
52. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_oms_trade_notification_esb_cancel on oms_trade_notification_esb (cost=0.43..8.34 rows=1 width=8) (never executed)

  • Index Cond: ((oms.deal_no = deal_no) AND (deal_no IS NOT NULL) AND (deal_status = 7))
53. 0.000 0.000 ↓ 0.0 0

Seq Scan on country c (cost=0.00..6.50 rows=250 width=16) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_oms_trade_flag" on oms_trade_flag otf (cost=0.15..0.17 rows=1 width=17) (never executed)

  • Index Cond: (trade_flag_no = el.trade_flag_no)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_trade_flag" on trade_flag tf (cost=0.14..0.19 rows=1 width=28) (never executed)

  • Index Cond: ((name)::text = (otf.trade_flag_name)::text)
Planning time : 11.688 ms
Execution time : 0.926 ms