explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TZIu

Settings
# exclusive inclusive rows x rows loops node
1. 367.498 367.498 ↑ 808.7 106 1

CTE Scan on users (cost=268,147.50..269,861.88 rows=85,719 width=138) (actual time=363.820..367.498 rows=106 loops=1)

2.          

CTE users_dupes

3. 1.056 349.420 ↑ 32.3 26,533 1

Append (cost=353.11..34,898.51 rows=857,193 width=25) (actual time=2.983..349.420 rows=26,533 loops=1)

4. 48.809 60.706 ↑ 1.8 26,512 1

Hash Join (cost=353.11..3,484.86 rows=48,718 width=25) (actual time=2.982..60.706 rows=26,512 loops=1)

  • Hash Cond: (lower((d.username)::text) = lower((u.username)::text))
5. 8.994 8.994 ↓ 1.0 49,990 1

Index Scan using raw_data_pkey on raw_data d (cost=0.43..2,340.51 rows=48,718 width=17) (actual time=0.015..8.994 rows=49,990 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
6. 2.041 2.903 ↑ 1.0 3,314 1

Hash (cost=311.19..311.19 rows=3,319 width=18) (actual time=2.903..2.903 rows=3,314 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 197kB
7. 0.862 0.862 ↑ 1.0 3,319 1

Seq Scan on users u (cost=0.00..311.19 rows=3,319 width=18) (actual time=0.016..0.862 rows=3,319 loops=1)

8. 14.139 287.658 ↑ 38,498.8 21 1

Merge Join (cost=6,639.03..22,841.72 rows=808,475 width=25) (actual time=269.969..287.658 rows=21 loops=1)

  • Merge Cond: ((lower((u_1.email)::text)) = (lower((d_1.username)::text)))
9. 4.014 6.056 ↑ 2.4 1,355 1

Sort (cost=505.29..513.59 rows=3,319 width=37) (actual time=5.878..6.056 rows=1,355 loops=1)

  • Sort Key: (lower((u_1.email)::text))
  • Sort Method: quicksort Memory: 378kB
10. 2.042 2.042 ↑ 1.0 3,319 1

Seq Scan on users u_1 (cost=0.00..311.19 rows=3,319 width=37) (actual time=0.029..2.042 rows=3,319 loops=1)

11. 229.831 267.463 ↑ 1.0 48,016 1

Sort (cost=6,133.74..6,255.53 rows=48,718 width=17) (actual time=261.228..267.463 rows=48,016 loops=1)

  • Sort Key: (lower((d_1.username)::text))
  • Sort Method: external sort Disk: 2088kB
12. 37.632 37.632 ↓ 1.0 49,990 1

Index Scan using raw_data_pkey on raw_data d_1 (cost=0.43..2,340.51 rows=48,718 width=17) (actual time=0.021..37.632 rows=49,990 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
13.          

CTE users

14. 2.777 367.480 ↑ 808.7 106 1

Unique (cost=224,677.06..233,248.99 rows=85,719 width=138) (actual time=363.817..367.480 rows=106 loops=1)

15. 9.307 364.703 ↑ 32.3 26,533 1

Sort (cost=224,677.06..226,820.04 rows=857,193 width=138) (actual time=363.815..364.703 rows=26,533 loops=1)

  • Sort Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
  • Sort Method: quicksort Memory: 2048kB
16. 355.396 355.396 ↑ 32.3 26,533 1

CTE Scan on users_dupes (cost=0.00..17,143.86 rows=857,193 width=138) (actual time=2.985..355.396 rows=26,533 loops=1)

Planning time : 0.472 ms
Execution time : 370.394 ms