Posted almost 3 years ago. Visible to the public. Repeats.

Rails and Postgres: How to test if your index is used as expected

This is a small example on how you can check if your Postgres index can be used by a specific query in you Rails application. For more complex execution plans it might still be a good idea to use the same path of proof.

1. Identify the query your application produces

puts User.order(:last_name, :created_at).to_sql # => SELECT "users".* FROM "users" ORDER BY "users"."last_name" ASC, "users"."created_at" ASC

2. Add an index in your migration and migrate

add_index :users, [:last_name, :created_at]

3. Test the index in your database

ActiveRecord::Base.connection.execute('SET enable_seqscan = OFF') # Try to force index only scan even seq scan is faster query = "EXPLAIN ANALYSE #{User.order(:last_name, :created_at).to_sql}" ActiveRecord::Base.connection.execute(query).each {|result| puts result} # => {"QUERY PLAN"=>"Index Scan using index_users_on_last_name_and_created_at on users (cost=0.28..140.10 rows=1164 width=187) (actual time=0.045..0.499 rows=1164 loops=1)"} # => {"QUERY PLAN"=>"Planning time: 0.096 ms"} # => {"QUERY PLAN"=>"Execution time: 0.592 ms"} ActiveRecord::Base.connection.execute('SET enable_seqscan = ON') # Don't forget to reset query planner's settings

This proves that the query can use the index on last_name and created_at if it is the cheapest execution plan.

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Emanuel De
Last edit:
11 months ago
by Daniel Straßner
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Emanuel De to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more