explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KSUY

Settings
# exclusive inclusive rows x rows loops node
1. 0.302 47,897.188 ↓ 4.0 794 1

Hash Join (cost=11,580.15..11,726.12 rows=201 width=8) (actual time=47,896.670..47,897.188 rows=794 loops=1)

  • Hash Cond: (settlement.id = swapfxincomeexpense.settlement_id)
2. 0.234 0.234 ↓ 1.0 3,567 1

Seq Scan on settlement (cost=0.00..130.61 rows=3,561 width=8) (actual time=0.005..0.234 rows=3,567 loops=1)

3. 0.177 47,896.652 ↓ 3.9 794 1

Hash (cost=11,577.58..11,577.58 rows=205 width=24) (actual time=47,896.652..47,896.652 rows=794 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
4. 0.146 47,896.475 ↓ 3.9 794 1

Hash Left Join (cost=11,445.46..11,577.58 rows=205 width=24) (actual time=47,765.806..47,896.475 rows=794 loops=1)

  • Hash Cond: ((max(ticker_plant_daily_forex_forward_rate_1.date_time_applicable_on)) = premium_ticker_plant_rate_details.date_time_applicable_on)
  • Join Filter: ((((COALESCE(premium_ticker_plant_rate_details.business_unit_id, '0'::bigint) <> 0) AND (trade.business_unit_id = premium_ticker_plant_rate_details.business_unit_id)) OR ((COALESCE(premium_ticker_plant_rate_details.business_unit_ (...)
  • Rows Removed by Join Filter: 1
5. 0.115 47,891.121 ↓ 3.9 794 1

Hash Left Join (cost=10,618.84..10,742.93 rows=205 width=57) (actual time=47,760.585..47,891.121 rows=794 loops=1)

  • Hash Cond: (fx_settlement.id = sub_fx_settlement_1.id)
6. 129.442 47,868.205 ↓ 3.9 794 1

Hash Left Join (cost=3,992.48..4,115.79 rows=205 width=57) (actual time=47,737.778..47,868.205 rows=794 loops=1)

  • Hash Cond: ((max(ticker_plant_daily_forex_forward_rate.date_time_applicable_on)) = swap_premium_ticket_plant_rate_details.date_time_applicable_on)
  • Join Filter: ((((COALESCE(swap_premium_ticket_plant_rate_details.business_unit_id, '0'::bigint) <> 0) AND (trade.business_unit_id = swap_premium_ticket_plant_rate_details.business_unit_id)) OR ((COALESCE(swap_premium_ticket_plant_ (...)
  • Rows Removed by Join Filter: 1
7. 0.146 47,732.709 ↓ 3.9 794 1

Hash Left Join (cost=3,126.59..3,200.42 rows=205 width=77) (actual time=47,731.629..47,732.709 rows=794 loops=1)

  • Hash Cond: (fx_settlement.id = sub_fx_settlement.id)
8. 0.128 3.954 ↓ 3.9 794 1

Hash Join (cost=478.64..551.69 rows=205 width=69) (actual time=3.016..3.954 rows=794 loops=1)

  • Hash Cond: (forex_trade.currency_pair_id = currencypair.id)
9. 0.125 3.809 ↓ 3.9 794 1

Hash Join (cost=476.24..546.47 rows=205 width=69) (actual time=2.997..3.809 rows=794 loops=1)

  • Hash Cond: (trade.business_unit_id = business_unit.id)
10. 0.261 3.666 ↓ 3.9 794 1

Hash Right Join (cost=472.71..540.12 rows=205 width=61) (actual time=2.977..3.666 rows=794 loops=1)

  • Hash Cond: ((interestfxincomeexpense.trade_id = trade.id) AND (interestfxincomeexpense.settlement_id = swapfxincomeexpense.settlement_id))
11. 0.437 0.437 ↓ 1.1 769 1

Seq Scan on fx_income_or_expense interestfxincomeexpense (cost=0.00..62.19 rows=695 width=16) (actual time=0.007..0.437 rows=769 loops=1)

  • Filter: (((status)::text <> 'Reversed'::text) AND (((income_expense_heads)::text = 'InterestOnCashInlay'::text) OR ((income_expense_heads)::text = 'InterestOnCashOutlay'::text)))
  • Rows Removed by Filter: 956
12. 0.155 2.968 ↓ 3.9 794 1

Hash (cost=469.64..469.64 rows=205 width=69) (actual time=2.968..2.968 rows=794 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
13. 0.118 2.813 ↓ 3.9 794 1

Hash Join (cost=441.12..469.64 rows=205 width=69) (actual time=2.502..2.813 rows=794 loops=1)

  • Hash Cond: (forex_trade.deal_type_id = dealtype.id)
14. 0.152 2.692 ↓ 3.8 794 1

Hash Join (cost=440.03..465.72 rows=207 width=77) (actual time=2.497..2.692 rows=794 loops=1)

  • Hash Cond: (fx_settlement.settlement_id = swapfxincomeexpense.settlement_id)
15. 0.050 0.050 ↓ 1.0 921 1

Seq Scan on fx_settlement (cost=0.00..20.18 rows=918 width=25) (actual time=0.003..0.050 rows=921 loops=1)

16. 0.124 2.490 ↓ 3.8 794 1

Hash (cost=437.39..437.39 rows=211 width=52) (actual time=2.490..2.490 rows=794 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 77kB
17. 0.132 2.366 ↓ 3.8 794 1

Hash Join (cost=374.43..437.39 rows=211 width=52) (actual time=1.976..2.366 rows=794 loops=1)

  • Hash Cond: (swapfxincomeexpense.trade_id = trade.id)
18. 0.270 0.270 ↓ 1.0 794 1

Seq Scan on fx_income_or_expense swapfxincomeexpense (cost=0.00..57.88 rows=793 width=16) (actual time=0.004..0.270 rows=794 loops=1)

  • Filter: (((status)::text <> 'Reversed'::text) AND ((income_expense_heads)::text = 'SwapGainOrLoss'::text))
  • Rows Removed by Filter: 931
19. 0.257 1.964 ↓ 1.0 1,807 1

Hash (cost=351.88..351.88 rows=1,804 width=44) (actual time=1.964..1.964 rows=1,807 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 158kB
20. 0.697 1.707 ↓ 1.0 1,807 1

Hash Join (cost=97.59..351.88 rows=1,804 width=44) (actual time=0.565..1.707 rows=1,807 loops=1)

  • Hash Cond: (trade.id = forex_trade.id)
21. 0.453 0.453 ↓ 1.0 6,786 1

Seq Scan on trade (cost=0.00..210.82 rows=6,782 width=20) (actual time=0.002..0.453 rows=6,786 loops=1)

22. 0.171 0.557 ↓ 1.0 1,807 1

Hash (cost=75.04..75.04 rows=1,804 width=24) (actual time=0.557..0.557 rows=1,807 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 115kB
23. 0.386 0.386 ↓ 1.0 1,807 1

Seq Scan on forex_trade (cost=0.00..75.04 rows=1,804 width=24) (actual time=0.002..0.386 rows=1,807 loops=1)

24. 0.002 0.003 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.003..0.003 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.001 0.001 ↑ 1.0 4 1

Seq Scan on rd_forex_deal_type dealtype (cost=0.00..1.04 rows=4 width=8) (actual time=0.001..0.001 rows=4 loops=1)

26. 0.006 0.018 ↑ 1.0 68 1

Hash (cost=2.68..2.68 rows=68 width=16) (actual time=0.018..0.018 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.012 0.012 ↑ 1.0 68 1

Seq Scan on business_unit (cost=0.00..2.68 rows=68 width=16) (actual time=0.002..0.012 rows=68 loops=1)

28. 0.008 0.017 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=16) (actual time=0.017..0.017 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
29. 0.009 0.009 ↑ 1.0 62 1

Seq Scan on rd_currency_pair currencypair (cost=0.00..1.62 rows=62 width=16) (actual time=0.003..0.009 rows=62 loops=1)

30. 0.020 47,728.609 ↓ 97.0 97 1

Hash (cost=2,647.94..2,647.94 rows=1 width=16) (actual time=47,728.609..47,728.609 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
31. 1.492 47,728.589 ↓ 97.0 97 1

HashAggregate (cost=2,647.92..2,647.93 rows=1 width=16) (actual time=47,728.579..47,728.589 rows=97 loops=1)

  • Group Key: sub_fx_settlement.id
32. 47,630.877 47,727.097 ↓ 2,609.0 2,609 1

Nested Loop (cost=549.99..2,647.91 rows=1 width=16) (actual time=56.580..47,727.097 rows=2,609 loops=1)

  • Join Filter: (suashish.ufn_getlastworkingdateofmonth(currencypair_1.base_currency_code, currencypair_1.quoted_currency_code, sub_trade.value_date) = ticker_plant_daily_forex_forward_rate.settlement_date)
  • Rows Removed by Join Filter: 29979
33. 24.395 63.632 ↓ 16,294.0 32,588 1

Hash Join (cost=549.85..2,647.06 rows=2 width=32) (actual time=2.440..63.632 rows=32,588 loops=1)

  • Hash Cond: (sub_trade.business_unit_id = bu.id)
  • Join Filter: (((COALESCE(ticker_plant_daily_forex_forward_rate.business_unit_id, '0'::bigint) <> 0) AND (sub_trade.business_unit_id = ticker_plant_daily_forex_forward_rate.business_unit_id)) OR ((COAL (...)
34. 29.433 39.221 ↓ 4.3 32,588 1

Hash Join (cost=546.32..2,463.36 rows=7,586 width=56) (actual time=2.420..39.221 rows=32,588 loops=1)

  • Hash Cond: ((ticker_plant_daily_forex_forward_rate.date_time_applicable_on)::date = sub_settlement.settlement_date)
  • Join Filter: ((ticker_plant_daily_forex_forward_rate.date_time_applicable_on)::time without time zone <= (sub_fx_settlement.premium_time)::time without time zone)
  • Rows Removed by Join Filter: 21309
35. 7.392 7.392 ↑ 1.0 18,654 1

Seq Scan on ticker_plant_daily_forex_forward_rate (cost=0.00..632.71 rows=18,654 width=28) (actual time=0.014..7.392 rows=18,654 loops=1)

  • Filter: ((tenor)::text <> ALL ('{CASHSPOT,CASHTOM,TOMSPOT}'::text[]))
  • Rows Removed by Filter: 3507
36. 0.130 2.396 ↓ 3.8 921 1

Hash (cost=543.27..543.27 rows=244 width=40) (actual time=2.396..2.396 rows=921 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 79kB
37. 0.038 2.266 ↓ 3.8 921 1

Nested Loop (cost=196.56..543.27 rows=244 width=40) (actual time=0.940..2.266 rows=921 loops=1)

38. 0.259 1.307 ↓ 1.8 921 1

Hash Join (cost=196.27..287.76 rows=517 width=44) (actual time=0.934..1.307 rows=921 loops=1)

  • Hash Cond: (sub_forex_trade.id = sub_settlement.trade_id)
39. 0.118 0.118 ↓ 1.0 1,807 1

Seq Scan on forex_trade sub_forex_trade (cost=0.00..75.04 rows=1,804 width=16) (actual time=0.001..0.118 rows=1,807 loops=1)

40. 0.119 0.930 ↓ 1.0 921 1

Hash (cost=184.80..184.80 rows=918 width=28) (actual time=0.930..0.930 rows=921 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
41. 0.412 0.811 ↓ 1.0 921 1

Hash Join (cost=31.66..184.80 rows=918 width=28) (actual time=0.207..0.811 rows=921 loops=1)

  • Hash Cond: (sub_settlement.id = sub_fx_settlement.settlement_id)
42. 0.220 0.220 ↓ 1.0 3,567 1

Seq Scan on settlement sub_settlement (cost=0.00..130.61 rows=3,561 width=20) (actual time=0.002..0.220 rows=3,567 loops=1)

43. 0.088 0.179 ↓ 1.0 921 1

Hash (cost=20.18..20.18 rows=918 width=24) (actual time=0.179..0.179 rows=921 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
44. 0.091 0.091 ↓ 1.0 921 1

Seq Scan on fx_settlement sub_fx_settlement (cost=0.00..20.18 rows=918 width=24) (actual time=0.002..0.091 rows=921 loops=1)

45. 0.921 0.921 ↑ 1.0 1 921

Index Scan using pk_trade on trade sub_trade (cost=0.28..0.48 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=921)

  • Index Cond: (id = sub_settlement.trade_id)
46. 0.007 0.016 ↑ 1.0 68 1

Hash (cost=2.68..2.68 rows=68 width=16) (actual time=0.016..0.016 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
47. 0.009 0.009 ↑ 1.0 68 1

Seq Scan on business_unit bu (cost=0.00..2.68 rows=68 width=16) (actual time=0.002..0.009 rows=68 loops=1)

48. 32.588 32.588 ↑ 1.0 1 32,588

Index Scan using pk_rd_currency_pair on rd_currency_pair currencypair_1 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=32,588)

  • Index Cond: (id = sub_forex_trade.currency_pair_id)
49. 2.352 6.054 ↑ 1.0 18,654 1

Hash (cost=632.71..632.71 rows=18,654 width=28) (actual time=6.054..6.054 rows=18,654 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1277kB
50. 3.702 3.702 ↑ 1.0 18,654 1

Seq Scan on ticker_plant_daily_forex_forward_rate swap_premium_ticket_plant_rate_details (cost=0.00..632.71 rows=18,654 width=28) (actual time=0.016..3.702 rows=18,654 loops=1)

  • Filter: ((tenor)::text <> ALL ('{CASHSPOT,CASHTOM,TOMSPOT}'::text[]))
  • Rows Removed by Filter: 3507
51. 0.012 22.801 ↓ 10.8 43 1

Hash (cost=6,626.31..6,626.31 rows=4 width=16) (actual time=22.801..22.801 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
52. 0.097 22.789 ↓ 10.8 43 1

HashAggregate (cost=6,626.23..6,626.27 rows=4 width=16) (actual time=22.786..22.789 rows=43 loops=1)

  • Group Key: sub_fx_settlement_1.id
53. 0.185 22.692 ↓ 190.5 762 1

Hash Join (cost=2,656.53..6,626.21 rows=4 width=16) (actual time=9.258..22.692 rows=762 loops=1)

  • Hash Cond: (sub_trade_1.business_unit_id = bu_1.id)
  • Join Filter: (((COALESCE(ticker_plant_daily_forex_forward_rate_1.business_unit_id, '0'::bigint) <> 0) AND (sub_trade_1.business_unit_id = ticker_plant_daily_forex_forward_rate_1.business_unit_id)) OR ((COALESCE(ticker_ (...)
54. 11.490 22.486 ↑ 22.2 762 1

Merge Join (cost=2,653.00..6,220.05 rows=16,953 width=40) (actual time=9.231..22.486 rows=762 loops=1)

  • Merge Cond: (sub_settlement_1.settlement_date = ((ticker_plant_daily_forex_forward_rate_1.date_time_applicable_on)::date))
  • Join Filter: (((ticker_plant_daily_forex_forward_rate_1.date_time_applicable_on)::time without time zone <= (sub_fx_settlement_1.cash_spot_or_tom_time)::time without time zone) AND CASE WHEN ((sub_fx_settlement_1 (...)
  • Rows Removed by Join Filter: 62798
55. 0.171 2.181 ↑ 1.6 585 1

Sort (cost=503.84..506.13 rows=918 width=37) (actual time=2.159..2.181 rows=585 loops=1)

  • Sort Key: sub_settlement_1.settlement_date
  • Sort Method: quicksort Memory: 96kB
56. 0.541 2.010 ↓ 1.0 921 1

Hash Join (cost=196.27..458.66 rows=918 width=37) (actual time=1.034..2.010 rows=921 loops=1)

  • Hash Cond: (sub_trade_1.id = sub_settlement_1.trade_id)
57. 0.449 0.449 ↓ 1.0 6,786 1

Seq Scan on trade sub_trade_1 (cost=0.00..210.82 rows=6,782 width=16) (actual time=0.003..0.449 rows=6,786 loops=1)

58. 0.120 1.020 ↓ 1.0 921 1

Hash (cost=184.80..184.80 rows=918 width=37) (actual time=1.020..1.020 rows=921 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
59. 0.376 0.900 ↓ 1.0 921 1

Hash Join (cost=31.66..184.80 rows=918 width=37) (actual time=0.213..0.900 rows=921 loops=1)

  • Hash Cond: (sub_settlement_1.id = sub_fx_settlement_1.settlement_id)
60. 0.317 0.317 ↓ 1.0 3,567 1

Seq Scan on settlement sub_settlement_1 (cost=0.00..130.61 rows=3,561 width=20) (actual time=0.003..0.317 rows=3,567 loops=1)

61. 0.111 0.207 ↓ 1.0 921 1

Hash (cost=20.18..20.18 rows=918 width=33) (actual time=0.207..0.207 rows=921 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
62. 0.096 0.096 ↓ 1.0 921 1

Seq Scan on fx_settlement sub_fx_settlement_1 (cost=0.00..20.18 rows=918 width=33) (actual time=0.004..0.096 rows=921 loops=1)

63. 5.077 8.815 ↓ 3.2 71,630 1

Sort (cost=2,149.16..2,204.56 rows=22,161 width=27) (actual time=7.017..8.815 rows=71,630 loops=1)

  • Sort Key: ((ticker_plant_daily_forex_forward_rate_1.date_time_applicable_on)::date)
  • Sort Method: quicksort Memory: 2500kB
64. 3.738 3.738 ↑ 1.0 22,161 1

Seq Scan on ticker_plant_daily_forex_forward_rate ticker_plant_daily_forex_forward_rate_1 (cost=0.00..549.61 rows=22,161 width=27) (actual time=0.003..3.738 rows=22,161 loops=1)

65. 0.011 0.021 ↑ 1.0 68 1

Hash (cost=2.68..2.68 rows=68 width=16) (actual time=0.021..0.021 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
66. 0.010 0.010 ↑ 1.0 68 1

Seq Scan on business_unit bu_1 (cost=0.00..2.68 rows=68 width=16) (actual time=0.003..0.010 rows=68 loops=1)

67. 2.584 5.208 ↑ 1.0 22,161 1

Hash (cost=549.61..549.61 rows=22,161 width=27) (actual time=5.208..5.208 rows=22,161 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
68. 2.624 2.624 ↑ 1.0 22,161 1

Seq Scan on ticker_plant_daily_forex_forward_rate premium_ticker_plant_rate_details (cost=0.00..549.61 rows=22,161 width=27) (actual time=0.004..2.624 rows=22,161 loops=1)