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:
The name of the index, which must be unique within the table.
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.
The name of the method to be used for the index. Choices are btree,
hash, rtree, and gist. The default method is btree.
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.
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.
The constraint expression for a partial index.
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
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.
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.
Defines the UNIQUE constraint for the selected columns. I.e. the combination of the included
field values must be unique within the table;
An indicator that shows if the index is system and can't be modified by a user.
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;