Database Reference
In-Depth Information
The single-column index
The single-column index is utilized when a table represents mostly a single
category of data, or queries span around only a single category in the table.
Normally, in a database design, tables represent a single category of data, so
generally a single-column (category) index is utilized. Its syntax is as follows:
CREATE INDEX index_name ON table_name (column);
Take a look at the following example:
warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id = 100;
count
-------
202
(1 row)
Time: 201.206 ms
In this example, the rows are required where
item_id
is
100
. If there is no index
deined, then the whole table will be scanned to ind the rows where
item_id
is
100
,
which is an expensive operation. If you look closely, only a single column is utilized
in the
WHERE
clause, thereby creating an index on a single column, which is
item_id
in the case of the preceding query. This optimizes that query.
Now, consider the following example:
warehouse_db=# CREATE INDEX item_index ON item (item_id);
The result can be seen using the following statement:
warehouse_db=# \d item;
Table "item"
Column | Type | Modifiers
------------+--------------------+-----------
item_id | integer | not null
item_name | text |
item_price | numeric |
item_data | text |
Indexes:
"item_index" btree (item_id)
Now, we have created a B-tree index,
item_index
, on a table item's
item_id
column,
so now we try the same
SELECT
query again and see how much time it takes after
index creation.
