CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Developer
Developer

Reputation: 661

ActiveRecord count analysis in rails query

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?

Upvotes: 0

Views: 804

Answers (1)

Esse
Esse

Reputation: 3298

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.

Upvotes: 1

Related Questions