1CREATE INDEX(7) PostgreSQL 10.7 Documentation CREATE INDEX(7)
2
6 CREATE_INDEX - define a new index
7
9 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
10 ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
11 [ WITH ( storage_parameter = value [, ... ] ) ]
12 [ TABLESPACE tablespace_name ]
13 [ WHERE predicate ]
14
16 CREATE INDEX constructs an index on the specified column(s) of the
17 specified relation, which can be a table or a materialized view.
18 Indexes are primarily used to enhance database performance (though
19 inappropriate use can result in slower performance).
20 The key field(s) for the index are specified as column names, or
22 alternatively as expressions written in parentheses. Multiple fields
23 can be specified if the index method supports multicolumn indexes.
24 An index field can be an expression computed from the values of one or
26 more columns of the table row. This feature can be used to obtain fast
27 access to data based on some transformation of the basic data. For
28 example, an index computed on upper(col) would allow the clause WHERE
29 upper(col) = 'JIM' to use an index.
30 PostgreSQL provides the index methods B-tree, hash, GiST, SP-GiST, GIN,
32 and BRIN. Users can also define their own index methods, but that is
33 fairly complicated.
34 When the WHERE clause is present, a partial index is created. A partial
36 index is an index that contains entries for only a portion of a table,
37 usually a portion that is more useful for indexing than the rest of the
38 table. For example, if you have a table that contains both billed and
39 unbilled orders where the unbilled orders take up a small fraction of
40 the total table and yet that is an often used section, you can improve
41 performance by creating an index on just that portion. Another possible
42 application is to use WHERE with UNIQUE to enforce uniqueness over a
43 subset of a table. See Section 11.8 for more discussion.
44 The expression used in the WHERE clause can refer only to columns of
46 the underlying table, but it can use all columns, not just the ones
47 being indexed. Presently, subqueries and aggregate expressions are also
48 forbidden in WHERE. The same restrictions apply to index fields that
49 are expressions.
50 All functions and operators used in an index definition must be
52 “immutable”, that is, their results must depend only on their arguments
53 and never on any outside influence (such as the contents of another
54 table or the current time). This restriction ensures that the behavior
55 of the index is well-defined. To use a user-defined function in an
56 index expression or WHERE clause, remember to mark the function
57 immutable when you create it.
58
60 UNIQUE
61 Causes the system to check for duplicate values in the table when
62 the index is created (if data already exist) and each time data is
63 added. Attempts to insert or update data which would result in
64 duplicate entries will generate an error.
65 CONCURRENTLY
67 When this option is used, PostgreSQL will build the index without
68 taking any locks that prevent concurrent inserts, updates, or
69 deletes on the table; whereas a standard index build locks out
70 writes (but not reads) on the table until it's done. There are
71 several caveats to be aware of when using this option — see
72 Building Indexes Concurrently.
73 IF NOT EXISTS
75 Do not throw an error if a relation with the same name already
76 exists. A notice is issued in this case. Note that there is no
77 guarantee that the existing index is anything like the one that
78 would have been created. Index name is required when IF NOT EXISTS
79 is specified.
80 name
82 The name of the index to be created. No schema name can be included
83 here; the index is always created in the same schema as its parent
84 table. If the name is omitted, PostgreSQL chooses a suitable name
85 based on the parent table's name and the indexed column name(s).
86 table_name
88 The name (possibly schema-qualified) of the table to be indexed.
89 method
91 The name of the index method to be used. Choices are btree, hash,
92 gist, spgist, gin, and brin. The default method is btree.
93 column_name
95 The name of a column of the table.
96 expression
98 An expression based on one or more columns of the table. The
99 expression usually must be written with surrounding parentheses, as
100 shown in the syntax. However, the parentheses can be omitted if the
101 expression has the form of a function call.
102 collation
104 The name of the collation to use for the index. By default, the
105 index uses the collation declared for the column to be indexed or
106 the result collation of the expression to be indexed. Indexes with
107 non-default collations can be useful for queries that involve
108 expressions using non-default collations.
109 opclass
111 The name of an operator class. See below for details.
112 ASC
114 Specifies ascending sort order (which is the default).
115 DESC
117 Specifies descending sort order.
118 NULLS FIRST
120 Specifies that nulls sort before non-nulls. This is the default
121 when DESC is specified.
122 NULLS LAST
124 Specifies that nulls sort after non-nulls. This is the default when
125 DESC is not specified.
126 storage_parameter
128 The name of an index-method-specific storage parameter. See Index
129 Storage Parameters for details.
130 tablespace_name
132 The tablespace in which to create the index. If not specified,
133 default_tablespace is consulted, or temp_tablespaces for indexes on
134 temporary tables.
135 predicate
137 The constraint expression for a partial index.
138 Index Storage Parameters
140 The optional WITH clause specifies storage parameters for the index.
141 Each index method has its own set of allowed storage parameters. The
142 B-tree, hash, GiST and SP-GiST index methods all accept this parameter:
143 fillfactor
145 The fillfactor for an index is a percentage that determines how
146 full the index method will try to pack index pages. For B-trees,
147 leaf pages are filled to this percentage during initial index
148 build, and also when extending the index at the right (adding new
149 largest key values). If pages subsequently become completely full,
150 they will be split, leading to gradual degradation in the index's
151 efficiency. B-trees use a default fillfactor of 90, but any integer
152 value from 10 to 100 can be selected. If the table is static then
153 fillfactor 100 is best to minimize the index's physical size, but
154 for heavily updated tables a smaller fillfactor is better to
155 minimize the need for page splits. The other index methods use
156 fillfactor in different but roughly analogous ways; the default
157 fillfactor varies between methods.
158 GiST indexes additionally accept this parameter:
160 buffering
162 Determines whether the buffering build technique described in
163 Section 62.4.1 is used to build the index. With OFF it is disabled,
164 with ON it is enabled, and with AUTO it is initially disabled, but
165 turned on on-the-fly once the index size reaches
166 effective_cache_size. The default is AUTO.
167 GIN indexes accept different parameters:
169 fastupdate
171 This setting controls usage of the fast update technique described
172 in Section 64.4.1. It is a Boolean parameter: ON enables fast
173 update, OFF disables it. (Alternative spellings of ON and OFF are
174 allowed as described in Section 19.1.) The default is ON.
175 Note
177 Turning fastupdate off via ALTER INDEX prevents future
178 insertions from going into the list of pending index entries,
179 but does not in itself flush previous entries. You might want
180 to VACUUM the table or call gin_clean_pending_list function
181 afterward to ensure the pending list is emptied.
182 gin_pending_list_limit
184 Custom gin_pending_list_limit parameter. This value is specified in
185 kilobytes.
186 BRIN indexes accept different parameters:
188 pages_per_range
190 Defines the number of table blocks that make up one block range for
191 each entry of a BRIN index (see Section 65.1 for more details). The
192 default is 128.
193 autosummarize
195 Defines whether a summarization run is invoked for the previous
196 page range whenever an insertion is detected on the next one.
197 Building Indexes Concurrently
199 Creating an index can interfere with regular operation of a database.
200 Normally PostgreSQL locks the table to be indexed against writes and
201 performs the entire index build with a single scan of the table. Other
202 transactions can still read the table, but if they try to insert,
203 update, or delete rows in the table they will block until the index
204 build is finished. This could have a severe effect if the system is a
205 live production database. Very large tables can take many hours to be
206 indexed, and even for smaller tables, an index build can lock out
207 writers for periods that are unacceptably long for a production system.
208 PostgreSQL supports building indexes without locking out writes. This
210 method is invoked by specifying the CONCURRENTLY option of CREATE
211 INDEX. When this option is used, PostgreSQL must perform two scans of
212 the table, and in addition it must wait for all existing transactions
213 that could potentially modify or use the index to terminate. Thus this
214 method requires more total work than a standard index build and takes
215 significantly longer to complete. However, since it allows normal
216 operations to continue while the index is built, this method is useful
217 for adding new indexes in a production environment. Of course, the
218 extra CPU and I/O load imposed by the index creation might slow other
219 operations.
220 In a concurrent index build, the index is actually entered into the
222 system catalogs in one transaction, then two table scans occur in two
223 more transactions. Before each table scan, the index build must wait
224 for existing transactions that have modified the table to terminate.
225 After the second scan, the index build must wait for any transactions
226 that have a snapshot (see Chapter 13) predating the second scan to
227 terminate. Then finally the index can be marked ready for use, and the
228 CREATE INDEX command terminates. Even then, however, the index may not
229 be immediately usable for queries: in the worst case, it cannot be used
230 as long as transactions exist that predate the start of the index
231 build.
232 If a problem arises while scanning the table, such as a deadlock or a
234 uniqueness violation in a unique index, the CREATE INDEX command will
235 fail but leave behind an “invalid” index. This index will be ignored
236 for querying purposes because it might be incomplete; however it will
237 still consume update overhead. The psql \d command will report such an
238 index as INVALID:
239 postgres=# \d tab
241 Table "public.tab"
242 Column | Type | Collation | Nullable | Default
243 --------+---------+-----------+----------+---------
244 col | integer | | |
245 Indexes:
246 "idx" btree (col) INVALID
247 The recommended recovery method in such cases is to drop the index and
249 try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is
250 to rebuild the index with REINDEX. However, since REINDEX does not
251 support concurrent builds, this option is unlikely to seem attractive.)
252 Another caveat when building a unique index concurrently is that the
254 uniqueness constraint is already being enforced against other
255 transactions when the second table scan begins. This means that
256 constraint violations could be reported in other queries prior to the
257 index becoming available for use, or even in cases where the index
258 build eventually fails. Also, if a failure does occur in the second
259 scan, the “invalid” index continues to enforce its uniqueness
260 constraint afterwards.
261 Concurrent builds of expression indexes and partial indexes are
263 supported. Errors occurring in the evaluation of these expressions
264 could cause behavior similar to that described above for unique
265 constraint violations.
266 Regular index builds permit other regular index builds on the same
268 table to occur in parallel, but only one concurrent index build can
269 occur on a table at a time. In both cases, no other types of schema
270 modification on the table are allowed meanwhile. Another difference is
271 that a regular CREATE INDEX command can be performed within a
272 transaction block, but CREATE INDEX CONCURRENTLY cannot.
273
275 See Chapter 11 for information about when indexes can be used, when
276 they are not used, and in which particular situations they can be
277 useful.
278 Currently, only the B-tree, GiST, GIN, and BRIN index methods support
280 multicolumn indexes. Up to 32 fields can be specified by default. (This
281 limit can be altered when building PostgreSQL.) Only B-tree currently
282 supports unique indexes.
283 An operator class can be specified for each column of an index. The
285 operator class identifies the operators to be used by the index for
286 that column. For example, a B-tree index on four-byte integers would
287 use the int4_ops class; this operator class includes comparison
288 functions for four-byte integers. In practice the default operator
289 class for the column's data type is usually sufficient. The main point
290 of having operator classes is that for some data types, there could be
291 more than one meaningful ordering. For example, we might want to sort a
292 complex-number data type either by absolute value or by real part. We
293 could do this by defining two operator classes for the data type and
294 then selecting the proper class when making an index. More information
295 about operator classes is in Section 11.9 and in Section 37.14.
296 For index methods that support ordered scans (currently, only B-tree),
298 the optional clauses ASC, DESC, NULLS FIRST, and/or NULLS LAST can be
299 specified to modify the sort ordering of the index. Since an ordered
300 index can be scanned either forward or backward, it is not normally
301 useful to create a single-column DESC index — that sort ordering is
302 already available with a regular index. The value of these options is
303 that multicolumn indexes can be created that match the sort ordering
304 requested by a mixed-ordering query, such as SELECT ... ORDER BY x ASC,
305 y DESC. The NULLS options are useful if you need to support “nulls sort
306 low” behavior, rather than the default “nulls sort high”, in queries
307 that depend on indexes to avoid sorting steps.
308 For most index methods, the speed of creating an index is dependent on
310 the setting of maintenance_work_mem. Larger values will reduce the time
311 needed for index creation, so long as you don't make it larger than the
312 amount of memory really available, which would drive the machine into
313 swapping.
314 Use DROP INDEX (DROP_INDEX(7)) to remove an index.
316 Prior releases of PostgreSQL also had an R-tree index method. This
318 method has been removed because it had no significant advantages over
319 the GiST method. If USING rtree is specified, CREATE INDEX will
320 interpret it as USING gist, to simplify conversion of old databases to
321 GiST.
322
324 To create a B-tree index on the column title in the table films:
325 CREATE UNIQUE INDEX title_idx ON films (title);
327 To create an index on the expression lower(title), allowing efficient
329 case-insensitive searches:
330 CREATE INDEX ON films ((lower(title)));
332 (In this example we have chosen to omit the index name, so the system
334 will choose a name, typically films_lower_idx.)
335 To create an index with non-default collation:
337 CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");
339 To create an index with non-default sort ordering of nulls:
341 CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
343 To create an index with non-default fill factor:
345 CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
347 To create a GIN index with fast updates disabled:
349 CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);
351 To create an index on the column code in the table films and have the
353 index reside in the tablespace indexspace:
354 CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;
356 To create a GiST index on a point attribute so that we can efficiently
358 use box operators on the result of the conversion function:
359 CREATE INDEX pointloc
361 ON points USING gist (box(location,location));
362 SELECT * FROM points
363 WHERE box(location,location) && '(0,0),(1,1)'::box;
364 To create an index without locking out writes to the table:
366 CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
368
370 CREATE INDEX is a PostgreSQL language extension. There are no
371 provisions for indexes in the SQL standard.
372
374 ALTER INDEX (ALTER_INDEX(7)), DROP INDEX (DROP_INDEX(7))
375PostgreSQL 10.7 2019 CREATE INDEX(7)