#	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

Sort  (cost=18718.77..18718.78 rows=1 width=65) (actual time=39074.248..39074.248 rows=0 loops=1)
  Sort Key: b.createdt
  Sort Method: quicksort  Memory: 25kB
  ->  GroupAggregate  (cost=18717.95..18718.76 rows=1 width=65) (actual time=39074.242..39074.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
        ->  Sort  (cost=18717.95..18718.02 rows=27 width=69) (actual time=34577.324..36098.344 rows=5967157 loops=1)
              Sort Key: b.id, pc.firstname, pc.middlename, pc.lastname, pc.playersegmentid
              Sort Method: external merge  Disk: 495184kB
              ->  Nested Loop Left Join  (cost=1.12..18717.31 rows=27 width=69) (actual time=0.111..23797.224 rows=5967157 loops=1)
                    ->  Nested Loop  (cost=0.56..18598.30 rows=18 width=65) (actual time=0.071..419.750 rows=30203 loops=1)
                          ->  Seq Scan on playercard pc  (cost=0.00..105.91 rows=2188 width=53) (actual time=0.015..3.331 rows=2209 loops=1)
                                Filter: isvip
                                Rows Removed by Filter: 104
                          ->  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=2209)
                                Index Cond: ((playerid = pc.playerid) AND (account = pc.account) AND (processed = false))
                                Filter: ((NOT processed) AND (status = 2))
                                Rows Removed by Filter: 66
                    ->  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=30203)
                          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: 40921.395 ms

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	2,975.898 ms	7.6 %
Index Scan	2	21,231.063 ms	54.3 %
Nested Loop	1	25.426 ms	0.1 %
Nested Loop Left Join	1	2,537.404 ms	6.5 %
Seq Scan	1	3.331 ms	0.0 %
Sort	2	12,301.126 ms	31.5 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
balance	2	21,231.063 ms	54.3 %
Index Scan	2	21,231.063 ms	100.0 %
playercard	1	3.331 ms	0.0 %
Seq Scan	1	3.331 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RoIt

Color mode:

Visible columns:

Per node type stats

Per table stats