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)

What do your indexes look like? What is the EXPLAIN output for that query? — mu is too short, May 23 '21 at 02:30
I just updated the original post with the EXPLAIN of the slow query. Re: Indices, I've ensured that every column included in the query has an index. — D-Nice, May 23 '21 at 03:52
That's the EXPLAIN out for something else (probably a query using `includes`), not the `count(distinct ...)` queries you're asking about. — mu is too short, May 23 '21 at 04:23
Sorry about that, that was the EXPLAIN for the query without `count.` I've updated the post with the correct EXPLAIN. Just to clarify, the `@invited_by_individual` and `@ invited_by_filter` ActiveRecord relations both have an `includes` on the `invitations` table. — D-Nice, May 23 '21 at 20:24
I do have many indexes on `users` and `invitations`, including `invitations.user_id` and `invitations.event_id`. When I run EXPLAIN on the queries individually, I can see that it uses those indexes. But when I run the queries combined with `or` operator, it doesn't. Updated post with individual EXPLAIN output — D-Nice, May 24 '21 at 00:39
@D-Nice could you share what `@invited_by_individual` and `@invited_by_filter` are defined as (i.e. the ActiveRecord methods)? — Matt, May 25 '21 at 21:55
@Matt `@invited_by_filter` is constructed dynamically based on a customizable filter. `@invited_by_individual` is `User.where(:_organization_id => -1).includes(:invitations)` — D-Nice, May 26 '21 at 05:06

Lam Phan · Answer 1 · 2021-05-25T04:30:07.953

1

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

edited May 25 '21 at 04:30

answered May 25 '21 at 04:19

Lam Phan

3,405
2
9
20

Thank you, when I try running this query, I get `ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: each UNION query must have the same number of columns).` I suspect it's because one of the queries is selecting fields individually for some reason, and the other is selecting "users"."*". One query looks like `"users"."id" AS t0_r0, "users"."email" AS t0_r1, "users"."encrypted_password...`. I tried unscope(:select) to no avail, any idea how to get past this hurdle? – D-Nice May 26 '21 at 05:04
@D-Nice `User.from( "(#{@invited_by_individual.select(:id).to_sql} UNION #{@invited_by_filter.select(:id).to_sql})" ).count` – Lam Phan May 26 '21 at 05:18
That worked with the last error, but I'm now seeing `ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: subquery in FROM must have an alias)`, which I haven't been able to overcome yet :/ Any ideas? – D-Nice May 26 '21 at 07:12
1

@D-Nice try `User.from( "(#{@invited_by_individual.select(:id).to_sql} UNION #{@invited_by_filter.select(:id).to_sql}) AS invitations" ).count` – Lam Phan May 26 '21 at 07:25
It reverted back to `ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: each UNION query must have the same number of columns)` after adding that alias. I suspect it's because the `@invited_by_individual` relation is resolving to `SELECT "users"."id", "users"."id" AS t0_r0, "users"."email" AS t0_r1, "users"."encrypted_password" AS t0_r2,`... even though we're using `select(:id).` I've also tried adding `unscope(:select)` before `.select(:id)` without luck. Any idea why it would be stil individually selecting all the `User` columns like that? – D-Nice May 26 '21 at 18:34
@D-Nice could you re-check your `@invited_by_individual` has any `eager_load` ? because the output sql look like that, if so you can try `.except(:eager_load).select(:id)` – Lam Phan May 27 '21 at 03:48
There are `includes` in both `@invited_by_individual` and `@invited_by_filter` – D-Nice May 27 '21 at 03:52
@D-Nice any `includes()..references()` ? – Lam Phan May 27 '21 at 04:30
Yes, sorry I should have mentioned each `includes()` also has a `references()` – D-Nice May 27 '21 at 05:23
so try `except(:references)` or `unscope(:references)` first ... but i think you should consider other answers that suggest you should re-write the sql instead of reuse `@invited_by_individual` and `@invited_by_filter` since both of them have high potential to change in future and you (or other developers) maybe forget to update this count optimize or in the worst case it's very hard to update ... – Lam Phan May 27 '21 at 05:27

Vladimir Baranov · Answer 2 · 2021-05-28T08:08:09.757

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.

score 0 · Answer 3 · answered May 25 '21 at 06:42

0

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

answered May 25 '21 at 06:42

