CODEWITHSUNDEEP
sqlpostgresql
Tarlen
Tarlen

Reputation: 3797

OR-statement slows down query substantially

I have this query that behaves very different if include OR in the where clause like so

SELECT t.id
FROM teams AS t
LEFT OUTER JOIN teams_users AS tu ON tu.team_id = t.id
LEFT OUTER JOIN user AS u ON tu.user_id = u.id
WHERE 
    u.id = '8601680c9cd549c0a786288e1775fb63' OR t.id = '8601680c-9cd5-49c0-a786-288e1775fb63'

The above will run in ~500ms. If I remove the OR and only filter by the user id like so:

WHERE u.id = '8601680c9cd549c0a786288e1775fb63'

It runs in 20ms. If I only filter by the team id like so:

WHERE t.id = '8601680c-9cd5-49c0-a786-288e1775fb63'

It also runs in 20ms. But for some reason, if I filter by both, it becomes much slower

Any ideas as to why?

Upvotes: 5

Views: 2164

Answers (1)

MatBailie
MatBailie

Reputation: 86706

In many cases it's because the explain plan can't easily handle handle the two conditions.

  • The optimal plan for one condition would be significantly different from the plan for the other condition.

To know that or sure you need to look at all three explain plans, and if you want us to figure that out, you need to provide them in your question.

  1. You main query with the OR
  2. Filtering only on t.id
  3. Filtering only on u.id

The example of using IN() avoids typing the keyword OR, but still yields the same logic and is normally prone to the same planning problems.

If that is the case, one option is to use UNION. the allows your results to consist of the results from two separate queries, which the DBMS can plan separately...

SELECT t.id
FROM teams AS t
WHERE t.id = '8601680c-9cd5-49c0-a786-288e1775fb63'

UNION

SELECT t.id
FROM teams AS t
INNER JOIN teams_users AS tu ON tu.team_id = t.id
INNER JOIN user AS u ON tu.user_id = u.id
WHERE u.id = '8601680c9cd549c0a786288e1775fb63'

This has also allowed the LEFT OUTER JOINs to become INNER JOINs. That's an example of where the explain plan would suffer in your first example; needing LEFT JOIN only because you wanted to solve both conditions, but allowing them to be solved separately allowed INNER JOIN.

You could also remove the join on to teams in the second query, and select from tu.team_id AS id instead...

EDIT:

SELECT id
  FROM teams
 WHERE id = '8601680c-9cd5-49c0-a786-288e1775fb63'

UNION

SELECT team_id
  FROM teams_users
 WHERE user_id = '8601680c9cd549c0a786288e1775fb63'

I've also noticed that the column you're filtering the teams table by is also the column that you're selecting from it. So, do you really need to refer to the teams table at all (SELECT '8601680c-9cd5-49c0-a786-288e1775fb63' AS id UNION ...)? (Or are you just checking that such a team exists?)

Upvotes: 5

Related Questions