ActiveRecord count analysis in rails query

Question

I am hardly checking to find the execution speed of two queries, explain analyze and benchmark because i got timeout for one query but i am not sure this query was causing this.

queue_count = purchase.purchase_items.where("queue_id = ?", queue.id).count

same sql query

SELECT COUNT(*) FROM "purchase_items" WHERE "purchase_items"."purchase_id" = 1241422 AND (queue_id = 3479783)

so i have to remove the count then i got one solution to take all record in array and do the count then i got the query like this

queue_count = purchase.purchase_items.where("queue_id = ?", queue.id).all.count

same sql query

SELECT "purchase_items".* FROM "purchase_items" WHERE "purchase_items"."purchase_id" = 1241422 AND (queue_id = 3479783)

finally got some slight variation when i was checking with query analyze and also benchmark, so this was the correct way? or am i doing anything wrong?

Esse · Accepted Answer · 2017-02-10 11:47:07Z

1

In terms of performance second query will be quite terrible. It will load all records in memory and count them using Ruby. Database is designed to do stuff like this quickly.

In order to analyze query you can do EXPLAIN ANALYZE in Psql console. My long shot is that you're missing some indexes (on purchase_id and queue_id). You can look into this by running:

EXPLAIN ANALYZE SELECT COUNT(*) FROM purchase_items WHERE purchase_id = 1241422 AND (queue_id = 3479783)

If you see that PostgreSQL is scanning whole table, then performance will not be optimal. Try adding indexes:

CREATE INDEX  purchase_id_purchase_items_idx ON purchase_items (purchase_id);
CREATE INDEX  queue_id_purchase_items_idx ON purchase_items (queue_id);

and examining performance using EXPLAIN ANALYZE then. But never load all records into Ruby to do simple .count on them.

answered Feb 10, 2017 at 11:47

Esse

3,3082 gold badges24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Developer Over a year ago

I already have that index, but one more thing when i do analyze EXPLAIN ANALYZE SELECT COUNT(*) FROM purchase_items WHERE purchase_id = 1241422 AND (queue_id = 3479783) this query in first hit this shows Execution time: 1.088 ms, second hit this shows 0.148 ms, is there any logic behind to check one more times or the first hit execution time is correct?

Esse Over a year ago

you need to run it few times and calculate f.e. average - one try isn't enough to get correct data. And if it's 1 ms or 0.1 ms then it's really fast and definitely not a bottleneck

Collectives™ on Stack Overflow

ActiveRecord count analysis in rails query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related