This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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.
 
Continue to: