explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3r6j

Settings
# exclusive inclusive rows x rows loops node
1. 30.611 411,720.632 ↑ 1.1 18 1

GroupAggregate (cost=132,676.13..230,143.85 rows=19 width=36) (actual time=410,946.488..411,720.632 rows=18 loops=1)

  • Group Key: accounts.currency
2. 5.382 410,612.348 ↓ 3.3 12,210 1

Sort (cost=132,676.13..132,685.38 rows=3,702 width=29) (actual time=410,608.879..410,612.348 rows=12,210 loops=1)

  • Sort Key: accounts.currency
  • Sort Method: quicksort Memory: 1338kB
3. 21.648 410,606.966 ↓ 3.3 12,210 1

Gather (cost=122,876.08..132,456.71 rows=3,702 width=29) (actual time=2,279.758..410,606.966 rows=12,210 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
4. 11.415 410,585.318 ↓ 2.8 2,035 6

Nested Loop Left Join (cost=121,876.08..131,086.51 rows=740 width=29) (actual time=2,415.901..410,585.318 rows=2,035 loops=6)

5. 230.743 410,573.899 ↓ 2.8 2,035 6

Nested Loop (cost=121,875.65..129,397.35 rows=740 width=29) (actual time=2,415.888..410,573.899 rows=2,035 loops=6)

6. 789.743 410,343.046 ↓ 2.8 2,035 6

Merge Semi Join (cost=121,875.22..127,792.28 rows=740 width=33) (actual time=2,415.211..410,343.046 rows=2,035 loops=6)

  • Merge Cond: (bonus_issues.id = balance_transactions.reference_id)
7. 3.196 224.409 ↑ 1.3 2,035 6

Sort (cost=121,874.57..121,881.15 rows=2,631 width=33) (actual time=221.903..224.409 rows=2,035 loops=6)

  • Sort Key: bonus_issues.id
  • Sort Method: quicksort Memory: 204kB
  • Worker 0: Sort Method: quicksort Memory: 263kB
  • Worker 1: Sort Method: quicksort Memory: 207kB
  • Worker 2: Sort Method: quicksort Memory: 199kB
  • Worker 3: Sort Method: quicksort Memory: 258kB
  • Worker 4: Sort Method: quicksort Memory: 257kB
8. 221.213 221.213 ↑ 1.3 2,035 6

Parallel Seq Scan on bonus_issues (cost=0.00..121,725.11 rows=2,631 width=33) (actual time=3.330..221.213 rows=2,035 loops=6)

  • Filter: (((stage)::text = ANY ('{wait,handle_bets}'::text[])) OR (((stage)::text <> 'issued'::text) AND (updated_at >= '2019-05-01 00:00:00'::timestamp without time zone)))
  • Rows Removed by Filter: 394386
9. 409,328.894 409,328.894 ↓ 3.5 2,321,053 6

Index Scan using index_balance_transactions_on_reference_type_and_reference_id on balance_transactions (cost=0.58..1,337,604.86 rows=667,045 width=8) (actual time=0.324..409,328.894 rows=2,321,053 loops=6)

  • Index Cond: ((reference_type)::text = 'BonusIssue'::text)
  • Filter: ((amount_cents > 0) AND (created_at < '2019-05-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1169385
10. 0.110 0.110 ↑ 1.0 1 12,210

Index Scan using accounts_pkey on accounts (cost=0.43..2.17 rows=1 width=12) (actual time=0.110..0.110 rows=1 loops=12,210)

  • Index Cond: (id = bonus_issues.account_id)
11. 0.004 0.004 ↓ 0.0 0 12,210

Index Scan using payments_pkey on payments (cost=0.43..2.28 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=12,210)

  • Index Cond: (bonus_issues.payment_id = id)
12.          

SubPlan (forGroupAggregate)

13. 12.210 1,001.220 ↑ 1.0 1 12,210

Aggregate (cost=3.28..3.29 rows=1 width=32) (actual time=0.082..0.082 rows=1 loops=12,210)

14. 989.010 989.010 ↑ 5.7 3 12,210

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions (cost=0.56..3.24 rows=17 width=8) (actual time=0.057..0.081 rows=3 loops=12,210)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: (((action)::text = ANY ('{bet,win}'::text[])) AND (dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone))
15. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3.26..3.27 rows=1 width=32) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions dense_balance_transactions_1 (cost=0.56..3.24 rows=9 width=8) (never executed)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: ((dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone) AND ((action)::text = 'bet'::text))
17. 11.936 71.616 ↑ 1.0 1 11,936

Aggregate (cost=3.28..3.29 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=11,936)

18. 59.680 59.680 ↑ 5.7 3 11,936

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions dense_balance_transactions_2 (cost=0.56..3.24 rows=17 width=8) (actual time=0.003..0.005 rows=3 loops=11,936)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: (((action)::text = ANY ('{bet,win}'::text[])) AND (dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone))
19. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3.26..3.27 rows=1 width=32) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions dense_balance_transactions_3 (cost=0.56..3.24 rows=9 width=8) (never executed)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: ((dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone) AND ((action)::text = 'bet'::text))
21. 0.548 2.740 ↑ 1.0 1 274

Aggregate (cost=3.28..3.29 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=274)

22. 2.192 2.192 ↑ 2.4 7 274

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions dense_balance_transactions_4 (cost=0.56..3.24 rows=17 width=8) (actual time=0.003..0.008 rows=7 loops=274)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: (((action)::text = ANY ('{bet,win}'::text[])) AND (dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone))
23. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3.26..3.27 rows=1 width=32) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions dense_balance_transactions_5 (cost=0.56..3.24 rows=9 width=8) (never executed)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: ((dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone) AND ((action)::text = 'bet'::text))
25. 0.466 2.097 ↑ 1.0 1 233

Aggregate (cost=3.28..3.29 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=233)

26. 1.631 1.631 ↑ 2.4 7 233

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions dense_balance_transactions_6 (cost=0.56..3.24 rows=17 width=8) (actual time=0.003..0.007 rows=7 loops=233)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: (((action)::text = ANY ('{bet,win}'::text[])) AND (dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone))
27. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3.26..3.27 rows=1 width=32) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using index_dense_balance_transactions_on_bonus_issue_id on dense_balance_transactions dense_balance_transactions_7 (cost=0.56..3.24 rows=9 width=8) (never executed)

  • Index Cond: (bonus_issue_id = bonus_issues.id)
  • Filter: ((dense_created_at < '2019-05-01 00:00:00'::timestamp without time zone) AND ((action)::text = 'bet'::text))
Planning time : 2.911 ms
Execution time : 411,734.046 ms