Index Dialog v9
Use the Index
dialog to create an index on a specified table or materialized view.
The Index
dialog organizes the development of a index through the following dialog tabs: General
and Definition
. The SQL
tab displays the SQL code generated by dialog selections.
Use the fields in the General
tab to identify the index:
- Use the
Name
field to add a descriptive name for the index. The name will be displayed in theBrowser
tree control. - Use the drop-down listbox next to
Tablespace
to select the tablespace in which the index will reside. - Store notes about the index in the
Comment
field.
Click the Definition
tab to continue.
Use the fields in the Definition
tab to define the index:
Use the drop-down listbox next to
Access Method
to select an index type:- Select
btree
to create a B-tree index. A B-tree index may improve performance when managing equality and range queries on data that can be sorted into some ordering (the default). - Select
hash
to create a hash index. A hash index may improve performance when managing simple equality comparisons. - Select
gist
to create a GiST index. A GiST index may improve performance when managing two-dimensional geometric data types and nearest-neighbor searches - Select
gin
to create a GIN index. A GIN index may performance when managing values with more than one key. - Select
spgist
to create a space-partitioned GiST index. A SP-GiST index may improve performance when managing non-balanced data structures. - Select
brin
to create a BRIN index. A BRIN index may improve performance when managing minimum and maximum values and ranges.
- Select
Use the
Fill Factor
field to specify a fill factor for the index. The fill factor specifies how full the selected method will try to fill each index page.Move the
Unique?
switch to theYes
position to check for duplicate values in the table when the index is created and when data is added. The default isNo
.Move the
Clustered?
switch to theYes
position to instruct the server to cluster the table.Move the
Concurrent build?
switch to theYes
position to build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table.Use the
Constraint
field to provide a constraint expression; a constraint expression limits the entries in the index to those rows that satisfy the constraint.
Use the context-sensitive fields in the Columns
panel to specify which column(s) the index queries. Click the Add
icon (+) to add a column:
Use the drop-down listbox in
Column
field to select the name of the column from the table.If enabled, use the drop-down listbox to select an available
Operator class
to specify the type of action performed on the column.If enabled, move the
Sort order
switch to specify the sort order:- Select
ASC
to specify an ascending sort order (the default); - Select
DESC
to specify a descending sort order.
- Select
If enabled, move the
Nulls
switch to specify the sort order of nulls:- Select
First
to specify nulls sort before non-nulls; - Select
Last
to specify nulls sort after non-nulls (the default).
- Select
Use the drop-down listbox in the
Collation
field to select a collation to use for the index.
Use Include columns
field to specify columns for INCLUDE
clause of the index. This option is available in Postgres 11 and later.
Click the SQL
tab to continue.
Your entries in the Index
dialog generate a SQL command (see an example below). Use the SQL
tab for review; revisit or switch tabs to make any changes to the SQL command.
Example
The following is an example of the sql command generated by user selections in the Index
dialog:
The example shown demonstrates creating an index named dist_codes
that indexes the values in the code
column of the distributors
table.
- Click the
Info
button (i) to access online help. View context-sensitive help in theTabbed browser
, where a new tab displays the PostgreSQL core documentation. - Click the
Save
button to save work. - Click the
Cancel
button to exit without saving work. - Click the
Reset
button to restore configuration parameters.