Postgres allows you to create an index on transformed values of a column. This means you can create an index on the lowercased value for each of our three fields. Further, you can configure the index in a way that allows Postgres to optimize for the “starts with” search you are doing. Here’s the basic syntax:
| | CREATE INDEX |
| | customers_lower_last_name |
| | ON |
| | customers (lower(last_name) varchar_pattern_ops); |
If you’re familiar with creating indexes, the varchar_pattern_ops might look odd. This is a feature of Postgres called operator classes. Specifying an operator class isn’t required; however, the default operator class used by Postgres will only optimize the index for an exact match. Because you’re using a like in your search, you need to use the nonstandard operator class varchar_pattern_ops. You can read more about operator classes in Postgres’s documentation.[36]
Now that you’ve seen the SQL needed to create these indexes, you need to adapt them to a Rails migration. Previous versions of Rails didn’t provide a way to do this, and you’d have to use execute to directly execute SQL, but as of Rails 5, we can pass custom SQL to add_index, making our migration a bit cleaner. Let’s create the migration file using Rails’s generator.
| | $ bundle exec rails g migration add-lower-indexes-to-customers |
| | invoke active_record |
| | create db/migrate/20160721030725_add_lower_indexes_to_customers.rb |
Next, edit the migration to add the indexes. Rails 5 added the ability to create these Postgres-specific indexes using add_index. Previous versions of Rails required using execute and typing the CREATE INDEX SQL directly.
| | class AddLowerIndexesToCustomers < ActiveRecord::Migration[5.0] |
| | def change |
| | add_index :customers, "lower(last_name) varchar_pattern_ops" |
| | add_index :customers, "lower(first_name) varchar_pattern_ops" |
| | add_index :customers, "lower(email)" |
| | end |
| | end |
Note that we aren’t using the operator class on the email index since we’ll always be doing an exact match. Sticking with the default operator class is recommended if we don’t have a reason not to. Next, let’s run this migration (it may take several seconds due to the volume of data being indexed).
| | $ bundle exec rails db:migrate |
| | == 20160721030725 AddLowerIndexesToCustomers: migrating =========== |
| | -- add_index(:customers, "lower(last_name) varchar_pattern_ops") |
| | -> 0.5506s |
| | -- add_index(:customers, "lower(first_name) varchar_pattern_ops") |
| | -> 0.4963s |
| | -- add_index(:customers, "lower(email)") |
| | -> 7.1292s |
| | == 20160721030725 AddLowerIndexesToCustomers: migrated (8.1763s) == |
Before you try the app, let’s run the EXPLAIN ANALYZE again and see what it says. Note the highlighted lines.
| | $ bundle exec rails dbconsole |
| | shine_development> EXPLAIN ANALYZE |
| | SELECT * |
| | FROM customers |
| | WHERE |
| | lower(first_name) like 'pat%' OR |
| | lower(last_name) like 'pat%' OR |
| | lower(email) = 'pat@example.com' |
| | ORDER BY |
| | email = 'pat@example.com' DESC, |
| | last_name ASC |
| | ; |
| | QUERY PLAN |
| | ------------------------------------------------------------------------- |
| | Sort (cost=5666.10..5679.16 rows=5224 width=79) |
| | (actual time=14.467..14.537 rows=704 loops=1) |
| | Sort Key: (((email)::text = 'pat@example.com'::text)) DESC, last_name |
| | Sort Method: quicksort Memory: 124kB |
| » | -> Bitmap Heap Scan on customers |
| | (cost=145.31..5343.49 rows=5224 width=79) |
| | (actual time=0.387..8.650 rows=704 loops=1) |
| | Recheck Cond: ((lower((first_name)::text) ~~ 'pat%'::text) OR |
| | (lower((last_name)::text) ~~ 'pat%'::text) OR |
| | (lower((email)::text) = 'pat@example.com'::text)) |
| | Filter: ((lower((first_name)::text) ~~ 'pat%'::text) OR |
| | (lower((last_name)::text) ~~ 'pat%'::text) OR |
| | (lower((email)::text) = 'pat@example.com'::text)) |
| | Heap Blocks: exact=655 |
| | -> BitmapOr (cost=145.31..145.31 rows=5250 width=0) |
| | (actual time=0.263..0.263 rows=0 loops=1) |
| | -> Bitmap Index Scan on |
| | index_customers_on_lower_first_name_varchar_pattern_ops |
| | (cost=0.00..41.92 rows=1750 width=0) |
| | (actual time=0.209..0.209 rows=704 loops=1) |
| » | Index Cond: ( |
| » | (lower((first_name)::text) ~>=~ 'pat'::text) AND |
| » | (lower((first_name)::text) ~<~ 'pau'::text)) |
| | -> Bitmap Index Scan on |
| | index_customers_on_lower_last_name_varchar_pattern_ops |
| | (cost=0.00..41.92 rows=1750 width=0) |
| | (actual time=0.007..0.007 rows=0 loops=1) |
| » | Index Cond: ( |
| » | (lower((last_name)::text) ~>=~ 'pat'::text) AND |
| » | (lower((last_name)::text) ~<~ 'pau'::text)) |
| | -> Bitmap Index Scan on index_customers_on_lower_email |
| | (cost=0.00..57.55 rows=1750 width=0) |
| | (actual time=0.046..0.046 rows=0 loops=1) |
| » | Index Cond: ( |
| » | lower((email)::text) = 'pat@example.com'::text) |
| » | Planning time: 0.193 ms |
| » | Execution time: 14.732 ms |
This time, there is more gobbledegook, but if you look closely, Seq Scan on customers is gone, and you can see a lot of detail around our where clause. The highlighted lines indicate index scans, in contrast to the Seq Scan you saw before. And the index scan is using our index and thus not examining each row in the table to find the correct results. You can see that it’s doing three lookups, one for each field, using our indexes, and then or-ing the results together.
Setting aside the details of how Postgres does this, you can see that the results are about 40 times faster—the query should complete in under 15 milliseconds!
If you try our search in Shine now, the results come back almost instantly. We’ve improved the performance of our search by more than a factor of 40, all with just a few lines of SQL in a migration. And you didn’t have to change a line of code in the Rails application. If you were using a less powerful database, you’d need to set up new infrastructure for making this search fast, and that could have a significant cost to development, maintenance, and production support.
This sort of index is just the tip of the iceberg—Postgres has many advanced features.
With our search performing better, let’s take a final pass at the user interface. Bootstrap’s default table styling made it a snap to create a reasonable user interface in no time. This then enabled us to focus on the Rails application’s behavior and performance. If you stopped now and shipped what you have, you’d be shipping a feature you could be proud of. But, because you haven’t spent that much time on this feature, let’s see if there’s any way to make the UI better for our users.