Working with Indexes - PostgreSQL Developer's Guide - page 63

Now, we will create a single-column index, try the same query in the following
manner, and note down the query execution time:

warehouse_db=# CREATE INDEX item_single_index ON item (item_id);

The result can be seen using the following statement:

warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id < 200 AND
item_price = 100;
count
-------
202
(1 row)
Time: 1604.328 ms

Finally, create a multicolumn index on the table, try the same query in the following
manner, and note down the timing of the query:

warehouse_db=# CREATE INDEX item_multi_index ON item (item_id,
item_price);

The result can be seen using the following statement:

warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id < 200 AND
item_price = 100;
count
-------
202
(1 row)
Time: 331.295 ms

The query times are as follows:

Without index Single-column index Multicolumn index

1675.783 ms 1604.328 ms 331.295 ms

We can observe the difference in the execution time of the preceding queries. The
query without an index took 1675.783 ms , the query with a single-column index
ran slightly faster and took 1604.328 ms , and the query with a multicolumn index
ran much faster and took only 331.295 ms . Therefore, creating a single-column or
multicolumn index depends on the nature of the queries used, so selecting a proper
index can give a real performance boost.

By default, an index is created in ascending order; to create an
index in descending order, use DESC after the column name.