Postgres allows you to create an index on transformed values of a column. This means we can create an index on the lowercased value for each of our three fields. Further, we can configure the index in a way that allows Postgres to optimize for the “starts with” search we are doing. Here’s the basic syntax:
|||customers (lower(last_name) varchar_pattern_ops);|
If you’re familiar with creating indexes in general, 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 ...