explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T92p

Settings
# exclusive inclusive rows x rows loops node
1. 826.997 826.997 ↑ 1,260.9 892 1

CTE Scan on users (cost=4,431,441.30..4,453,935.88 rows=1,124,729 width=138) (actual time=813.687..826.997 rows=892 loops=1)

2.          

CTE users_dupes

3. 4.647 589.050 ↑ 168.3 66,818 1

Append (cost=2,738.49..354,883.53 rows=11,247,287 width=25) (actual time=67.742..589.050 rows=66,818 loops=1)

4. 64.749 145.588 ↑ 1.1 48,026 1

Hash Join (cost=2,738.49..6,026.01 rows=51,095 width=25) (actual time=67.741..145.588 rows=48,026 loops=1)

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

  • Index Cond: ((id >= 1) AND (id <= 50000))
6. 52.118 67.357 ↓ 1.0 43,827 1

Hash (cost=2,190.25..2,190.25 rows=43,825 width=32) (actual time=67.357..67.357 rows=43,827 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3284kB
7. 15.239 15.239 ↓ 1.0 43,832 1

Seq Scan on users u (cost=0.00..2,190.25 rows=43,825 width=32) (actual time=0.007..15.239 rows=43,832 loops=1)

8. 19.461 438.815 ↑ 595.8 18,792 1

Merge Join (cost=12,022.56..236,384.65 rows=11,196,192 width=25) (actual time=401.155..438.815 rows=18,792 loops=1)

  • Merge Cond: ((lower((u_1.email)::text)) = (lower((d_1.username)::text)))
9. 218.586 255.067 ↑ 1.0 42,066 1

Sort (cost=5,569.04..5,678.60 rows=43,825 width=34) (actual time=242.331..255.067 rows=42,066 loops=1)

  • Sort Key: (lower((u_1.email)::text))
  • Sort Method: external merge Disk: 2968kB
10. 36.481 36.481 ↓ 1.0 43,832 1

Seq Scan on users u_1 (cost=0.00..2,190.25 rows=43,825 width=34) (actual time=0.011..36.481 rows=43,832 loops=1)

11. 129.165 164.287 ↑ 1.1 48,027 1

Sort (cost=6,453.52..6,581.25 rows=51,095 width=17) (actual time=158.798..164.287 rows=48,027 loops=1)

  • Sort Key: (lower((d_1.username)::text))
  • Sort Method: external sort Disk: 2088kB
12. 35.122 35.122 ↑ 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.052..35.122 rows=50,000 loops=1)

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

CTE users

14. 6.872 826.862 ↑ 1,260.9 892 1

Unique (cost=3,964,084.90..4,076,557.77 rows=1,124,729 width=138) (actual time=813.686..826.862 rows=892 loops=1)

15. 209.007 819.990 ↑ 168.3 66,818 1

Sort (cost=3,964,084.90..3,992,203.12 rows=11,247,287 width=138) (actual time=813.685..819.990 rows=66,818 loops=1)

  • Sort Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
  • Sort Method: external merge Disk: 2496kB
16. 610.983 610.983 ↑ 168.3 66,818 1

CTE Scan on users_dupes (cost=0.00..224,945.74 rows=11,247,287 width=138) (actual time=67.743..610.983 rows=66,818 loops=1)

Planning time : 0.669 ms
Execution time : 829.907 ms