explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wzFF

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

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

2.          

CTE users_dupes

3. 4.199 95.906 ↑ 1.5 65,267 1

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

4. 13.078 46.674 ↑ 1.0 48,026 1

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

  • Hash Cond: ((d.username)::text = (u.username)::text)
5. 13.280 13.280 ↑ 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.033..13.280 rows=50,000 loops=1)

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

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

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

8. 9.616 45.033 ↑ 2.8 17,241 1

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

  • Hash Cond: ((d_1.username)::text = (u_1.email)::text)
9. 13.022 13.022 ↑ 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.040..13.022 rows=50,000 loops=1)

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

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

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

12.          

CTE users

13. 19.393 136.992 ↑ 10.9 892 1

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

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

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

15.          

CTE normalised_data

16. 7.273 298.220 ↑ 3.7 50,000 1

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

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

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

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

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

19. 137.127 151.987 ↑ 1.0 50,000 1

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

  • Sort Key: data.username
  • Sort Method: external sort Disk: 1640kB
20. 14.860 14.860 ↑ 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.045..14.860 rows=50,000 loops=1)

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