Provided by: postgresql-client-8.4_8.4.3-1_i386 bug

NAME

       CREATE INDEX - define a new index

SYNOPSIS

       CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
           ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
           [ WITH ( storage_parameter = value [, ... ] ) ]
           [ TABLESPACE tablespace ]
           [ WHERE predicate ]

DESCRIPTION

       CREATE  INDEX  constructs  an  index named name on the specified table.
       Indexes are primarily used  to  enhance  database  performance  (though
       inappropriate use can result in slower performance).

       The  key  field(s)  for  the  index  are  specified as column names, or
       alternatively as expressions written in parentheses.   Multiple  fields
       can be specified if the index method supports multicolumn indexes.

       An  index field can be an expression computed from the values of one or
       more columns of the table row. This feature can be used to obtain  fast
       access  to  data  based  on  some transformation of the basic data. For
       example, an index computed on upper(col) would allow the  clause  WHERE
       upper(col) = ’JIM’ to use an index.

       PostgreSQL  provides  the  index  methods  B-tree, hash, GiST, and GIN.
       Users can also define their own  index  methods,  but  that  is  fairly
       complicated.

       When  the  WHERE  clause  is  present,  a  partial index is created.  A
       partial index is an index that contains entries for only a portion of a
       table, usually a portion that is more useful for indexing than the rest
       of the table. For example, if you  have  a  table  that  contains  both
       billed  and  unbilled  orders where the unbilled orders take up a small
       fraction of the total table and yet that is an often used section,  you
       can  improve  performance  by  creating  an index on just that portion.
       Another possible application is to use WHERE  with  UNIQUE  to  enforce
       uniqueness  over a subset of a table. See in the documentation for more
       discussion.

       The expression used in the WHERE clause can refer only  to  columns  of
       the  underlying  table,  but  it can use all columns, not just the ones
       being indexed. Presently, subqueries and aggregate expressions are also
       forbidden  in  WHERE.  The same restrictions apply to index fields that
       are expressions.

       All functions and  operators  used  in  an  index  definition  must  be
       ‘‘immutable’’,  that  is,  their  results  must  depend  only  on their
       arguments and never on any outside influence (such as the  contents  of
       another  table  or the current time). This restriction ensures that the
       behavior of the index is well-defined. To use a  user-defined  function
       in  an  index expression or WHERE clause, remember to mark the function
       immutable when you create it.

PARAMETERS

       UNIQUE Causes the system to check for duplicate  values  in  the  table
              when  the index is created (if data already exist) and each time
              data is added. Attempts to insert or  update  data  which  would
              result in duplicate entries will generate an error.

       CONCURRENTLY
              When  this  option  is  used,  PostgreSQL  will  build the index
              without  taking  any  locks  that  prevent  concurrent  inserts,
              updates, or deletes on the table; whereas a standard index build
              locks out writes (but not reads) on the table until  it’s  done.
              There  are several caveats to be aware of when using this option
              — see Building Indexes Concurrently [create_index(7)].

       name   The name of the index to be  created.  No  schema  name  can  be
              included here; the index is always created in the same schema as
              its parent table.

       table  The name (possibly schema-qualified) of the table to be indexed.

       method The  name  of  the  index  method to be used. Choices are btree,
              hash, gist, and gin. The default method is btree.

       column The name of a column of the table.

       expression
              An expression based on one or more columns  of  the  table.  The
              expression usually must be written with surrounding parentheses,
              as shown in the syntax. However, the parentheses can be  omitted
              if the expression has the form of a function call.

       opclass
              The name of an operator class. See below for details.

       ASC    Specifies ascending sort order (which is the default).

       DESC   Specifies descending sort order.

       NULLS FIRST
              Specifies  that nulls sort before non-nulls. This is the default
              when DESC is specified.

       NULLS LAST
              Specifies that nulls sort after non-nulls. This is  the  default
              when DESC is not specified.

       storage_parameter
              The  name  of  an  index-method-specific  storage parameter. See
              Index Storage Parameters [create_index(7)] for details.

       tablespace
              The tablespace in which to create the index. If  not  specified,
              default_tablespace is consulted, or temp_tablespaces for indexes
              on temporary tables.

       predicate
              The constraint expression for a partial index.

   INDEX STORAGE PARAMETERS
       The WITH clause can specify storage parameters for indexes. Each  index
       method  can have its own set of allowed storage parameters. The B-tree,
       hash and GiST built-in index methods all accept a single parameter:

       FILLFACTOR
              The fillfactor for an index is a percentage that determines  how
              full the index method will try to pack index pages. For B-trees,
              leaf pages are filled to this percentage  during  initial  index
              build,  and  also when extending the index at the right (largest
              key values). If pages subsequently become completely full,  they
              will  be  split,  leading  to gradual degradation in the index’s
              efficiency. B-trees use a default  fillfactor  of  90,  but  any
              value  from  10  to 100 can be selected.  If the table is static
              then fillfactor 100 is best to  minimize  the  index’s  physical
              size,  but  for  heavily  updated tables a smaller fillfactor is
              better to minimize the need for page  splits.  The  other  index
              methods  use fillfactor in different but roughly analogous ways;
              the default fillfactor varies between methods.

       GIN indexes accept a different parameter:

       FASTUPDATE
              This  setting  controls  usage  of  the  fast  update  technique
              described in in the documentation. It is a Boolean parameter: ON
              enables fast update, OFF disables it.  (Alternative spellings of
              ON  and  OFF  are allowed as described in in the documentation.)
              The default is ON.

              Note: Turning FASTUPDATE off via  ALTER  INDEX  prevents  future
              insertions  from  going  into the list of pending index entries,
              but does not in itself flush previous entries. You might want to
              VACUUM  the  table  afterward  to  ensure  the  pending  list is
              emptied.

   BUILDING INDEXES CONCURRENTLY
       Creating an index can interfere with regular operation of  a  database.
       Normally  PostgreSQL  locks  the table to be indexed against writes and
       performs the entire index build with a single scan of the table.  Other
       transactions  can  still  read  the  table,  but if they try to insert,
       update, or delete rows in the table they will  block  until  the  index
       build  is  finished. This could have a severe effect if the system is a
       live production database. Very large tables can take many hours  to  be
       indexed,  and  even  for  smaller  tables,  an index build can lock out
       writers for periods that are unacceptably long for a production system.

       PostgreSQL  supports  building indexes without locking out writes. This
       method is invoked by  specifying  the  CONCURRENTLY  option  of  CREATE
       INDEX.   When this option is used, PostgreSQL must perform two scans of
       the table, and in addition it must wait for all  existing  transactions
       that  could  potentially  use  the index to terminate. Thus this method
       requires more  total  work  than  a  standard  index  build  and  takes
       significantly  longer  to  complete.  However,  since  it allows normal
       operations to continue while the index is built, this method is  useful
       for  adding  new  indexes  in  a production environment. Of course, the
       extra CPU and I/O load imposed by the index creation might  slow  other
       operations.

       In  a  concurrent  index  build, the index is actually entered into the
       system catalogs in one transaction, then the two table scans occur in a
       second  and  third transaction.  If a problem arises while scanning the
       table, such as a uniqueness violation in a  unique  index,  the  CREATE
       INDEX  command  will  fail  but leave behind an ‘‘invalid’’ index. This
       index will be  ignored  for  querying  purposes  because  it  might  be
       incomplete;  however it will still consume update overhead. The psql \d
       command will mark such an index as INVALID:

       postgres=# \d tab
              Table "public.tab"
        Column |  Type   | Modifiers
       --------+---------+-----------
        col    | integer |
       Indexes:
           "idx" btree (col) INVALID

       The recommended recovery method in such cases is to drop the index  and
       try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is
       to rebuild the index with REINDEX.  However,  since  REINDEX  does  not
       support concurrent builds, this option is unlikely to seem attractive.)

       Another caveat when building a unique index concurrently  is  that  the
       uniqueness   constraint   is   already  being  enforced  against  other
       transactions when  the  second  table  scan  begins.  This  means  that
       constraint  violations  could be reported in other queries prior to the
       index becoming available for use, or even  in  cases  where  the  index
       build  eventually  fails.  Also,  if a failure does occur in the second
       scan,  the  ‘‘invalid’’  index  continues  to  enforce  its  uniqueness
       constraint afterwards.

       Concurrent  builds  of  expression  indexes  and  partial  indexes  are
       supported.  Errors occurring in the  evaluation  of  these  expressions
       could  cause  behavior  similar  to  that  described  above  for unique
       constraint violations.

       Regular index builds permit other regular  index  builds  on  the  same
       table  to  occur  in  parallel, but only one concurrent index build can
       occur on a table at a time. In both cases, no  other  types  of  schema
       modification  on the table are allowed meanwhile. Another difference is
       that  a  regular  CREATE  INDEX  command  can  be  performed  within  a
       transaction block, but CREATE INDEX CONCURRENTLY cannot.

