CREATE INDEX constructs an index on the specified column(s) of the specified table. Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance).
PostgreSQL provides the index methods B-tree, hash, GiST, and GIN. Users can also define their own index methods, but that is fairly complicated.
- B-tree index
B-Tree is to handle sorted data for equation or range search, like:
< <= > >= =
CREATE INDEX cece_index ON cece_table (column)
- R-Tree index
R-Tree is suitable for two-dimensional data, like:
<< &< &> >> <<| &<| |&>
|>> @> <@ ~= &&
CREATE INDEX cece_index ON cece_table USING rtree (column)
- Hash index
Hash can only handle the “=”
CREATE INDEX cece_index ON cece_table USING hash (column)
- Gist index
There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are not mandatory for full text searching, but in cases where a column is searched on a regular basis, an index is usually desirable.
CREATE INDEX cece_index ON cece_table USING gist(column);
- Multicolumn index
CREATE INDEX cece_index ON cece_table (column1, column2);
SELECT * FROM cece_table WHERE column1 >0 AND column2 < 0;
- Combining Multiple Indexes
A single index scan can only use query clauses that use the index’s columns with operators of its operator class and are joined with AND. For example, given an index on (a, b) a query condition like WHERE a = 5 AND b = 6 could use the index, but a query like WHERE a = 5 OR b = 6 could not directly use the index.
- Unique index
Force index value unque
CREATE UNIQUE INDEX cece_index ON cece_table (column);
- Indexes on Expressions
CREATE INDEX cece_index ON cece_table (lower(col1));
SELECT * FROM test1 WHERE lower(col1) = 'value';
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
- Partial index
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful.
- Other index operations
-- Delete index
DROP cece_index
More details can be seen in Postgre SQL