Database Reference
In-Depth Information
An index requires additional disk space, so be careful when creating
an index. Also, insert/delete/update incurs more processing to
maintain the index data, and the type of index has a different impact
on performance. For instance, B-tree indexes require tree rebalancing,
which is quite heavy in terms of computational cost.
Here is the complete syntax for index creation:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ]
[ name ] ON table [ USING method ]
( { column | ( expression ) }
[ COLLATE collation ] [ opclass ] [ ASC | DESC ]
[ NULLS { FIRST | LAST } ] [, ...])
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
Details on the complete syntax for index creation can be found at the
oficial PostgreSQL documentation available online at the following link:
http://www.postgresql.org/docs/9.4/static/sql-
createindex.html
.
How to drop an index
The
DROP INDEX
command is used to drop an existing index. Its basic syntax
is as follows:
warehouse_db=# DROP INDEX index_name;
Dropping an index will not affect the rows of the table, but be careful since it
will affect the performance of the database.
Types of indexes
A PostgreSQL database supports the single-column index, multicolumn index,
partial index, unique index, expression index, implicit index, and concurrent index.
