ActiveRecord OR operator slows down query by factor of 10. Why?

Question

I have an ActiveRecord query that uses the OR operator to chain together 2 queries. The results come back fine, but the speed of executing the combined query is ~10 times as slow as executing either of the 2 queries in on their own.

We have an Event model and an Invitation model. A User can be invited to an Event by being targeted through an invitation filter, or by being individually invited by having an Invitation record.

So when determining how many users are invited to a particular event, we have to look at all those with Invitations and all those matching the filter. We do that here:

@invited_count = @invited_by_individual.or(@invited_by_filter).distinct.count(:id)

It's important to note, both @invited_by_individual and @invited_by_filter relations have references and includes statements within them.

Now, the problem is that when we execute that query, it takes about 1200ms. If we were to do the queries individually, each of them only take about 80ms. So @invited_by_filter.distinct.count and @invited_by_individual.distinct.count both return results in about 80ms, but neither of these is complete on its own.

Is there any way to speed up the query with the OR operator? Why is this happening in the first place?

Here is the SQL generated by the ActiveRecord queries:

Fast, single query:

(79.7ms)  
SELECT COUNT(DISTINCT "users"."id") 
FROM "users" 
LEFT OUTER JOIN "invitations" 
ON "invitations"."user_id" = "users"."id" 
WHERE "invitations"."event_id" = $1  [["event_id", 732]]

Slow, with combined query:

(1220.7ms)  
SELECT COUNT(DISTINCT "users"."id") 
FROM "users" 
LEFT OUTER JOIN "invitations" 
ON "invitations"."user_id" = "users"."id" 
WHERE ("invitations"."event_id" = $1 OR "users"."organization_id" = $2)  [["event_id", 732], ["organization_id", 13]]

Update, here's the EXPLAIN:

(1418.2ms)  SELECT COUNT(DISTINCT "users"."id") FROM "users" LEFT OUTER JOIN "invitations" ON "invitations"."user_id" = "users"."id" WHERE ("users"."root_organization_id" = $1 OR "invitations"."event_id" = $2)  [["root_organization_id", -1], ["event_id", 749]]
 => 
EXPLAIN for: SELECT COUNT(DISTINCT "users"."id") FROM "users" LEFT OUTER JOIN "invitations" ON "invitations"."user_id" = "users"."id" WHERE ("users"."root_organization_id" = $1 OR "invitations"."event_id" = $2) [["root_organization_id", -1], ["event_id", 749]]

 #=> QUERY PLAN
                                                     
 Aggregate  (cost=121781.56..121781.57 rows=1 width=8)
   ->  Hash Right Join  (cost=113248.88..121778.64 rows=1165 width=8)
         Hash Cond: (invitations.user_id = users.id)
         Filter: ((users.root_organization_id = '-1'::integer) OR (invitations.event_id = 749))
         ->  Seq Scan on invitations  (cost=0.00..1299.70 rows=63470 width=8)
         ->  Hash  (cost=93513.28..93513.28 rows=1135328 width=12)
               ->  Seq Scan on users  (cost=0.00..93513.28 rows=1135328 width=12)
(7 rows)

Update 2, EXPLAIN for queries ran individually, does use the indices:

(91.5ms)  SELECT COUNT(*) FROM "users" INNER JOIN "invitations" ON "invitations"."user_id" = "users"."id" WHERE "users"."root_organization_id" = $1  [["root_organization_id", -1]]
 => 
EXPLAIN for: SELECT COUNT(*) FROM "users" INNER JOIN "invitations" ON "invitations"."user_id" = "users"."id" WHERE "users"."root_organization_id" = $1 [["root_organization_id", -1]]

 #=> QUERY PLAN

 Aggregate  (cost=19.05..19.06 rows=1 width=8)
   ->  Nested Loop  (cost=0.72..19.05 rows=1 width=0)
         ->  Index Scan using index_users_on_root_organization_id on users  (cost=0.43..4.45 rows=1 width=8)
               Index Cond: (root_organization_id = '-1'::integer)
         ->  Index Only Scan using index_invitations_on_user_id on invitations  (cost=0.29..14.57 rows=3 width=4)
               Index Cond: (user_id = users.id)
(6 rows)

and

