Rails, Angular, Postgres, and Bootstrap, 2nd Edition

Indexing Derived and Partial Values

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 ...