CREATE INDEX - Constructs a secondary index


SYNOPSIS

       CREATE [ UNIQUE ] INDEX index_name ON table
           [ USING acc_name ] ( column [ ops_name ] [, ...] )
       CREATE [ UNIQUE ] INDEX index_name ON table
           [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )

   INPUTS
       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.

       index_name
              The name of the index to be created.

       table  The name of the table to be indexed.

       acc_name
              The name of the access method to be  used  for  the
              index.  The  default access method is BTREE.  Post­
              gres provides three access methods for indexes:

              BTREE  an implementation of Lehman-Yao high-concur­
                     rency btrees.

              RTREE  implements  standard  rtrees using Guttman's
                     quadratic split algorithm.

              HASH   an implementation of Litwin's  linear  hash­
                     ing.

       column The name of a column of the table.

       ops_name
              An   associated   operator  class.  See  below  for
              details.

       func_name
              A function, which  returns  a  value  that  can  be
              indexed.

   OUTPUTS
       CREATE The  message  returned if the index is successfully
              created.

              This error occurs if it is impossible to create the
              index.


DESCRIPTION

       CREATE  INDEX constructs an index index_name on the speci­
       fied table.

              Tip: Indexes are primarily used to enhance database
              performance.   But inappropriate use will result in
              slower performance.

       In the first syntax shown above, the key field(s) for  the
       index  are specified as column names.  Multiple fields can
       be specified if the index access  method  supports  multi-
       column indexes.

       In  the  second syntax shown above, an index is defined on
       the result of a user-specified function func_name  applied
       to  one  or  more  columns of a single table.  These func­
       tional indices can be used to obtain fast access  to  data
       based on operators that would normally require some trans­
       formation to apply them to the base data.

       Postgres provides btree, rtree and hash access methods for
       indices.  The  btree access method is an implementation of
       Lehman-Yao  high-concurrency  btrees.  The  rtree   access
       method   implements   standard   rtrees   using  Guttman's
       quadratic split algorithm.  The hash access method  is  an
       implementation  of Litwin's linear hashing. We mention the
       algorithms used solely  to  indicate  that  all  of  these
       access  methods  are  fully  dynamic and do not have to be
       optimized periodically (as is the case with, for  example,
       static hash access methods).

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

   NOTES
       The  Postgres  query optimizer will consider using a btree
       index whenever an indexed attribute is involved in a  com­
       parison using one of: <, <=, =, >=, >

       The  Postgres query optimizer will consider using an rtree
       index whenever an indexed attribute is involved in a  com­
       parison using one of: <<, &<, &>, >>, @, ~=, &&

       The  Postgres  query  optimizer will consider using a hash
       index whenever an indexed attribute is involved in a  com­
       parison using the = operator.

       column indexes. Up to 16 keys may be specified by  default
       (this limit can be altered when building Postgres).

       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 btree
       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 field'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.
       There are also some operator  classes  with  special  pur­
       poses:

       · The operator classes box_ops and bigbox_ops both support
         rtree indices on the  box  data  type.   The  difference
         between  them  is that bigbox_ops scales box coordinates
         down, to avoid floating-point exceptions from doing mul­
         tiplication,  addition,  and  subtraction  on very large
         floating-point coordinates. If the field on  which  your
         rectangles  lie  is about 20,000 units square or larger,
         you should use bigbox_ops.

       The following query shows all defined operator classes:

       SELECT am.amname AS acc_name,
              opc.opcname AS ops_name,
              opr.oprname AS ops_comp
           FROM pg_am am, pg_amop amop,
                pg_opclass opc, pg_operator opr
           WHERE amop.amopid = am.oid AND
                 amop.amopclaid = opc.oid AND
                 amop.amopopr = opr.oid
           ORDER BY acc_name, ops_name, ops_comp


USAGE

       To create a btree index on the field title  in  the  table
       films:

       CREATE UNIQUE INDEX title_idx
           ON films (title);

   SQL92
       CREATE INDEX is a Postgres language extension.

       There is no CREATE INDEX command in SQL92.


Man(1) output converted with man2html