1CREATE INDEX() SQL Commands CREATE INDEX()
2
6 CREATE INDEX - define a new index
7
10 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
11 ( { column | ( expression ) } [ opclass ] [, ...] )
12 [ WITH ( storage_parameter = value [, ... ] ) ]
13 [ TABLESPACE tablespace ]
14 [ WHERE predicate ]
15
18 CREATE INDEX constructs an index index_name on the specified table.
19 Indexes are primarily used to enhance database performance (though
20 inappropriate use can result in slower performance).
21 The key field(s) for the index are specified as column names, or alter‐
23 natively as expressions written in parentheses. Multiple fields can be
24 specified if the index method supports multicolumn indexes.
25 An index field can be an expression computed from the values of one or
27 more columns of the table row. This feature can be used to obtain fast
28 access to data based on some transformation of the basic data. For
29 example, an index computed on upper(col) would allow the clause WHERE
30 upper(col) = 'JIM' to use an index.
31 PostgreSQL provides the index methods B-tree, hash, GiST, and GIN.
33 Users can also define their own index methods, but that is fairly com‐
34 plicated.
35 When the WHERE clause is present, a partial index is created. A par‐
37 tial index is an index that contains entries for only a portion of a
38 table, usually a portion that is more useful for indexing than the rest
39 of the table. For example, if you have a table that contains both
40 billed and unbilled orders where the unbilled orders take up a small
41 fraction of the total table and yet that is an often used section, you
42 can improve performance by creating an index on just that portion.
43 Another possible application is to use WHERE with UNIQUE to enforce
44 uniqueness over a subset of a table. See in the documentation for more
45 discussion.
46 The expression used in the WHERE clause may refer only to columns of
48 the underlying table, but it can use all columns, not just the ones
49 being indexed. Presently, subqueries and aggregate expressions are also
50 forbidden in WHERE. The same restrictions apply to index fields that
51 are expressions.
52 All functions and operators used in an index definition must be
54 ``immutable'', that is, their results must depend only on their argu‐
55 ments and never on any outside influence (such as the contents of
56 another table or the current time). This restriction ensures that the
57 behavior of the index is well-defined. To use a user-defined function
58 in an index expression or WHERE clause, remember to mark the function
59 immutable when you create it.
60
62 UNIQUE Causes the system to check for duplicate values in the table
63 when the index is created (if data already exist) and each time
64 data is added. Attempts to insert or update data which would
65 result in duplicate entries will generate an error.
66 CONCURRENTLY
68 When this option is used, PostgreSQL will build the index with‐
69 out taking any locks that prevent concurrent inserts, updates,
70 or deletes on the table; whereas a standard index build locks
71 out writes (but not reads) on the table until it's done. There
72 are several caveats to be aware of when using this option — see
73 Building Indexes Concurrently [create_index(7)].
74 name The name of the index to be created. No schema name can be
76 included here; the index is always created in the same schema as
77 its parent table.
78 table The name (possibly schema-qualified) of the table to be indexed.
80 method The name of the index method to be used. Choices are btree,
82 hash, gist, and gin. The default method is btree.
83 column The name of a column of the table.
85 expression
87 An expression based on one or more columns of the table. The
88 expression usually must be written with surrounding parentheses,
89 as shown in the syntax. However, the parentheses may be omitted
90 if the expression has the form of a function call.
91 opclass
93 The name of an operator class. See below for details.
94 storage_parameter
96 The name of an index-method-specific storage parameter. See
97 below for details.
98 tablespace
100 The tablespace in which to create the index. If not specified,
101 default_tablespace is used, or the database's default tablespace
102 if default_tablespace is an empty string.
103 predicate
105 The constraint expression for a partial index.
106 INDEX STORAGE PARAMETERS
108 The WITH clause can specify storage parameters for indexes. Each index
109 method can have its own set of allowed storage parameters. The built-in
110 index methods all accept a single parameter:
111 FILLFACTOR
113 The fillfactor for an index is a percentage that determines how
114 full the index method will try to pack index pages. For B-trees,
115 leaf pages are filled to this percentage during initial index
116 build, and also when extending the index at the right (largest
117 key values). If pages subsequently become completely full, they
118 will be split, leading to gradual degradation in the index's
119 efficiency. B-trees use a default fillfactor of 90, but any
120 value from 10 to 100 can be selected. If the table is static
121 then fillfactor 100 is best to minimize the index's physical
122 size, but for heavily updated tables a smaller fillfactor is
123 better to minimize the need for page splits. The other index
124 methods use fillfactor in different but roughly analogous ways;
125 the default fillfactor varies between methods.
126 BUILDING INDEXES CONCURRENTLY
128 Creating an index can interfere with regular operation of a database.
129 Normally PostgreSQL locks the table to be indexed against writes and
130 performs the entire index build with a single scan of the table. Other
131 transactions can still read the table, but if they try to insert,
132 update, or delete rows in the table they will block until the index
133 build is finished. This could have a severe effect if the system is a
134 live production database. Large tables can take many hours to be
135 indexed, and even for smaller tables, an index build can lock out writ‐
136 ers for periods that are unacceptably long for a production system.
137 PostgreSQL supports building indexes without locking out writes. This
139 method is invoked by specifying the CONCURRENTLY option of CREATE
140 INDEX. When this option is used, PostgreSQL must perform two scans of
141 the table, and in addition it must wait for all existing transactions
142 to terminate. Thus this method requires more total work than a standard
143 index build and takes significantly longer to complete. However, since
144 it allows normal operations to continue while the index is built, this
145 method is useful for adding new indexes in a production environment. Of
146 course, the extra CPU and I/O load imposed by the index creation may
147 slow other operations.
148 If a problem arises during the second scan of the table, such as a
150 uniqueness violation in a unique index, the CREATE INDEX command will
151 fail but leave behind an ``invalid'' index. This index will be ignored
152 for querying purposes because it may be incomplete; however it will
153 still consume update overhead. The recommended recovery method in such
154 cases is to drop the index and try again to perform CREATE INDEX CON‐
155 CURRENTLY. (Another possibility is to rebuild the index with REINDEX.
156 However, since REINDEX does not support concurrent builds, this option
157 is unlikely to seem attractive.)
158 Another caveat when building a unique index concurrently is that the
160 uniqueness constraint is already being enforced against other transac‐
161 tions when the second table scan begins. This means that constraint
162 violations could be reported in other queries prior to the index becom‐
163 ing available for use, or even in cases where the index build eventu‐
164 ally fails. Also, if a failure does occur in the second scan, the
165 ``invalid'' index continues to enforce its uniqueness constraint after‐
166 wards.
167 Concurrent builds of expression indexes and partial indexes are sup‐
169 ported. Errors occurring in the evaluation of these expressions could
170 cause behavior similar to that described above for unique constraint
171 violations.
172 Regular index builds permit other regular index builds on the same ta‐
174 ble to occur in parallel, but only one concurrent index build can occur
175 on a table at a time. In both cases, no other types of schema modifica‐
176 tion on the table are allowed meanwhile. Another difference is that a
177 regular CREATE INDEX command can be performed within a transaction
178 block, but CREATE INDEX CONCURRENTLY cannot.
179
181 See in the documentation for information about when indexes can be
182 used, when they are not used, and in which particular situations they
183 can be useful.
184 Currently, only the B-tree and GiST index methods support multicolumn
186 indexes. Up to 32 fields may be specified by default. (This limit can
187 be altered when building PostgreSQL.) Only B-tree currently supports
188 unique indexes.
189 An operator class can be specified for each column of an index. The
191 operator class identifies the operators to be used by the index for
192 that column. For example, a B-tree index on four-byte integers would
193 use the int4_ops class; this operator class includes comparison func‐
194 tions for four-byte integers. In practice the default operator class
195 for the column's data type is usually sufficient. The main point of
196 having operator classes is that for some data types, there could be
197 more than one meaningful ordering. For example, we might want to sort a
198 complex-number data type either by absolute value or by real part. We
199 could do this by defining two operator classes for the data type and
200 then selecting the proper class when making an index. More information
201 about operator classes is in in the documentation and in in the docu‐
202 mentation.
203 Use DROP INDEX [drop_index(7)] to remove an index.
205 Indexes are not used for IS NULL clauses by default. The best way to
207 use indexes in such cases is to create a partial index using an IS NULL
208 predicate.
209 Prior releases of PostgreSQL also had an R-tree index method. This
211 method has been removed because it had no significant advantages over
212 the GiST method. If USING rtree is specified, CREATE INDEX will inter‐
213 pret it as USING gist, to simplify conversion of old databases to GiST.
214
216 To create a B-tree index on the column title in the table films:
217 CREATE UNIQUE INDEX title_idx ON films (title);
219 To create an index on the expression lower(title), allowing efficient
222 case-insensitive searches:
223 CREATE INDEX lower_title_idx ON films ((lower(title)));
225 To create an index with non-default fill factor:
228 CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
230 To create an index on the column code in the table films and have the
233 index reside in the tablespace indexspace:
234 CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
236 To create an index without locking out writes to the table:
239 CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
241
244 CREATE INDEX is a PostgreSQL language extension. There are no provi‐
245 sions for indexes in the SQL standard.
246
248 ALTER INDEX [alter_index(7)], DROP INDEX [drop_index(l)]
249SQL - Language Statements 2008-06-08 CREATE INDEX()