Method: ActiveRecord::ConnectionAdapters::PostgreSQLAdapter#create_index

Defined in:
lib/active_record/postgresql_extensions/indexes.rb

#create_index(name, object, columns, options = {}) ⇒ Object

Creates an index. This method is an alternative to the standard ActiveRecord add_index method and includes PostgreSQL-specific options. Indexes can be created on tables as well as materialized views starting with PostgreSQL 9.3.

Differences to add_index

  • With the standard ActiveRecord add_index method, ActiveRecord will automatically generate an index name. With create_index, you need to supply a name yourself. This is due to the fact that PostgreSQL’s indexes can include things like expressions and special index types, so we’re not going to try and parse your expressions for you. You’ll have to supply your own index name.

  • Several PostgreSQL-specific options are included. See below for details.

  • The columns argument supports Hashes to allow for expressions. See examples below.

Options

  • :unique - adds UNIQUE to the index definition.

  • :concurrently - adds CONCURRENTLY to the index definition. See the PostgreSQL documentation for a discussion on concurrently reindexing tables.

  • :using - the indexing method to use. PostgreSQL supports serveral indexing methods out of the box, the default being a binary tree method. For certain column types, alternative indexing methods produce better indexing results. In some cases, a btree index would be pointless given certain datatypes and queries. For instance, PostGIS’ geometry datatypes should generally be indexed with GiST indexes, while the tsvector full text search datatype should generally be indexed with a GiN index. See the PostgreSQL documentation for details.

  • :fill_factor - sets the FILLFACTOR value for the index. This option tells PostgreSQL how to pack its index pages on disk. As indexes grow, they begin to get spread out over multiple disk pages, thus reducing efficiency. This option allows you to control some of that behaviour. The default value for btree indexes is 90, and any value from 10 to 100 can be used. See the PostgreSQL documentation for more details.

  • :tablespace - sets the tablespace for the index.

  • :conditions - adds an optional WHERE clause to the index. (You can alternatively use the option :where instead.)

  • :index_parameters - a simple String or Hash used to assign index storage parameters. See the PostgreSQL docs for details on the various storage parameters available.

Column Options

You can specify a handful of options on each index column/expression definition by supplying a Hash for the definition rather than a Symbol/String.

  • :column or :expression - you can specify either :column or :expression in the column definition, but not both. When using :column, the column name is quoted properly using PostgreSQL’s quoting rules, while using :expression leaves you on your own.

  • :opclass - an “opclass” (a.k.a. “operator class”) provides hints to the PostgreSQL query planner that allow it to more effectively take advantage of indexes. An opclass effectively tells the planner what operators can be used by an index when searching a column or expression. When creating an index, PostgreSQL generally uses an opclass equivalent to the column datatype (i.e. int4_ops for an integer column). You can override this behaviour when necessary. For instance, in queries involving the LIKE operator on a text column, PostgreSQL will usually only take advantage of an index if the database has been created in the C locale. You can override this behaviour by forcing the index to be created using the text_pattern_ops opclass

  • :order - the order to index the column in. This can be one of :asc or :desc.

  • :nulls - specify whether NULL values should be placed :first or :last in the index.

Examples

# using multiple columns
create_index('this_is_my_index', :foo, [ :id, :ref_id ], :using => :gin)
# => CREATE INDEX "this_is_my_index" ON "foo"("id", "ref_id");

# using expressions
create_index('this_is_another_idx', :foo, { :expression => 'COALESCE(ref_id, 0)' })
# => CREATE INDEX "this_is_another_idx" ON "foo"((COALESCE(ref_id, 0)));

# additional options
create_index('search_idx', :foo, :tsvector, :using => :gin)
# => CREATE INDEX "search_idx" ON "foo" USING "gin"("tsvector");



113
114
115
 
# File 'lib/active_record/postgresql_extensions/indexes.rb', line 113

def create_index(name, object, columns, options = {})
  execute PostgreSQLIndexDefinition.new(self, name, object, columns, options).to_s
end