Database Reference
In-Depth Information
How to create an index
The CREATE INDEX command is used to create an index; the basic syntax of creating
an index on a table is as follows:
CREATE INDEX index_name ON table_name (column_name);
Here is an example of creating an index on the item_id column of the item table:
warehouse_db=# CREATE INDEX item_idx ON item (item_id);
The result can be seen using the following statement:
warehouse_db=# \di item_idx;
List of relations
Schema | Name | Type | Owner | Table
--------+----------+-------+----------+-------
public | item_idx | index | postgres | item
(1 row)
The index name is optional; if the index name is not speciied, PostgreSQL generates
the index name using the table name and the column name. In the preceding
example, we have speciied the index name. In the following example, PostgreSQL
generates the index name using the table name and column name, which is item_
item_id_idx :
warehouse_db=# CREATE INDEX ON item (item_id);
CREATE INDEX
The result can be seen using the following statement:
warehouse_db=# \di item_item_id_idx;
List of relations
Schema | Name | Type | Owner | Table
--------+------------------+-------+----------+-------
public | item_item_id_idx | index | postgres | item
(1 row)
Creating an index on a large table can take a long time; for example, in the preceding
example, the query has to scan all the records and generate the index data. On a
larger table, this process can take time.
 
Search WWH ::




Custom Search