Index Editor

The Index Editor is placed within the Table Editor dialog. It allows you to modify the list of table indexes as well as index properties.

The main element of the editor is the index list, which displays all indexes available within the table. The columns of the list allow you to modify the properties of the selected index.

These properties are:

Index Name

The name of the index, which must be unique within the table.

Attributes

The names of a columns of the table or expressions based on one or more columns of the table. The expression must not be written with surrounding parentheses, because Designer will add them automatically.

Method

The name of the method to be used for the index. Choices are btree, hash, rtree, and gist. The default method is btree.

Sort Order

Specifies index attributes sort order.

Since an ordered index can be scanned either forward or backward, it is not normally useful to create a single-column DESC index — that sort ordering is already available with a regular index. The value of these options is that multicolumn indexes can be created that match the sort ordering requested by a mixed-ordering query, such as SELECT ... ORDER BY x ASC, y DESC.

NULLs Order

Specifies that nulls sort before or after non-nulls.

The NULLs Order options are useful if you need to support “nulls sort low” behavior, rather than the default “nulls sort high”, in queries that depend on indexes to avoid sorting steps.

Predicate

The constraint expression for a partial index.

Tablespace

The tablespace in which to create the index. If not specified, default is used, or the database's default tablespace if server's parameter default_tablespace is an empty string.

Fast Update

This setting controls usage of the fast update technique for GIN indexes. Updating a GIN index tends to be slow because of the intrinsic nature of inverted indexes: inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed value). As of PostgreSQL 8.4, GIN is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. See PostgreSQL manual for details.

Fillfactor

The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.

Unique

Defines the UNIQUE constraint for the selected columns. I.e. the combination of the included field values must be unique within the table;

System

An indicator that shows if the index is system and can't be modified by a user.

Comment

Specifies comment for index object.

The buttons under the list of indexes allows you to perform the following actions:

• Add - add a new index with the default properties to the end of the list;
• Duplicate - add a new index with the same properties as the selected index to the end of the list;
• Delete - remove the selected index from the list;
• Up/Down - move the selected index along the list.