3.14. Indexes

In the object hierarchy under every table are Indexes. In order to facilitate faster searching over a table, PostgreSQL allows creation of indexes on a table, which helps the database engine find relevant data quickly.

Indexes can be added in two ways, depending on what type of index you want. The first is by selecting CREATE INDEX from the Indexes context menu. The other way is by selecting CREATE FUNCTIONAL INDEX. Once an index has been created, it can be altered, reindexed, commented on, and dropped. This section describes how to create, manage, and drop an index with Administrator.

In the following example, you will create two indexes. The first is a BTree index called customer_name_index on the table customers.

1. An index is an object under a table, so to create an index, first expand the web_store database (click the [O-] beside the database name), then web_store_schema, then the customers table.

2. Right-click on Indexes and select CREATE INDEX.

3. The first field is the name for the index. Type: customer_name_index

4. Next is the Unique Index checkbox. If Unique Index is checked, each time a value is entered in the selected columns, PostgreSQL ensures that there is no row in the table with the same corresponding values. For this example, leave Unique Index unchecked.

5. In the Access Method, specify the type of index to be used. For this example, select BTree.

6. Next is the WHERE Predicate. This optional field enables you to create a partial clause for the index. For this example, leave WHERE Predicate blank.

7. The Index Columns box specifies columns to which the index applied. You can declare an index on multiple columns by selecting the required columns from the Columns combo-box one by one and clicking Add Index Definition to move them to the Current Index Definitions listbox at the bottom. For this example, add the column name.

8. The last combo-box allows you to select an operator class. This selection is optional and can be specified for columns as needed. To associate a column with an operator class, select that column and the operator class before clicking the Add Index Definition button. For this example, do not specify an Operator Class.

9. Click on the Add Index Definition button to add this column to the listbox below.

   

   Figure 3-47. The CREATE INDEX Dialog

10. Click OK to create the index.

For the second index, you will create a functional index named customer_id_index.

1. An index is an object under a table, so to create an index, (click the [O-] beside the database name), then web_store_schema, then the customers table.

2. Right-click on Indexes and select CREATE FUNCTIONAL INDEX.

3. The first field is the name for the index. Type: customer_id_index

4. Next is the Unique Index checkbox. If Unique Index is checked, the index will be unique. This means that each time a value is entered in the selected columns, PostgreSQL ensures that there is no row in the table with the same corresponding values. For this example, leave Unique Index unchecked.

5. In the Access Method, specify the type of index to be used. For this example, select: BTree.

6. Next is the WHERE Predicate. This optional field enables you to create a partial clause for the index. For this example, leave WHERE Predicate blank.

7. Next is the Function combo-box, which specifies the function to apply to the column for this index (see the PostgreSQL 7.3 Reference Manual for more information). For this example, select the function: abs(pg_catalog.int4)

8. Next is the optional Operator Class selection. For this example, do not select an operator class.

9. Last is the column selection, which allows you to specify which columns this index is on. To specify the columns, select as many as needed in the left listbox and click the Add >> button to move them to the right listbox. For this example, click on the column customer_id on the left, and click Add >> to move it to the right.

   

   Figure 3-48. The CREATE FUNCTIONAL INDEX Dialog

10. Click OK to create the index.

Note that the indexes customer_name_index and customer_id_index have been added under the Indexes node in the Tree View.

Administrator also enables you to perform the following operations on existing indexes:

Refer to the PostgreSQL 7.3 Reference Manual for more information on functional indexes, indexes with partial clauses, and types.