Indexes

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index: CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_method ] ( column [ ops_name ] [, ...] ) [ WHERE predicate ] The index_name can be chosen freely.
When the WHERE clause is present, a partial index is created.

Example:

CREATE TABLE names ( id integer, content varchar(100) ); and the application requires a lot of queries of the form: SELECT content FROM names WHERE id = constant; Ordinarily, the system would have to scan the entire names table row by row to find all matching entries.

The following command would be used to create the index on the id column:

CREATE INDEX names_id_index ON names (id); The name names_id_index can be chosen freely.

To remove an index, use the DROP INDEX command. Indexes can be added to and removed from tables at any time.