explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jrmE : Optimization for: plan #jB6u

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.037 28,838.512 ↓ 0.0 0 1

Subquery Scan on tx (cost=4,909,520.12..4,918,084.50 rows=1,215 width=8) (actual time=28,838.512..28,838.512 rows=0 loops=1)

  • Filter: ((SubPlan 1) OR (SubPlan 2) OR (SubPlan 3))
  • Rows Removed by Filter: 71
2. 0.048 28,837.836 ↑ 19.6 71 1

Finalize GroupAggregate (cost=4,909,520.12..4,910,255.33 rows=1,389 width=32) (actual time=28,837.277..28,837.836 rows=71 loops=1)

  • Group Key: ho.fund_holding_id
  • Filter: (sum(tx1.units_flow) >= 0.4)
  • Rows Removed by Filter: 35
3. 0.579 28,837.788 ↑ 26.2 106 1

Gather Merge (cost=4,909,520.12..4,910,189.35 rows=2,778 width=64) (actual time=28,837.257..28,837.788 rows=106 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 0.398 28,837.209 ↑ 13.1 106 1

Partial GroupAggregate (cost=4,908,520.09..4,908,868.68 rows=1,389 width=64) (actual time=28,836.757..28,837.209 rows=106 loops=1)

  • Group Key: ho.fund_holding_id
5. 0.282 28,836.811 ↑ 16.1 786 1

Sort (cost=4,908,520.09..4,908,551.64 rows=12,618 width=37) (actual time=28,836.734..28,836.811 rows=786 loops=1)

  • Sort Key: ho.fund_holding_id
  • Sort Method: quicksort Memory: 86kB
6. 7,679.629 28,836.529 ↑ 16.1 786 1

Hash Join (cost=5,170.01..4,907,660.60 rows=12,618 width=37) (actual time=4,112.162..28,836.529 rows=786 loops=1)

  • Hash Cond: (tx1.fund_holding_id = ho.fund_holding_id)
7. 21,156.486 21,156.486 ↓ 1.5 80,664,659 1

Parallel Seq Scan on fund_txns tx1 (cost=0.00..4,760,567.82 rows=54,065,782 width=29) (actual time=0.059..21,156.486 rows=80,664,659 loops=1)

8. 0.023 0.414 ↑ 13.1 106 1

Hash (cost=5,152.65..5,152.65 rows=1,389 width=16) (actual time=0.414..0.414 rows=106 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 21kB
9. 0.391 0.391 ↑ 13.1 106 1

Index Scan using fund_holdings_user_id_fkey_idx on fund_holdings ho (cost=0.56..5,152.65 rows=1,389 width=16) (actual time=0.164..0.391 rows=106 loops=1)

  • Index Cond: (user_id = '21149'::bigint)
10.          

SubPlan (for Subquery Scan)

11. 0.426 0.426 ↓ 0.0 0 71

Index Only Scan using fund_dividends_pkey on fund_dividends div (cost=0.43..28.43 rows=149 width=0) (actual time=0.006..0.006 rows=0 loops=71)

  • Index Cond: (plan_id = tx.plan_id)
  • Filter: (((tx.max_div_date IS NOT NULL) AND (div_date > tx.max_div_date)) OR ((tx.max_div_date IS NULL) AND (div_date >= tx.min_txn_date)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
12. 0.142 0.142 ↓ 0.0 0 71

Index Only Scan using fund_splits_pkey on fund_splits splt (cost=0.28..4.30 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=71)

  • Index Cond: (plan_id = tx.plan_id)
  • Filter: (((tx.max_split_date IS NOT NULL) AND (div_date > tx.max_split_date)) OR ((tx.max_split_date IS NULL) AND (div_date >= tx.min_txn_date)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
13. 0.071 0.071 ↓ 0.0 0 71

Index Only Scan using fund_bonus_pkey on fund_bonus bon (cost=0.28..5.02 rows=11 width=0) (actual time=0.001..0.001 rows=0 loops=71)

  • Index Cond: (plan_id = tx.plan_id)
  • Filter: (((tx.max_bonus_date IS NOT NULL) AND (div_date > tx.max_bonus_date)) OR ((tx.max_bonus_date IS NULL) AND (div_date >= tx.min_txn_date)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 0