How to Create PG Index Operators in Ecto
1 min read

How to Create PG Index Operators in Ecto

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 |
	    \"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 text_pattern_ops.

Hope this will be useful to someone else.