NOTES

       See  in  the  documentation  for  information about when indexes can be
       used, when they are not used, and in which particular  situations  they
       can be useful.

       Currently,  only  the  B-tree,  GiST  and  GIN  index  methods  support
       multicolumn indexes. Up to 32  fields  can  be  specified  by  default.
       (This  limit  can  be  altered  when  building PostgreSQL.) Only B-tree
       currently supports unique indexes.

       An operator class can be specified for each column  of  an  index.  The
       operator  class  identifies  the  operators to be used by the index for
       that column. For example, a B-tree index on  four-byte  integers  would
       use  the  int4_ops  class;  this  operator  class  includes  comparison
       functions for four-byte integers.  In  practice  the  default  operator
       class  for the column’s data type is usually sufficient. The main point
       of having operator classes is that for some data types, there could  be
       more than one meaningful ordering. For example, we might want to sort a
       complex-number data type either by absolute value or by real  part.  We
       could  do  this  by defining two operator classes for the data type and
       then selecting the proper class when making an index. More  information
       about  operator  classes  is  in  in  the  documentation  and in in the
       documentation.

       For index methods that support ordered scans (currently, only  B-tree),
       the  optional  clauses ASC, DESC, NULLS FIRST, and/or NULLS LAST can be
       specified to reverse the normal sort direction of the index.  Since  an
       ordered  index  can  be  scanned  either forward or backward, it is not
       normally useful to create  a  single-column  DESC  index  —  that  sort
       ordering  is already available with a regular index. The value of these
       options is that multicolumn indexes can be created that match the  sort
       ordering  requested by a mixed-ordering query, such as SELECT ... ORDER
       BY x ASC, y DESC. The NULLS options are useful if you need  to  support
       ‘‘nulls  sort  low’’  behavior,  rather  than  the default ‘‘nulls sort
       high’’, in queries that depend on indexes to avoid sorting steps.

       For most index methods, the speed of creating an index is dependent  on
       the  setting  of  maintenance_work_mem.   Larger values will reduce the
       time needed for index creation, so long as you  don’t  make  it  larger
       than  the  amount  of  memory  really  available, which would drive the
       machine   into   swapping.   For   hash   indexes,   the    value    of
       effective_cache_size   is   also   relevant  to  index  creation  time:
       PostgreSQL will use one of two different hash  index  creation  methods
       depending  on  whether  the  estimated  index size is more or less than
       effective_cache_size.  For best results, make sure that this  parameter
       is also set to something reflective of available memory, and be careful
       that the sum of maintenance_work_mem and effective_cache_size  is  less
       than the machine’s RAM less whatever space is needed by other programs.

       Use DROP INDEX [drop_index(7)] to remove an index.

       Prior releases of PostgreSQL also had  an  R-tree  index  method.  This
       method  has  been removed because it had no significant advantages over
       the GiST method.  If  USING  rtree  is  specified,  CREATE  INDEX  will
       interpret  it as USING gist, to simplify conversion of old databases to
       GiST.

EXAMPLES

       To create a B-tree index on the column title in the table films:

       CREATE UNIQUE INDEX title_idx ON films (title);

       To create an index on the expression lower(title),  allowing  efficient
       case-insensitive searches:

       CREATE INDEX lower_title_idx ON films ((lower(title)));

       To create an index with non-default sort ordering of nulls:

       CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

       To create an index with non-default fill factor:

       CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

       To create a GIN index with fast updates disabled:

       CREATE INDEX gin_idx ON documents_table (locations) WITH (fastupdate = off);

       To  create  an index on the column code in the table films and have the
       index reside in the tablespace indexspace:

       CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

       To create an index without locking out writes to the table:

       CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

COMPATIBILITY

       CREATE  INDEX  is  a  PostgreSQL  language  extension.  There  are   no
       provisions for indexes in the SQL standard.

SEE ALSO

       ALTER INDEX [alter_index(7)], DROP INDEX [drop_index(7)]