explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8z6f : Optimization for: plan #sJL6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=24,538,884.26..24,538,884.27 rows=1 width=32) (actual rows= loops=)

  • Output: sum(((((sum(tx.investment_flow)) + COALESCE((sum(pis.interest_amount)), 0.00)) + round((((sum(tx.investment_flow)) * (('2020-01-31'::date - COALESCE((max(pis.txn_date)), (max(tx.txn_date)))))::numeric) * 0.00021917808219178082), 2)) / c.rate_divisor))
2. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1.85..24,538,883.61 rows=29 width=77) (actual rows= loops=)

  • Output: (sum(tx.investment_flow)), (sum(pis.interest_amount)), (max(pis.txn_date)), (max(tx.txn_date)), c.rate_divisor
  • Inner Unique: true
  • Merge Cond: (ho.ppf_holding_id = pis.ppf_holding_id)
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.28..1,476.78 rows=1 width=49) (actual rows= loops=)

  • Output: c.rate_divisor, (sum(tx.investment_flow)), (max(tx.txn_date)), ho.ppf_holding_id
4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.86..1,474.32 rows=1 width=44) (actual rows= loops=)

  • Output: ho.ppf_holding_id, sum(tx.investment_flow), max(tx.txn_date)
  • Group Key: ho.ppf_holding_id
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..1,474.12 rows=26 width=17) (actual rows= loops=)

  • Output: ho.ppf_holding_id, tx.investment_flow, tx.txn_date
6. 0.000 0.000 ↓ 0.0

Index Scan using ppf_holdings_pkey on eusr.ppf_holdings ho (cost=0.42..2.45 rows=1 width=8) (actual rows= loops=)

  • Output: ho.ppf_holding_id, ho.investor_name, ho.notes, ho.user_id, ho.creation_ts, ho.modified_ts
  • Index Cond: (ho.ppf_holding_id = ANY ('{11708643}'::integer[]))
  • Filter: (ho.user_id = 9)
7. 0.000 0.000 ↓ 0.0

Index Scan using ppf_txns_ppf_holding_id_fkey_idx on eusr.ppf_txns tx (cost=0.44..1,449.88 rows=2,179 width=17) (actual rows= loops=)

  • Output: tx.ppf_txn_id, tx.ppf_holding_id, tx.txn_type_code, tx.investment_flow, tx.cash_flow, tx.txn_date, tx.notes, tx.sip_series_id, tx.creation_ts, tx.modified_ts
  • Index Cond: (tx.ppf_holding_id = ho.ppf_holding_id)
  • Filter: (tx.txn_date <= '2020-01-31'::date)
8. 0.000 0.000 ↓ 0.0

Index Scan using currency_rates_pkey on fs.currency_rates c (cost=0.41..2.44 rows=1 width=5) (actual rows= loops=)

  • Output: c.currency_from_code, c.currency_to_code, c.rate_date, c.rate_divisor
  • Index Cond: (((c.currency_from_code)::text = 'INR'::text) AND ((c.currency_to_code)::text = 'INR'::text) AND (c.rate_date = '2020-01-31'::date))
9. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.57..24,537,333.48 rows=5,844 width=44) (actual rows= loops=)

  • Output: pis.ppf_holding_id, max(pis.txn_date), sum(pis.interest_amount)
  • Group Key: pis.ppf_holding_id
10. 0.000 0.000 ↓ 0.0

Index Scan using ppf_interest_series_ppf_holding_id_fkey_idx on eusr.ppf_interest_series pis (cost=0.57..24,537,213.24 rows=6,292 width=18) (actual rows= loops=)

  • Output: pis.interest_series_id, pis.ppf_holding_id, pis.txn_date, pis.interest_amount, pis.creation_ts, pis.modified_ts
  • Index Cond: (pis.ppf_holding_id = ANY ('{11708643}'::integer[]))
  • Filter: ((pis.txn_date <= '2020-01-31'::date) AND (pis.txn_date > COALESCE((SubPlan 2), '2019-03-31'::date)))
11.          

SubPlan (for Index Scan)

12. 0.000 0.000 ↓ 0.0

Result (cost=1,299.38..1,299.39 rows=1 width=4) (actual rows= loops=)

  • Output: $1
13.          

Initplan (for Result)

14. 0.000 0.000 ↓ 0.0

Limit (cost=0.44..1,299.38 rows=1 width=4) (actual rows= loops=)

  • Output: tx_1.txn_date
15. 0.000 0.000 ↓ 0.0

Index Scan Backward using ppf_txns_txn_date_idx on eusr.ppf_txns tx_1 (cost=0.44..732,604.14 rows=564 width=4) (actual rows= loops=)

  • Output: tx_1.txn_date
  • Index Cond: ((tx_1.txn_date IS NOT NULL) AND (tx_1.txn_date <= '2020-01-31'::date))
  • Filter: ((tx_1.ppf_holding_id = pis.ppf_holding_id) AND ((tx_1.txn_type_code)::text = 'INTACCRUED'::text))