explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RoIt

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 39,074.248 ↓ 0.0 0 1

Sort (cost=18,718.77..18,718.78 rows=1 width=65) (actual time=39,074.248..39,074.248 rows=0 loops=1)

  • Sort Key: b.createdt
  • Sort Method: quicksort Memory: 25kB
2. 2,975.898 39,074.242 ↓ 0.0 0 1

GroupAggregate (cost=18,717.95..18,718.76 rows=1 width=65) (actual time=39,074.242..39,074.242 rows=0 loops=1)

  • Group Key: b.id, pc.firstname, pc.middlename, pc.lastname, pc.playersegmentid
  • Filter: (max(c.status) = 2)
  • Rows Removed by Filter: 30203
3. 12,301.120 36,098.344 ↓ 221,005.8 5,967,157 1

Sort (cost=18,717.95..18,718.02 rows=27 width=69) (actual time=34,577.324..36,098.344 rows=5,967,157 loops=1)

  • Sort Key: b.id, pc.firstname, pc.middlename, pc.lastname, pc.playersegmentid
  • Sort Method: external merge Disk: 495184kB
4. 2,537.404 23,797.224 ↓ 221,005.8 5,967,157 1

Nested Loop Left Join (cost=1.12..18,717.31 rows=27 width=69) (actual time=0.111..23,797.224 rows=5,967,157 loops=1)

5. 25.426 419.750 ↓ 1,677.9 30,203 1

Nested Loop (cost=0.56..18,598.30 rows=18 width=65) (actual time=0.071..419.750 rows=30,203 loops=1)

6. 3.331 3.331 ↓ 1.0 2,209 1

Seq Scan on playercard pc (cost=0.00..105.91 rows=2,188 width=53) (actual time=0.015..3.331 rows=2,209 loops=1)

  • Filter: isvip
  • Rows Removed by Filter: 104
7. 390.993 390.993 ↓ 14.0 14 2,209

Index Scan using ix_balance_playerid_account_processed_createdt on balance b (cost=0.56..8.44 rows=1 width=20) (actual time=0.032..0.177 rows=14 loops=2,209)

  • Index Cond: ((playerid = pc.playerid) AND (account = pc.account) AND (processed = false))
  • Filter: ((NOT processed) AND (status = 2))
  • Rows Removed by Filter: 66
8. 20,840.070 20,840.070 ↓ 198.0 198 30,203

Index Scan using ix_balance_playerid_account_processed_createdt on balance c (cost=0.56..6.60 rows=1 width=20) (actual time=0.020..0.690 rows=198 loops=30,203)

  • Index Cond: ((b.playerid = playerid) AND (b.account = account) AND (createdt <= b.createdt) AND (createdt >= (b.createdt - '168:00:00'::interval)))
Planning time : 1.547 ms
Execution time : 40,921.395 ms