An index is not limited to one per table. It can be defined for more than one column in a table. For instance, the shipped orders table follows the specifications listed in the chart below:

Table 5-4. Shipped Orders

Col Name Description Data type
invoice_num the number which identifies the invoice integer
cust_id customer identification number integer
subtotal the total price of the order, include shipping and handling charges money
ship_date the date the order was shipped on date

The Book Town store typically uses these queries on the shipped orders table to find out the subtotal for orders placed by customers: 

   SELECT subtotal FROM shipped_orders 
    WHERE invoice_num = 272 AND cust_id = 28;

If a company had a similar situation to the Book Town store and frequently made queries which involves a comparison of two columns using the AND operator, then we can create a multiple column index for these transactions. To create the index, use:

Example 5-5. Creating an Index 

   CREATE INDEX custid_ordnum_index 
       ON shipped_orders (cust_id, invoice_num);

The query optimizer will decide to use a multi-column index when the query involves only the first n consecutive columns in the index. For instance, an index with column x, y, and z can be used in queries which involve these combinations:

  • all of x, y, and z

  • both x and y

  • only x

If a query was used involving x and z, then the optimizer might choose to use an index for only x and treat z like an ordinary unindexed column.

Some rules about indices that you should keep in mind are:

  • Multiple column indices can only be implemented using the B-tree index type.

  • Multiple column indices can only be used with queries joining the indexed columns with an AND operator.

    For instance, the following does not use a multi-column index to perform the search:

    Example 5-6. Using a Multi-Column Index 

     
  SELECT subtotal FROM shipped_orders 
   WHERE invoice_num = 272 OR cust_id= 28;

  • The number of columns a multi-column index can have is limited to 16. You can change this limit when building PostgreSQL. It is in the config.h file.

  • Multiple column indices should be used whenever necessary. Usually, a single column index is adequate to perform a search which saves space and time. Indices with more than three columns can cause problems and are unnecessary.