explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aXGv

Settings
# exclusive inclusive rows x rows loops node
1. 121.075 121.075 ↑ 1.9 25,000 1

CTE Scan on normalised_data (cost=14,886.21..15,821.49 rows=46,764 width=32) (actual time=110.445..121.075 rows=25,000 loops=1)

2.          

CTE users_dupes

3. 2.036 71.077 ↑ 1.6 31,280 1

Append (cost=2,738.67..9,280.39 rows=48,529 width=25) (actual time=22.182..71.077 rows=31,280 loops=1)

4. 6.622 35.110 ↓ 1.0 24,488 1

Hash Join (cost=2,738.67..4,038.31 rows=24,254 width=25) (actual time=22.182..35.110 rows=24,488 loops=1)

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

Index Scan using raw_data_pkey on raw_data d (cost=0.43..1,233.01 rows=25,543 width=17) (actual time=0.046..6.640 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
6. 11.523 21.848 ↓ 1.0 43,836 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3284kB
7. 10.325 10.325 ↓ 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..10.325 rows=43,841 loops=1)

8. 4.628 33.931 ↑ 3.6 6,792 1

Hash Join (cost=2,738.67..4,756.79 rows=24,275 width=25) (actual time=22.854..33.931 rows=6,792 loops=1)

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

Index Scan using raw_data_pkey on raw_data d_1 (cost=0.43..1,233.01 rows=25,543 width=17) (actual time=0.074..6.805 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
10. 10.198 22.498 ↑ 1.0 42,083 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3246kB
11. 12.300 12.300 ↓ 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.008..12.300 rows=43,841 loops=1)

12.          

CTE users

13. 9.243 88.759 ↑ 11.6 417 1

HashAggregate (cost=1,334.55..1,383.08 rows=4,853 width=138) (actual time=88.672..88.759 rows=417 loops=1)

  • Group Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
14. 79.516 79.516 ↑ 1.6 31,280 1

CTE Scan on users_dupes (cost=0.00..970.58 rows=48,529 width=138) (actual time=22.184..79.516 rows=31,280 loops=1)

15.          

CTE normalised_data

16. 3.923 115.744 ↑ 1.9 25,000 1

Merge Right Join (cost=3,497.01..4,222.74 rows=46,764 width=5) (actual time=110.442..115.744 rows=25,000 loops=1)

  • Merge Cond: ((u_2.username)::text = (data.username)::text)
17. 0.686 89.576 ↑ 11.6 417 1

Sort (cost=394.18..406.31 rows=4,853 width=130) (actual time=89.528..89.576 rows=417 loops=1)

  • Sort Key: u_2.username
  • Sort Method: quicksort Memory: 55kB
18. 88.890 88.890 ↑ 11.6 417 1

CTE Scan on users u_2 (cost=0.00..97.06 rows=4,853 width=130) (actual time=88.674..88.890 rows=417 loops=1)

19. 15.355 22.245 ↑ 1.0 25,000 1

Sort (cost=3,102.84..3,166.70 rows=25,543 width=22) (actual time=20.908..22.245 rows=25,000 loops=1)

  • Sort Key: data.username
  • Sort Method: quicksort Memory: 2179kB
20. 6.890 6.890 ↑ 1.0 25,000 1

Index Scan using raw_data_pkey on raw_data data (cost=0.43..1,233.01 rows=25,543 width=22) (actual time=0.045..6.890 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
Planning time : 1.068 ms
Execution time : 123.995 ms