dfens

5,413
4
35
50

I vacuumed the database, but didn't have an effect :/. I've tried this UNION query, but keep running into issues. I'm suspecting it's because my subqueries `@invited_by_individual` and `@invited_by_filter` both use `includes.` Is there a way to continue using `includes` while also taking the `UNION` approach? The combination of `includes` and `UNION` appears to always give me the error `ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: each UNION query must have the same number of columns)`. – D-Nice May 26 '21 at 18:53
@D-Nice this is somehow connected to usecase. First of all: Is this feature just to show counter ? Because if yes - you might want to reconsider how do you use it. Note that includes does not affect counts because it is simply separate query. So basically: 1) check if pure SQL query works faster then your query and don't focus on activerecord 2) Reconsider to pass value of this select directly to the view / attached virtually to model. I imagine that your controller may look like @ invitation = Invitation.find(...); @ counter = result_of_this_sql(@ invitation) – dfens May 27 '21 at 07:25
In other words - do not focus on getting this by activerecord, check if running this query directly solves your problem and later pass your variable separately to the view. Of course later you might want to try this with Arel – dfens May 27 '21 at 07:29

eshirvana · Answer 4 · 2021-05-26T19:42:12.250

0

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

edited May 26 '21 at 19:42

answered May 26 '21 at 02:01

eshirvana

23,227
3
22
38

I'm attempting to rewrite the query with ActiveRecord using your SQL. Just to clarify, this query is used both as `count(distinct)` and to retrieve the actual `User` records. Does that change the validity of the approach you've taken with that SQL query? – D-Nice May 26 '21 at 18:38
@D-Nice not following what you are trying to say but It's basically does count number of users with two conditions ( as in your original query) – eshirvana May 26 '21 at 18:42
@D-Nice on the second thought , if you use `in statement` that improve performance a little bit since you will ignore join , but still union gives you better performance out f all. – eshirvana May 26 '21 at 19:41
1

thank you, your 2nd query works well when on its own, but I'm struggling to incorporate it into my app because my subqueries each have `include` statements (which are generated dynamically based on user input) - and this complicates the use of `UNION.` I'm getting the error `ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: each UNION query must have the same number of columns)` regardless of what columns I explicitly `select`. – D-Nice May 26 '21 at 20:43
@D-Nice hmm... seems like you are selecting not balanced number of column in each select of union. make sure you are selecting only "Id" , I'm not expert in ActiveRecord but maybe add your code in your question , I might be able to pin point the issue – eshirvana May 26 '21 at 20:53
That much I understand, but it seems that when user input requires the query look at an additional table, the addition of `includes(:rsvps).references(:rsvps)` for example, to check the rsvps table - the dynamic query grows unwieldy to when preloading a table, generating something like `SELECT users.*, "users"."id" AS t0_r0, "users"."email" AS t0_r1...` which individually selects each table column. Which is where the `each UNION query must have the same number of columns` error appears to be coming from. – D-Nice May 26 '21 at 21:12
@D-Nice , I see , not sure why you have `select "users"."id" AS t0_r0, "users"."email" AS t0_r ...` in your query , because even if both union side of union , you just have `select * from ...` you won't get that error message ( however that impact your performance , since you don't need all the columns. – eshirvana May 26 '21 at 21:43

Aniket Tiwari · Answer 5 · 2021-05-27T10:36:26.777

0

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

edited May 27 '21 at 10:36

answered May 26 '21 at 14:07

Aniket Tiwari

3,561
4
21
61

Hi, when you say all those columns should have a composite index, I'm assuming you mean `invitations.event_id` and `invitations.event_id` together, and `users.organization_id` separately? I'm under the impression that you can't have a composite index including more than 1 table? – D-Nice May 26 '21 at 17:59
You need to add composite index on invitations.user_id & invitations.event_id. And normal index on users.organization_id – Aniket Tiwari May 26 '21 at 18:01
I added the composite index (users organization_id index already existed), but the query is still performing poorly (no effect) :/ – D-Nice May 26 '21 at 18:17
Can you show me the indexes in both the tables by running \d table_name – Aniket Tiwari May 27 '21 at 04:53

score 0 · Answer 6 · answered May 27 '21 at 22:13

0

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;

answered May 27 '21 at 22:13

Nadav

304
3
11

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

6 Answers6