Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

CREATE INDEX

Name

CREATE INDEX -- Places an index on a table.

Synopsis

CREATE [ UNIQUE ] INDEX 
index_name
 ON 
table

       [ USING 
method
 ] ( 
column
 [ 
op_class
 ] [, ...] )
CREATE [ UNIQUE ] INDEX 
index_name
 ON 
table

       [ USING 
method
 ] ( 
func_name
 ( 
column
 [, ... ] ) [ 
op_class
 ] )

Parameters

UNIQUE

The optional UNIQUE keyword. When used, this causes the database to check for, and prevent, duplicate values within the column (or combined columns) it is placed upon. This check will occur both when the index is created and each time data is added to the table. PostgreSQL will then generate an error whenever an INSERT or UPDATE request is made that would place duplicate data within the index, and the command will fail.

index_name

The name for the new index.

table

The name of the table you are placing the index on.

method

The type of indexing method you wish to use for the index. There are three methods available to choose from, the default being btree:

btree

The PostgreSQL implementation of Lehman-Yao high-concurrency B-trees.

rtree

The PostgreSQL implementation of standard R-trees using Guttman's quadratic split algorithm.

hash

The PostgreSQL implementation of Litwin's linear hashing.

column

The name of the column (or comma-delimited list of columns) on which to place the index.

op_class

The optionally specified associated operator class. For most users, this should not be specified.

func_name

The name of a function you wish CREATE INDEX to use on the specified columns (rather than on the data values literally in those columns). The specified function must return a valid value that can be indexed (e.g., not a set of values).

Results

CREATE

The message returned when an index is created successfully.

ERROR: Cannot create index: ' index_name ' already exists

The error returned if an index with the name you specified already exists.

ERROR: DefineIndex: attribute " column " not found

The error returned if the specified column does not exist in the specified table to index.

ERROR: DefineIndex: relation " table " not found

The error returned if the specified table does not exist in the connected database.

Description

Use CREATE INDEX to build an optimization index on a specified table, based on one or more of its columns. Remember that while indices are designed to improve the performance and effectiveness of your database, using them on tables whose cost of index maintenance outweighs the practical benefit actually decreases overall performance.

Column index

You may create an index specifying a list of one or more table columns. This is the "traditional" index type. It may be used by queries that directly reference the indexed columns in a WHERE clause. Note that R-tree and Hash indices may only index one column, though B-tree indices can index up to sixteen columns.

Functional index

An alternate type of index is one called a functional index . A functional index is an index based on the returned value of a function applied to one or more columns. Such an index is useful for queries that use the same function in a WHERE clause frequently.

For example, if you have a query that always references upper(last_name) in its WHERE clause, you could optimized that query by creating a functional index on upper(last_name).

Operators and operator classes

The PostgreSQL query optimizer will use different indices for different operators used in a comparison. It will choose which type of index to used based on the pre-defined list shown in Table 14-1.

Table 14-1. Operator/index correspondence

Index Operator

B-tree

<, <=, >=, >

R-tree

<<, &<, &>, >>, @, ~=, &&

Hash

=

You can optionally specify an operator class for each column on which an index is placed. This is done by setting the optional op_class parameter to the class of operator you intend to use. This option only exists because, in some circumstances, there can be more than one meaningful way to order data. The default indexing method is generally sufficient for most users, however, and this option is best left unused unless you are creating your own custom types and operators.

Examples

The following example creates a unique index on the id column of the employees table:

booktown=# 
CREATE UNIQUE INDEX employee_id_idx

booktown-# 
              ON employees (id);

CREATE

The next example creates a functional index on the last_name column of the employees table, using the upper() function:

booktown=# 
CREATE INDEX employee_upper_name_idx

booktown-# 
       ON employees (upper(last_name));

CREATE
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire