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

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 ...).

 

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.

 

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.

 

 

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.

 

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 Use default fillfactor option.

 

B-tree index method

Use default deduplicate items

This option is available for B-tree method only. It controls usage of the B-tree deduplication technique for work optimization. If enabled, it applies the default value.

 

Deduplicate_items

Sets the value for inserts from triggering deduplication.

 

GIN index method

Use default fast update

This option is available for Gin method only. Enable this setting to control the usage of the fast update technique. If enabled, it applies the value set below by default.

 

Fastupdate

Set this option ON to improve update speed or OFF otherwise.

 

Use default gin_pending_list_limit

This option is available for Gin method only. Enable this setting to control the limit to move entries  to the main GIN data structure. If enabled, it applies the default value (4Mb).

 

Gin_pending_list_limit

This value is specified in kilobytes. If the pending list becomes larger than this value, the entries are moved to the main GIN data structure using the same bulk insert techniques used during initial index creation.

 

GIST index method

Use default buffering

This option is available for Gist method only. It controls whether to use the buffered build technique to build the index. If enabled, it applies the value set by default.

 

Buffering

If it's OFF the buffering is disabled, ON it is enabled, and with AUTO it is initially disabled, but is turned on on-the-fly once the index size reaches effective_cache_size.

 

BRIN index method

Use default pages_per_range

This option is available for Brin method only. It controls whether the default value must be applied.

 

Pages_per_range

It defines the number of table blocks that make up one block range for each entry of an index.

 

Use default autosummarize

This option is available for Brin method only. It applies the default value fo autosummarizing.

 

Autosummarize

Enables summarization is queued for the previous page range whenever an insertion is detected on the next one. Whenever autovacuum runs in that database, summarization will occur for all unsummarized page ranges that have been filled.

 

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 column(s) in the index, you need to move it from the Available Columns list to the Index Keys list. Use the buttons or drag-and-drop operations to move the columns 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.