explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lg0

Settings
# exclusive inclusive rows x rows loops node
1. 140.298 140.298 ↑ 42.4 118 1

CTE Scan on users (cost=2,952.34..3,052.34 rows=5,000 width=138) (actual time=140.228..140.298 rows=118 loops=1)

2.          

CTE users_dupes

3. 3.139 106.020 ↑ 1.0 50,000 1

Limit (cost=0.84..1,527.34 rows=50,000 width=25) (actual time=0.111..106.020 rows=50,000 loops=1)

4. 3.629 102.881 ↑ 5,678.3 50,000 1

Append (cost=0.84..8,667,895.16 rows=283,915,242 width=25) (actual time=0.111..102.881 rows=50,000 loops=1)

5. 1.903 49.566 ↑ 1.0 25,000 1

Limit (cost=0.84..3,297.55 rows=25,000 width=25) (actual time=0.111..49.566 rows=25,000 loops=1)

6. 19.307 47.663 ↑ 51.8 25,000 1

Merge Join (cost=0.84..170,845.25 rows=1,295,568 width=25) (actual time=0.110..47.663 rows=25,000 loops=1)

  • Merge Cond: (lower((u.username)::text) = lower((d.username)::text))
7. 1.543 1.543 ↑ 33.3 1,318 1

Index Scan using idx_users_username on users u (cost=0.41..9,201.72 rows=43,825 width=32) (actual time=0.024..1.543 rows=1,318 loops=1)

8. 26.813 26.813 ↑ 51.8 25,000 1

Index Scan using idx_raw_data_username on temp_data d (cost=0.43..138,641.87 rows=1,295,563 width=17) (actual time=0.018..26.813 rows=25,000 loops=1)

9. 20.516 49.686 ↑ 11,355.6 25,000 1

Merge Join (cost=0.84..5,825,445.19 rows=283,890,242 width=25) (actual time=0.108..49.686 rows=25,000 loops=1)

  • Merge Cond: (lower((u_1.email)::text) = lower((d_1.username)::text))
10. 1.464 1.464 ↑ 30.0 1,460 1

Index Scan using idx_users_email on users u_1 (cost=0.41..8,669.79 rows=43,825 width=34) (actual time=0.033..1.464 rows=1,460 loops=1)

11. 4.407 27.706 ↑ 49.2 26,351 1

Materialize (cost=0.43..141,880.78 rows=1,295,563 width=17) (actual time=0.024..27.706 rows=26,351 loops=1)

12. 23.299 23.299 ↑ 49.2 26,351 1

Index Scan using idx_raw_data_username on temp_data d_1 (cost=0.43..138,641.87 rows=1,295,563 width=17) (actual time=0.020..23.299 rows=26,351 loops=1)

13.          

CTE users

14. 14.854 140.270 ↑ 42.4 118 1

HashAggregate (cost=1,375.00..1,425.00 rows=5,000 width=138) (actual time=140.227..140.270 rows=118 loops=1)

  • Group Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
15. 125.416 125.416 ↑ 1.0 50,000 1

CTE Scan on users_dupes (cost=0.00..1,000.00 rows=50,000 width=138) (actual time=0.112..125.416 rows=50,000 loops=1)

Planning time : 0.366 ms
Execution time : 141.194 ms