EXPLAIN for: SELECT COUNT(DISTINCT "users"."id") FROM "users" LEFT OUTER JOIN "invitations" ON "invitations"."user_id" = "users"."id" WHERE "invitations"."event_id" = $1 [["event_id", 749]]

 #=> QUERY PLAN

 Aggregate  (cost=536.34..536.35 rows=1 width=8)
   ->  Nested Loop  (cost=0.72..536.19 rows=62 width=8)
         ->  Index Scan using index_invitations_on_event_id on invitations  (cost=0.29..11.98 rows=62 width=4)
               Index Cond: (event_id = 749)
         ->  Index Only Scan using users_pkey on users  (cost=0.43..8.45 rows=1 width=8)
               Index Cond: (id = invitations.user_id)
(6 rows)

user_12219679 · Answer

UNION enables you to leverage both indexes while still preventing duplicates.

User.from(
"(#{@invited_by_individual.to_sql} 
UNION 
#{@invited_by_filter.to_sql})"
).count

user_4116017 · Answer

This is your query that uses OR:

SELECT COUNT(DISTINCT "users"."id") 
FROM "users" 
LEFT OUTER JOIN "invitations" 
ON "invitations"."user_id" = "users"."id" 
WHERE ("invitations"."event_id" = $1 OR "users"."organization_id" = $2)

If you try the following query in Postgres I expect it to produce the same result, but work faster:

SELECT
    COUNT(DISTINCT id) AS cc
FROM
    (
        SELECT
            "invitations"."user_id" AS id
        FROM
            "invitations"
        WHERE
            ("invitations"."event_id" = $1)

        UNION ALL

        SELECT
            "users"."id"
        FROM
            "users" 
        WHERE
            ("users"."organization_id" = $2)
    ) AS T
;

If you have indexes on "invitations"."event_id" and on "users"."organization_id", engine should use them. If you don't have such indexes, create them.

The query with OR is slow, because the optimizer is not smart enough to perform this translation and split original query into two parts. When you run each part separately the engine sees that it can use an appropriate index. When the query joins two tables and have an OR condition in the WHERE filter no single index can return the rows needed, so the engine doesn't attempt to use any index. It reads all 1135328 rows from the users table and it reads all 63470 rows from the invitations table. Naturally, it is slow.

I have no idea how to translate this query to the ActiveRecord syntax.

user_251920 · Answer

The main problem seems to be sequential scan on users where apparently you have ~1m rows. Since it works on the single query it seems that dbms is estimating that due to join it is more efficient to do those by sequential scan.

What you may want to try:

I. Ensure that database is vacuumed if you can

II. Try to use count from two subselects or UNION

SELECT count(id) FROM (
  SELECT users.id FROM users WHERE "users"."root_organization_id" = -1 
  UNION
   SELECT invitations.user_id AS id FROM invitations WHERE invitations.event_id = 749
) AS x

user_1367454 · Answer

using Or to filter out is usually causing bad performance and the better option is to use union however union will cause to hit all tables twice.

however when you have to count(distinct) , usually it's a sign that data is inflated because of joins and it's not preferably.

so you can rewrite your query to this , so it will benefit two thing , data will not be inflated (duplicated) because there would be no need to join,which will help the performance and database engine can still use indices :

FROM "users" u
where u.id in (select user_id from "invitations" i where i."event_id" = $1)
or u."organization_id" = $2

make sure there are proper indexes on organization_id in user table and event_id in invitation table

but also if you separate the condition with union , you will get even much better performance:

SELECT COUNT(*)
from (
select id 
FROM "users" u
where u.id in (select user_id from "invitations" i where i."event_id" = $1)
union 
select id
FROM "users" u
where u."organization_id" = $2
) t

user_5787849 · Answer

This problem is basically can be solved with a composite index in postgres

The first thing which I have noticed you have used here "users"."root_organization_id". Whereas in the slow query you are using "users"."organization_id"

(91.5ms)  SELECT COUNT(*) FROM "users" INNER JOIN "invitations" ON "invitations"."user_id" = "users"."id" WHERE "users"."root_organization_id" = $1  [["root_organization_id", -1]]

Secondly, you should have a composite index on all these columns

invitations.user_id
invitations.event_id
users.organization_id
users.root_organization_id
users.id

In rails, you can add a composite index with the help of this link

Once done then log in to the Postgres console and run this command \d table_name against both the table and share the result. Then run the explain query on the slow query result and share the result.

Update: You should have indexes on all 5 columns. Also, recreate your indexes

user_702369 · Answer

It's a long shot, but you can try to modify the statistics of the organization_id column, and then analyze the table.

ALTER TABLE users ALTER COLUMN organization_id SET STATISTICS 1000;
ANALYZE users;

ActiveRecord OR operator slows down query by factor of 10. Why?

6 Answers