CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecord
Alexander Zinchuk
Alexander Zinchuk

Reputation: 855

Slow queries with LIMIT (Active Record `first` method)

I observed that running Model.where(*condition with 3 integer indexes*).first takes too much time on big tables.

first adds sorting by id, so it probabably should take and sort all of 1.5 million records:

Game.where(private: 0, status: 0).first
DEBUG -- :   Game Load (__1278.6ms__)
SELECT  "games".* FROM "games" WHERE "games"."private" = 0 AND "games"."status" = 0
__ORDER BY "games"."id" ASC__ LIMIT 1

Removing first turns things much faster:

Game.where(private: 0, status: 0)
DEBUG -- :   Game Load (__68.0ms__)
SELECT "games".* FROM "games" WHERE "games"."private" = 0 AND "games"."status" = 0

However, if I manually remove sorting things are still going not so fast:

Game.where(private: 0, status: 0).order(nil).first
DEBUG -- :   Game Load (__323.7ms__)
SELECT  "games".* FROM "games" WHERE "games"."private" = 0 AND "games"."status" = 0 LIMIT 1

Does anyone know what's the cause of this? Now I consider using scope.to_a.first which seems to be much faster.

Explain plan for the first query is:

1. Limit (cost=0.43..59.68 rows=1 width=59)
2. -> Index Scan using games_pkey on games (cost=0.43..90007.49 rows=1519 width=59)
3. Filter: ?

UPD

It's strange but today I see other results for second query (now it's being executed almost instantly):

Game.where(private: 0, status: 0).order(nil).first
DEBUG -- :   Game Load (__2.5ms__)
SELECT  "games".* FROM "games"  WHERE "games"."private" = 0 AND "games"."status" = 0 LIMIT 1

Upvotes: 3

Views: 1911

Answers (1)

Simone Carletti
Simone Carletti

Reputation: 176402

Without posting any details of the query plan it is very hard to debug the issue. There may be several factor that are causing a temporary slow down of the query, factors not connected with the code itself rather with the way the database stores the data.

Just relying on the execution time of the query may not truly expose whether a query is efficient or not.

Generally speaking, the LIMIT requires more resource if there is a sort condition involved, as the database have to sort the data internally in order to extract the N records. Of course, if the attribute used in the sort clause is not indexed, then the query will be even more inefficient.

ActiveRecord exposes both first and take. If you run

Game.where(private: 0, status: 0).first

then ActiveRecord will sort the records by primary key (unless you specify a sorting column) whereas if you use

Game.where(private: 0, status: 0).take

ActiveRecord will query the database and just get the first one. Which solution is better, well it depends. In the second case the result is unpredictable because the database will return the data in whatever order it wants.

Generally, applying a sort condition is quite cheap. But again, you need to check the query plan.

From the console, just append .explain to dump the query plan of a specific query. For example

puts Game.where(private: 0, status: 0).explain
puts Game.where(private: 0, status: 0).order(:id).explain

Upvotes: 3

Related Questions