explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kyd3

Settings
# exclusive inclusive rows x rows loops node
1. 960.306 2,288.017 ↑ 1.0 1 1

Aggregate (cost=158,405.26..158,405.27 rows=1 width=32) (actual time=2,288.017..2,288.017 rows=1 loops=1)

2. 58.752 1,327.711 ↓ 9.2 107,842 1

Subquery Scan on result (cost=156,910.70..158,375.95 rows=11,722 width=132) (actual time=1,204.291..1,327.711 rows=107,842 loops=1)

3. 0.000 1,268.959 ↓ 9.2 107,842 1

Gather Merge (cost=156,910.70..158,258.73 rows=11,722 width=212) (actual time=1,204.282..1,268.959 rows=107,842 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 81.284 2,403.366 ↓ 4.6 53,921 2

Sort (cost=155,910.69..155,939.99 rows=11,722 width=212) (actual time=1,192.560..1,201.683 rows=53,921 loops=2)

  • Sort Key: balance_transactions.id
  • Sort Method: quicksort Memory: 14350kB
  • Worker 0: Sort Method: quicksort Memory: 17369kB
5. 138.496 2,322.082 ↓ 4.6 53,921 2

Hash Left Join (cost=2,106.51..155,118.46 rows=11,722 width=212) (actual time=28.487..1,161.041 rows=53,921 loops=2)

  • Hash Cond: (games.game_table_id = game_tables.id)
6. 52.984 2,129.874 ↓ 4.6 53,921 2

Nested Loop Left Join (cost=3.30..152,134.64 rows=11,722 width=124) (actual time=0.864..1,064.937 rows=53,921 loops=2)

  • Join Filter: ((balance_transactions.reference_type)::text = 'Game'::text)
  • Rows Removed by Join Filter: 258
7. 9.266 1,753.364 ↓ 4.6 53,921 2

Nested Loop Left Join (cost=2.72..119,252.00 rows=11,722 width=108) (actual time=0.715..876.682 rows=53,921 loops=2)

  • Join Filter: ((balance_transactions.reference_type)::text = 'BonusIssue'::text)
8. 16.214 1,528.414 ↓ 4.6 53,921 2

Nested Loop Left Join (cost=2.29..92,500.72 rows=11,722 width=93) (actual time=0.625..764.207 rows=53,921 loops=2)

  • Join Filter: ((balance_transactions.reference_type)::text = 'Payment'::text)
  • Rows Removed by Join Filter: 157
9. 34.126 1,296.516 ↓ 4.6 53,921 2

Nested Loop (cost=1.86..65,463.41 rows=11,722 width=77) (actual time=0.553..648.258 rows=53,921 loops=2)

10. 61.910 938.864 ↓ 4.6 53,921 2

Nested Loop (cost=1.44..42,519.61 rows=11,722 width=83) (actual time=0.448..469.432 rows=53,921 loops=2)

11. 30.134 553.428 ↓ 4.6 53,921 2

Nested Loop (cost=1.01..26,418.10 rows=11,722 width=75) (actual time=0.309..276.714 rows=53,921 loops=2)

12. 91.926 91.926 ↓ 4.5 53,921 2

Parallel Index Scan using index_balance_transactions_on_created_at on balance_transactions (cost=0.58..655.99 rows=11,854 width=63) (actual time=0.202..45.963 rows=53,921 loops=2)

  • Index Cond: ((created_at > '2019-07-26 10:10:09'::timestamp without time zone) AND (created_at < '2019-07-26 11:10:09'::timestamp without time zone))
13. 431.368 431.368 ↑ 1.0 1 107,842

Index Scan using accounts_pkey on accounts (cost=0.43..2.17 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=107,842)

  • Index Cond: (id = balance_transactions.account_id)
  • Filter: ((currency)::text <> 'FUN'::text)
14. 323.526 323.526 ↑ 1.0 1 107,842

Index Only Scan using users_pkey on users (cost=0.43..1.37 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=107,842)

  • Index Cond: (id = accounts.user_id)
  • Heap Fetches: 94281
15. 323.526 323.526 ↑ 1.0 1 107,842

Index Scan using index_profiles_on_user_id on profiles (cost=0.43..1.96 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=107,842)

  • Index Cond: (user_id = accounts.user_id)
16. 215.684 215.684 ↓ 0.0 0 107,842

Index Scan using payments_pkey on payments (cost=0.43..2.29 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=107,842)

  • Index Cond: (balance_transactions.reference_id = id)
17. 215.684 215.684 ↓ 0.0 0 107,842

Index Scan using bonus_issues_pkey on bonus_issues (cost=0.43..2.27 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=107,842)

  • Index Cond: (balance_transactions.reference_id = id)
18. 323.526 323.526 ↑ 1.0 1 107,842

Index Scan using games_pkey on games (cost=0.58..2.79 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=107,842)

  • Index Cond: (balance_transactions.reference_id = id)
19. 21.992 53.712 ↑ 1.0 56,351 2

Hash (cost=1,394.76..1,394.76 rows=56,676 width=16) (actual time=26.855..26.856 rows=56,351 loops=2)

  • Buckets: 65536 Batches: 1 Memory Usage: 3404kB
20. 31.720 31.720 ↑ 1.0 56,351 2

Seq Scan on game_tables (cost=0.00..1,394.76 rows=56,676 width=16) (actual time=0.034..15.860 rows=56,351 loops=2)

Planning time : 9.054 ms
Execution time : 2,299.605 ms