Database Reference
In-Depth Information
Creating an index on a table reduces the
SELECT
query time drastically, as shown in
the following output:
warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id = 100;
count
-------
202
(1 row)
Time: 1.140 ms
There is an obvious timing difference with and without the index. The same query
without the index took approximately
200 ms
to execute, and the query after
creating the index took approximately
1 ms
.
The
\d table_name
query is used to describe the table.
The
\timing
query is used to display the query time.
The multicolumn index
In some cases, there are tables in a database that involve multiple categories of
data. In such cases, a single-column index might not give good performance. In this
situation, the multicolumn index is needed. PostgreSQL supports the multicolumn
index. Its syntax is as follows:
CREATE INDEX index_name ON table_name (column1, column2);
The multicolumn index, which involves multiple columns in a query, helps optimize
the queries; let's look at an example.
In this example, we will get the total number of records whose
item_id
is less than
200
and
item_price
is also
100
; irst, we try the query without an index on the table
and note the query time in the following manner:
warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id < 200 AND
item_price = 100;
count
-------
202
(1 row)
Time: 1675.783 ms
