explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pIt : with u as(select * from user1 WHERE "user1"."account_id" IN (SELECT generate_series(1,1000))) SELECT * FROM purchase join u on u.id = purchase.user_id

Settings
# exclusive inclusive rows x rows loops node
1. 43,079.051 193,423.922 ↑ 15,277.5 2,000 1

Hash Join (cost=3,280,561.02..8,201,346.14 rows=30,554,912 width=60) (actual time=150,353.493..193,423.922 rows=2,000 loops=1)

  • Hash Cond: (u.id = purchase.user_id)
2.          

CTE u

3. 0.510 5.498 ↑ 25,000.0 1,000 1

Nested Loop (cost=18.08..1,738.02 rows=25,000,000 width=46) (actual time=0.644..5.498 rows=1,000 loops=1)

4. 0.825 0.988 ↓ 5.0 1,000 1

HashAggregate (cost=17.52..19.52 rows=200 width=4) (actual time=0.608..0.988 rows=1,000 loops=1)

  • Group Key: generate_series(1, 1000)
5. 0.161 0.163 ↑ 1.0 1,000 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=4) (actual time=0.005..0.163 rows=1,000 loops=1)

6. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)

7. 4.000 4.000 ↑ 1.0 1 1,000

Index Scan using user1_account_id_idx on user1 (cost=0.56..8.58 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=1,000)

  • Index Cond: (account_id = (generate_series(1, 1000)))
8. 6.047 6.047 ↑ 25,000.0 1,000 1

CTE Scan on u (cost=0.00..500,000.00 rows=25,000,000 width=46) (actual time=0.647..6.047 rows=1,000 loops=1)

9. 28,814.496 150,338.824 ↑ 1.0 100,000,000 1

Hash (cost=1,540,541.00..1,540,541.00 rows=100,000,000 width=14) (actual time=150,338.824..150,338.824 rows=100,000,000 loops=1)

  • Buckets: 2,097,152 Batches: 128 Memory Usage: 53,008kB
10. 121,524.328 121,524.328 ↑ 1.0 100,000,000 1

Seq Scan on purchase (cost=0.00..1,540,541.00 rows=100,000,000 width=14) (actual time=0.026..121,524.328 rows=100,000,000 loops=1)

Planning time : 0.398 ms
Execution time : 193,424.642 ms