Posted over 1 year 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 proofs 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:
over 1 year ago
by Arne Hartherz
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 cookies to improve usability and analyze traffic.
Accept or learn more