I recently learned about the
text_pattern_ops index that was introduced in Postgres 8.4.
It adds an index that will speed up both `WHERE my_column = 'foo'` and `LIKE` queries on text columns. The postgres syntax for creating the index looks like this. ```SQL CREATE INDEX my_column_text_pattern_index ON my_table (my_column text_pattern_ops); ```
It wasn't quite obvious from the Ecto docs how to accomplish this using Ecto migrations. Here's what worked for me.
create index(:my_table, [\"my_column text_pattern_ops\"], name: :my_column_text_pattern_index) ``` To check that the index was applied run this in the psql repl: ``` # \\d my_table Table \"public.my_table\" Column | Type | Modifiers -------------+------+----------- my_column | text | Indexes: \"my_column_text_pattern_index\" btree (my_column text_pattern_ops)
Check out this article by Peter Esentraut if you would like to read more about
Hope this will be useful to someone else.