explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bUG1

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

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

2.          

CTE users_dupes

3. 4.263 95.050 ↑ 1.5 65,267 1

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

4. 12.839 46.199 ↑ 1.0 48,026 1

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

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

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

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

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

8. 9.388 44.588 ↑ 2.8 17,241 1

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

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

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

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

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

12.          

CTE users

13. 19.677 136.663 ↑ 10.9 892 1

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

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

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

15.          

CTE normalised_data

16. 7.363 308.776 ↑ 3.7 50,000 1

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

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

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

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

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

19. 147.022 162.777 ↑ 1.0 50,000 1

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

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

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