Online Documentation for SQL Manager for PostgreSQL

Editing index definition


Use the Index tab of Index Editor to create/edit an index on a specified table or materialized view, and specify index properties.

 

Name

Enter a name for the new index, or modify the name of the index being edited.

 

For table or materialized view

The drop-down list of tables and materialized views allows you to select the object to be indexed (available on creating a new index).

 

Index type

RadioButton Primary key - this selection indicates that the primary key index is created.

 

RadioButton Unique key - this selection indicates that the unique key index is created.

 

RadioButton Unique index - makes index unique, causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added.

 

RadioButton Index - this selection specifies a regular, non-unique index.

 

RadioButton Exclusion - this selection specifies that the exclusion constraint is created. It ensures that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

 

CheckBox Mark for table cluster

This option selects the index as default for future CLUSTER operations. It does not actually re-cluster the table (ALTER TABLE ... CLUSTER ON ...).

 

Deferrable

If Primary or Unique key is selected as Index type, this section appears. It controls whether the index can be deferred. A constraint that is not deferrable will be checked immediately after every command.

 

CheckBox Deferrable

Check this option to defer the index.

 

Check Time

If a constraint is deferrable, this option specifies the default time to check the constraint:

Immediate - it is checked after each statement.

Deferred - it is checked only at the end of the transaction.

 

 

Index Editor - Editing index definition

 

 

CheckBox Use default fillfactor.

Check this option to set fillfactor value to 100.

 

Fillfactor

Specify the custom fillfactor value in this field.

Note: To set this value you need to uncheck CheckBox Use default fillfactor option.

 

CheckBox Don't lock table on creation

Use this option to build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table.

 

Tablespace

Use the drop-down list to specify the index storage (a tablespace).

 

Index method

Select the index type. PostgreSQL provides several index methods: B-tree, R-tree, hash, GiST (Generalized Index Search Trees), SP-GiST (space-partitioned GiST for Postgres 9.2 and higher), brin (Block Range INdex for Postgres 9.5 and higher).

The B-tree index method is an implementation of Lehman-Yao high-concurrency B-trees.

The R-tree index method implements standard R-trees using Guttman's quadratic split algorithm.

The hash index method is an implementation of Litwin's linear hashing.

 

Condition for partial index

A partial index is an index that contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table. If you wish to create partial index, enter conditional expression in this edit box.

 

Index keys

To include field(s) in the index, you need to move it from the Available Fields list to the Index Keys list. Use the Items - Move to Selected Items - Move all to Selected Items - Move all to Available Items - Move to Available buttons or drag-and-drop operations to move the fields from one list to another.

 

Operator Class

This column allows you to specify an operator class for each column of the index. Use the drop-down list to select the operator class identifying the operators to be used by the index for the column.

 

Selected index key

Specifies an index key name or an expression based on one or more index key columns of the table. This feature is useful to obtain fast access to tables based on the results of computations.