explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3XUa

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=7,957,177.17..7,957,196.96 rows=1,583 width=72) (actual rows= loops=)

2.          

CTE jira_users

3. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..0.62 rows=50 width=64) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=7,957,176.54..7,957,180.50 rows=1,583 width=72) (actual rows= loops=)

  • Sort Key: (CASE WHEN ((jira_users.email = (users.email)::text) OR (jira_users.email = (emails.email)::text)) THEN 3 WHEN (jira_users.name = lower((users.username)::text)) THEN 2 WHEN (jira_users.name = lower((users.name)::text)) THEN 1 ELSE NULL::integer END) DESC, users.id, jira_users.name, jira_users.email
5. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1.93..7,957,092.42 rows=1,583 width=72) (actual rows= loops=)

  • Merge Cond: (users.id = emails.user_id)
  • Filter: ((jira_users.name = lower((users.username)::text)) OR (jira_users.name = lower((users.name)::text)) OR (jira_users.email = (users.email)::text) OR (jira_users.email = (emails.email)::text))
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..6,823,027.22 rows=304,871,600 width=116) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Scan using users_pkey on users (cost=0.43..725,594.72 rows=6,097,432 width=52) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

CTE Scan on jira_users (cost=0.00..1.00 rows=50 width=64) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..13,703.88 rows=239,171 width=27) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using index_emails_on_user_id on emails (cost=0.42..13,105.96 rows=239,171 width=27) (actual rows= loops=)