#	exclusive	inclusive	rows x	rows	loops	node
1.	1.011	7.671	↑ 25,000.0	2,000	1	→ Nested Loop (cost=564,238.57..566,569.93 rows=50,000,000 width=12) (actual time=4.548..7.671 rows=2,000 loops=1)
2.						CTE user_ids
3.	0.883	3.870	↑ 25,000.0	1,000	1	→ Nested Loop (cost=18.07..1,738.01 rows=25,000,000 width=4) (actual time=0.810..3.870 rows=1,000 loops=1)
4.	0.739	0.987	↓ 5.0	1,000	1	→ HashAggregate (cost=17.51..19.51 rows=200 width=4) (actual time=0.785..0.987 rows=1,000 loops=1) Group Key: generate_series(1, 1000)
5.	0.248	0.248	↑ 1.0	1,000	1	→ Result (cost=0.00..5.01 rows=1,000 width=4) (actual time=0.015..0.248 rows=1,000 loops=1)
6.	2.000	2.000	↑ 1.0	1	1,000	→ Index Scan using user_account_id_idx on user (cost=0.56..8.58 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,000) Index Cond: (account_id = (generate_series(1, 1000)))
7.	0.511	4.660	↓ 5.0	1,000	1	→ HashAggregate (cost=562,500.00..562,502.00 rows=200 width=4) (actual time=4.540..4.660 rows=1,000 loops=1) Group Key: user_ids.id
8.	4.149	4.149	↑ 25,000.0	1,000	1	→ CTE Scan on user_ids (cost=0.00..500,000.00 rows=25,000,000 width=4) (actual time=0.812..4.149 rows=1,000 loops=1)
9.	2.000	2.000	↑ 1.0	2	1,000	→ Index Scan using purchase_user_id_idx on purchase (cost=0.57..11.63 rows=2 width=12) (actual time=0.002..0.002 rows=2 loops=1,000) Index Cond: (user_id = user_ids.id)

                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=564238.57..566569.93 rows=50000000 width=12) (actual time=4.548..7.671 rows=2000 loops=1)
   CTE user_ids
     ->  Nested Loop  (cost=18.07..1738.01 rows=25000000 width=4) (actual time=0.810..3.870 rows=1000 loops=1)
           ->  HashAggregate  (cost=17.51..19.51 rows=200 width=4) (actual time=0.785..0.987 rows=1000 loops=1)
                 Group Key: generate_series(1, 1000)
                 ->  Result  (cost=0.00..5.01 rows=1000 width=4) (actual time=0.015..0.248 rows=1000 loops=1)
           ->  Index Scan using user_account_id_idx on user  (cost=0.56..8.58 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1000)
                 Index Cond: (account_id = (generate_series(1, 1000)))
   ->  HashAggregate  (cost=562500.00..562502.00 rows=200 width=4) (actual time=4.540..4.660 rows=1000 loops=1)
         Group Key: user_ids.id
         ->  CTE Scan on user_ids  (cost=0.00..500000.00 rows=25000000 width=4) (actual time=0.812..4.149 rows=1000 loops=1)
   ->  Index Scan using purchase_user_id_idx on purchase  (cost=0.57..11.63 rows=2 width=12) (actual time=0.002..0.002 rows=2 loops=1000)
         Index Cond: (user_id = user_ids.id)
 Planning time: 0.549 ms
 Execution time: 7.941 ms
(15 rows)

Per node type stats

node type	count	sum of times	% of query
CTE Scan	1	4.149 ms	54.1 %
HashAggregate	2	1.250 ms	16.3 %
Index Scan	2	4.000 ms	52.1 %
Nested Loop	2	1.894 ms	24.7 %
Result	1	0.248 ms	3.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
purchase	1	2.000 ms	26.1 %
Index Scan	1	2.000 ms	100.0 %
user	1	2.000 ms	26.1 %
Index Scan	1	2.000 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9dE : Evaluate and Select

Color mode:

Visible columns:

Optimization(s) for this plan:

Per node type stats

Per table stats