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


       CREATE INDEX - define a new index


       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 ]


       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

       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

       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.


       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.

              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.

              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.

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

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

       DESC   Specifies descending sort order.

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

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

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

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

              The constraint expression for a partial index.

       The  optional  WITH  clause specifies storage parameters for the index.
       Each index method has its own set of allowed storage parameters. The B-
       tree, hash and GiST index methods all accept a single parameter:

              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  (adding
              new   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 integer 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:

              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

       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

       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 report such an index as INVALID:

       postgres=# \d tab
              Table ""
        Column |  Type   | Modifiers
        col    | integer |
           "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.


       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

       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 modify the sort ordering 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


       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 USING gin (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 a GiST index on a point attribute so that we can efficiently
       use box operators on the result of the conversion function:

       CREATE INDEX pointloc
           ON points USING gist (box(location,location));
       SELECT * FROM points
           WHERE box(location,location) && '(0,0),(1,1)'::box;

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

       CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);


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


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