Using dbconsole to index and test Rails query performance on the fly

Rails developers are very familiar with hopping into the application console using bin/rails console (or bundle exec rails console) to run quick metrics.

> Executing task: bin/rails console <

Running via Spring preloader in process 3997
Loading development environment (Rails 6.1.4)
bestreads-development> Book.count
   (2.1ms)  SELECT COUNT(*) FROM "books"
  => 10201

However, it wasn't until recently that I started using the equivalent command for loading the console of my database (Postgres), rails dbconsole:

> Executing task: bin/rails dbconsole <

psql (13.3)
Type "help" for help.

[local] anhari@bestreads_development=# select count(*) from books;
┌───────┐
│ count │
╞═══════╡
│ 10201 │
└───────┘
(1 row)

Time: 1.968 ms
[local] anhari@bestreads_development=# 

This is a great environment for testing out things on the fly before you follow-through with creating migration files to cement your changes.

For example, let's say you were trying to optimize a query on your books table that was filtering a book by it's title attribute. You might hop into your dbconsole and try something like this:

explain analyze select * from books where title = 'The Lord of the Rings';
create index index_books_on_title on books(title);
explain analyze select * from books where title = 'The Lord of the Rings';
drop index index_books_on_title;

Let's break down the commands:

1. Filtering the books table and looking for LOTR using the title column, and using EXPLAIN ANALYZE to see the query plan and run the actual query to guage performance.
2. Create a btree index on books.title
3. Re-run the query using EXPLAIN ANALYZE and read the new query plan and guage performance
4. Drop the index so that we can create it via a Rails migration