explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1KZ7 : Optimization for: plan #E1NZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.382 409.258 ↑ 1.0 1 1

Aggregate (cost=285,297.42..285,297.43 rows=1 width=8) (actual time=409.257..409.258 rows=1 loops=1)

  • Buffers: shared hit=51964 read=4693
  • JIT:
  • Functions: 74
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 12.968 ms, Inlining 0.000 ms, Optimization 2.894 ms, Emission 85.809 ms, Total 101.670 ms
2. 0.754 408.876 ↓ 25.5 1,404 1

Nested Loop (cost=285,204.35..285,297.29 rows=55 width=8) (actual time=402.291..408.876 rows=1,404 loops=1)

  • Buffers: shared hit=51964 read=4693
3. 0.649 402.506 ↓ 25.5 1,404 1

HashAggregate (cost=285,203.92..285,204.47 rows=55 width=8) (actual time=402.269..402.506 rows=1,404 loops=1)

  • Group Key: accounts.user_id
  • Buffers: shared hit=46618 read=4693
4. 0.000 401.857 ↓ 25.5 1,404 1

Finalize GroupAggregate (cost=278,467.55..285,203.24 rows=55 width=52) (actual time=397.271..401.857 rows=1,404 loops=1)

  • Group Key: accounts.id
  • Filter: (((((((- (COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'bet'::text)), '0'::numeric) + COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'win'::text)), '0'::numeric))) + COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'issued_bonus'::text)), '0'::numeric)) - COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'canceled_bonus'::text)), '0'::numeric)) + COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'addition'::text)), '0'::numeric)) - COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'subtraction'::text)), '0'::numeric)) >= '-50000000000000'::numeric) AND ((((((- (COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'bet'::text)), '0'::numeric) + COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'win'::text)), '0'::numeric))) + COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'issued_bonus'::text)), '0'::numeric)) - COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'canceled_bonus'::text)), '0'::numeric)) + COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'addition'::text)), '0'::numeric)) - COALESCE(sum(dense_balance_transactions.amount_cents) FILTER (WHERE ((dense_balance_transactions.action)::text = 'subtraction'::text)), '0'::numeric)) <= '50000000000000'::numeric))
  • Buffers: shared hit=46618 read=4693
5. 87.673 441.002 ↑ 23.3 1,404 1

Gather Merge (cost=278,467.55..282,339.89 rows=32,724 width=212) (actual time=397.244..441.002 rows=1,404 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=119833 read=12991
6. 0.180 353.329 ↑ 31.1 351 4 / 4

Sort (cost=277,467.51..277,494.78 rows=10,908 width=212) (actual time=353.299..353.329 rows=351 loops=4)

  • Sort Key: accounts.id
  • Sort Method: quicksort Memory: 107kB
  • Worker 0: Sort Method: quicksort Memory: 63kB
  • Worker 1: Sort Method: quicksort Memory: 61kB
  • Worker 2: Sort Method: quicksort Memory: 62kB
  • Buffers: shared hit=119833 read=12991
7. 8.659 353.149 ↑ 31.1 351 4 / 4

Partial HashAggregate (cost=276,463.26..276,735.96 rows=10,908 width=212) (actual time=352.665..353.149 rows=351 loops=4)

  • Group Key: accounts.id
  • Buffers: shared hit=119812 read=12991
8. 4.707 344.490 ↑ 2.0 18,344 4 / 4

Nested Loop (cost=0.56..275,287.60 rows=36,174 width=32) (actual time=22.595..344.490 rows=18,344 loops=4)

  • Buffers: shared hit=119812 read=12991
9. 117.043 117.043 ↑ 1.1 3,182 4 / 4

Parallel Seq Scan on accounts (cost=0.00..28,892.67 rows=3,519 width=20) (actual time=22.457..117.043 rows=3,182 loops=4)

  • Filter: ((currency)::text = 'USD'::text)
  • Rows Removed by Filter: 514624
  • Buffers: shared hit=20541
10. 222.740 222.740 ↑ 151.2 6 12,728 / 4

Index Scan using index_dense_balance_transactions_on_account_id on dense_balance_transactions (cost=0.56..60.95 rows=907 width=20) (actual time=0.012..0.070 rows=6 loops=12,728)

  • Index Cond: (account_id = accounts.id)
  • Filter: ((action)::text = ANY ('{bet,win,issued_bonus,canceled_bonus,addition,subtraction}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=99271 read=12991
11. 5.616 5.616 ↑ 1.0 1 1,404

Index Only Scan using users_pkey on users (cost=0.43..1.69 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,404)

  • Index Cond: (id = accounts.user_id)
  • Heap Fetches: 246
  • Buffers: shared hit=5346
Planning time : 1.137 ms
Execution time : 458.691 ms