explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R31u

Settings
# exclusive inclusive rows x rows loops node
1. 5.431 51,151.384 ↑ 1.0 922 1

Nested Loop Left Join (cost=2,647.95..3,314.63 rows=933 width=8) (actual time=51,143.504..51,151.384 rows=922 loops=1)

  • Join Filter: (sub_fx_settlement.id = fx_settlement.id)
  • Rows Removed by Join Filter: 90160
2. 0.060 0.060 ↓ 1.0 921 1

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

3. 2.416 51,145.893 ↓ 98.0 98 921

Materialize (cost=2,647.95..3,280.68 rows=1 width=8) (actual time=55.527..55.533 rows=98 loops=921)

4. 1.678 51,143.477 ↓ 98.0 98 1

Hash Right Join (cost=2,647.95..3,280.68 rows=1 width=8) (actual time=51,140.437..51,143.477 rows=98 loops=1)

  • Hash Cond: (swap_premium_ticket_plant_rate_details.date_time_applicable_on = (max(ticker_plant_daily_forex_forward_rate.date_time_applicable_on)))
5. 1.393 1.393 ↑ 1.0 22,161 1

Seq Scan on ticker_plant_daily_forex_forward_rate swap_premium_ticket_plant_rate_details (cost=0.00..549.61 rows=22,161 width=8) (actual time=0.003..1.393 rows=22,161 loops=1)

6. 0.020 51,140.406 ↓ 97.0 97 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
7. 1.639 51,140.386 ↓ 97.0 97 1

HashAggregate (cost=2,647.92..2,647.93 rows=1 width=16) (actual time=51,140.372..51,140.386 rows=97 loops=1)

  • Group Key: sub_fx_settlement.id
8. 51,002.148 51,138.747 ↓ 2,609.0 2,609 1

Nested Loop (cost=549.99..2,647.91 rows=1 width=16) (actual time=56.228..51,138.747 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
9. 27.934 71.423 ↓ 16,294.0 32,588 1

Hash Join (cost=549.85..2,647.06 rows=2 width=32) (actual time=2.564..71.423 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 ((COALESCE(ticker_plant_ (...)
10. 32.934 43.467 ↓ 4.3 32,588 1

Hash Join (cost=546.32..2,463.36 rows=7,586 width=56) (actual time=2.538..43.467 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
11. 8.023 8.023 ↑ 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.016..8.023 rows=18,654 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 79kB
13. 0.057 2.393 ↓ 3.8 921 1

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

14. 0.310 1.415 ↓ 1.8 921 1

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

  • Hash Cond: (sub_forex_trade.id = sub_settlement.trade_id)
15. 0.211 0.211 ↓ 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.003..0.211 rows=1,807 loops=1)

16. 0.102 0.894 ↓ 1.0 921 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
17. 0.370 0.792 ↓ 1.0 921 1

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

  • Hash Cond: (sub_settlement.id = sub_fx_settlement.settlement_id)
18. 0.233 0.233 ↓ 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.233 rows=3,567 loops=1)

19. 0.096 0.189 ↓ 1.0 921 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
20. 0.093 0.093 ↓ 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.093 rows=921 loops=1)

21. 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)
22. 0.010 0.022 ↑ 1.0 68 1

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

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

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

24. 65.176 65.176 ↑ 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.002 rows=1 loops=32,588)

  • Index Cond: (id = sub_forex_trade.currency_pair_id)