CODEWITHSUNDEEP
ruby-on-railspostgresqloptimizationrails-activerecord
Steve
Steve

Reputation: 4566

Does Rails automagically optimize queries

After running two similar queries like

@articles = @magazine.articles.limit(2).offset(0)
@articles = @articles.limit(2).offset(2)

I was expecting to see two SQL statements in my console being executed by the server. However, the first query is missing and only the second one is being run. Similarly, after executing the following two queries:

@articles = @magazine.articles.limit(2).offset(0)
@articles = @articles.limit(2).offset(@articles.size - 2)

the first query is completely ignored as well. These two queries generate the SQL:

SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "articles"  
WHERE "articles"."magazine_id" = $1 LIMIT 2 OFFSET 0)
subquery_for_count  [["magazine_id", 1]]

SELECT  "articles".* FROM "articles"  
WHERE "articles"."magazine_id" = $1 
LIMIT 2 OFFSET 2  [["magazine_id", 1]]

Interestingly enough, if I change @articles.size to @articles.length both queries are run as expected. I would think since length requires the collection in memory, the first statement is forced to run. Can anyone describe what's happening here and if it's too broad a topic, point me to a good resource.

Upvotes: 1

Views: 95

Answers (1)

Shadwell
Shadwell

Reputation: 34774

It's not so much optimising as deferring execution of the query until it really needs to execute it.

In both cases you're storing the result of building up a query in @articles. Active Record, or more accurately arel, defers execution of the query until you call a method that needs the results. I suspect that you're actually seeing the query being executed against the database when you call something like @artircles.each or @articles.count or somesuch.

You could build the query up in a series of steps and it won't actually get executed:

a = @magazine.articles
a = a.limit(2)
a = a.offset(0)

It also means you can leave some query clause that drastically reduces the result size to the end of the process:

a = a.where('created_at > ?', Time.now.at_beginning_of_day)

Still no query has been sent to the database.

The thing to watch out for is testing this logic in the rails console. If you run these steps in the console itself it tries to display the last return value (by calling .inspect I think) and by inspecting the return value it causes the query to be executed. So if you put a = Magazine.find(1).articles into the console you'll see a query immediately exeecuted which wouldn't have been if the code was run in the context of a controller action for example. If you then call a.limit(2) you'll see another query and so on.

Upvotes: 2

Related Questions