explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bcGW

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

CTE Scan on normalised_data (cost=36,272.67..40,015.19 rows=187,126 width=40) (actual time=270.707..405.732 rows=50,000 loops=1)

2.          

CTE users_dupes

3. 4.240 92.713 ↑ 1.5 65,267 1

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

4. 12.834 45.721 ↑ 1.0 48,026 1

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

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

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

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

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

8. 9.306 42.752 ↑ 2.8 17,241 1

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

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

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

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

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

12.          

CTE users

13. 19.261 133.631 ↑ 10.9 892 1

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

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

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

15.          

CTE normalised_data

16. 116.559 393.365 ↑ 3.7 50,000 1

Merge Right Join (cost=7,290.59..20,437.95 rows=187,126 width=13) (actual time=270.706..393.365 rows=50,000 loops=1)

  • Merge Cond: ((u_2.username)::text = (d_2.username)::text)
17. 1.723 135.587 ↑ 10.9 892 1

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

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

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

19. 127.429 141.219 ↑ 1.0 50,000 1

Sort (cost=6,453.52..6,581.25 rows=51,095 width=30) (actual time=135.175..141.219 rows=50,000 loops=1)

  • Sort Key: d_2.username
  • Sort Method: external sort Disk: 2168kB
20. 13.790 13.790 ↑ 1.0 50,000 1

Index Scan using raw_data_pkey on raw_data d_2 (cost=0.43..2,457.66 rows=51,095 width=30) (actual time=0.041..13.790 rows=50,000 loops=1)

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