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