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.
Enter a name for the new index, or modify the name of the index being edited.
For table or materialized view
Primary key - this selection indicates that the primary key index is created.
Unique key - this selection indicates that the unique key index is created.
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.
Index - this selection specifies a regular, non-unique index.
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.
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 ...).
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.
Check this option to defer the index.
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.
Use default fillfactor.
Check this option to set fillfactor value to 100.
Specify the custom fillfactor value in this field.
Note: To set this value you need to uncheck Use default fillfactor option.
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.
Use the drop-down list to specify the index storage (a tablespace).
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.
To include field(s) in the index, you need to move it from the Available Fields list to the Index Keys list. Use the buttons or drag-and-drop operations to move the fields from one list to another.
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.