explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1UPZ

Settings
# exclusive inclusive rows x rows loops node
1. 490.628 490.628 ↑ 3.7 50,000 1

CTE Scan on normalised_data (cost=25,980.74..29,723.26 rows=187,126 width=32) (actual time=464.307..490.628 rows=50,000 loops=1)

2.          

CTE users_dupes

3. 5.824 152.854 ↑ 1.5 65,267 1

Append (cost=2,738.67..13,068.06 rows=97,076 width=25) (actual time=38.108..152.854 rows=65,267 loops=1)

4. 19.146 75.682 ↑ 1.0 48,026 1

Hash Join (cost=2,738.67..5,330.04 rows=48,517 width=25) (actual time=38.108..75.682 rows=48,026 loops=1)

  • Hash Cond: ((d.username)::text = (u.username)::text)
5. 18.832 18.832 ↑ 1.0 50,000 1

Index Scan using raw_data_pkey on raw_data d (cost=0.43..2,457.66 rows=51,095 width=17) (actual time=0.074..18.832 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
6. 20.324 37.704 ↓ 1.0 43,836 1

Hash (cost=2,190.33..2,190.33 rows=43,833 width=32) (actual time=37.704..37.704 rows=43,836 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3284kB
7. 17.380 17.380 ↓ 1.0 43,841 1

Seq Scan on users u (cost=0.00..2,190.33 rows=43,833 width=32) (actual time=0.005..17.380 rows=43,841 loops=1)

8. 14.470 71.348 ↑ 2.8 17,241 1

Hash Join (cost=2,738.67..6,767.26 rows=48,559 width=25) (actual time=38.044..71.348 rows=17,241 loops=1)

  • Hash Cond: ((d_1.username)::text = (u_1.email)::text)
9. 19.149 19.149 ↑ 1.0 50,000 1

Index Scan using raw_data_pkey on raw_data d_1 (cost=0.43..2,457.66 rows=51,095 width=17) (actual time=0.039..19.149 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
10. 16.652 37.729 ↑ 1.0 42,083 1

Hash (cost=2,190.33..2,190.33 rows=43,833 width=34) (actual time=37.729..37.729 rows=42,083 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3246kB
11. 21.077 21.077 ↓ 1.0 43,841 1

Seq Scan on users u_1 (cost=0.00..2,190.33 rows=43,833 width=34) (actual time=0.007..21.077 rows=43,841 loops=1)

12.          

CTE users

13. 28.573 212.149 ↑ 10.9 892 1

HashAggregate (cost=2,669.59..2,766.67 rows=9,708 width=138) (actual time=211.900..212.149 rows=892 loops=1)

  • Group Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
14. 183.576 183.576 ↑ 1.5 65,267 1

CTE Scan on users_dupes (cost=0.00..1,941.52 rows=97,076 width=138) (actual time=38.110..183.576 rows=65,267 loops=1)

15.          

CTE normalised_data

16. 8.294 479.134 ↑ 3.7 50,000 1

Merge Right Join (cost=7,290.59..10,146.02 rows=187,126 width=5) (actual time=464.304..479.134 rows=50,000 loops=1)

  • Merge Cond: ((u_2.username)::text = (data.username)::text)
17. 1.963 214.462 ↑ 10.9 892 1

Sort (cost=837.07..861.34 rows=9,708 width=130) (actual time=214.321..214.462 rows=892 loops=1)

  • Sort Key: u_2.username
  • Sort Method: quicksort Memory: 89kB
18. 212.499 212.499 ↑ 10.9 892 1

CTE Scan on users u_2 (cost=0.00..194.16 rows=9,708 width=130) (actual time=211.901..212.499 rows=892 loops=1)

19. 230.857 256.378 ↑ 1.0 50,000 1

Sort (cost=6,453.52..6,581.25 rows=51,095 width=22) (actual time=249.977..256.378 rows=50,000 loops=1)

  • Sort Key: data.username
  • Sort Method: external sort Disk: 1640kB
20. 25.521 25.521 ↑ 1.0 50,000 1

Index Scan using raw_data_pkey on raw_data data (cost=0.43..2,457.66 rows=51,095 width=22) (actual time=0.049..25.521 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
Planning time : 1.496 ms
Execution time : 495.839 ms