Create an index
CREATE INDEX statement to create a B-tree index.
CREATE INDEX gender_idx ON employee (gender);
CREATE INDEX title_bmp_idx ON films USING bitmap (title);
CREATE INDEX lineitem_idx ON lineitem USING gin(to_tsvector('english', l_comment));
CREATE INDEX arrayt_idx ON arrayt USING gin(intarray);
CREATE INDEX customer_idx ON customer USING gist(to_tsvector('english', c_comment));
Rebuild an index
REINDEX INDEX statement to rebuild an index.
REINDEX INDEX my_index;
REINDEX TABLE my_table;
Delete an index from a table
DROP INDEX statement to delete an index.
DROP INDEX title_idx;
Principles for indexing
- Create an index based on query loads.
A query load-based index helps increase the performance of queries for single data records or small data sets. Such queries include online transaction processing (OLTP) queries.
- Create an index on a compressed table.
On a compressed append-optimized table, an index helps increase the performance of queries for single rows because only the involved rows are decompressed.
- Do not create an index on a frequently updated column.
If you create an index on a frequently updated column, the amount of data that needs to be read and written for column updates increases.
- Create a B-tree index that has a high selectivity.
For example, if a table has 1,000 rows and you create an index on a column that has 800 distinct values, the selectivity of the index is 0.8. The selectivity of an index created on a column that has the same value in all rows is always 1.0.
- Create a bitmap index that has a low selectivity. Bitmap indexes perform best for columns that have 100 to 100,000 distinct values.
- Create an index on a column that is frequently used for joins with other tables.
For example, create an index on a column used as the foreign key. This enables the query optimizer to use more join methods and therefore increases join performance.
- Create an index on a column that is frequently referenced in predicates.
For example, create an index on a column that is frequently referenced in WHERE clauses.
- Do not create redundant indexes.
For example, if an index is created on more than one column, indexes with the same leading column are redundant.
- Delete indexes before you load data.
If you want to load a large volume of data into a table, we recommend that you delete all indexes on the data, load the data, and then rebuild the indexes on the table. This is faster than updating indexes.
- Test and compare the performance of queries with and without indexes used.
Do not create indexes unless the performance of queries for indexed columns increases.
- Execute the ANALYZE statement after you create or update an index.
For more information, visit CREATE INDEX in Pivotal Greenplum documentation.