3.4. Indexes

Indexes are applied in a set of columns and are primarily used to enhance database performance. This kind of object is composed of basic attributes and a set of elements with their own attributes which reunited define the index as a whole. In the database model, pgModeler includes indexes at the bottom of tables in a portion called the extended attributes section. The image below shows an example of how an index is displayed in a table.

3.4.1. Index attributes

Basic attributes of the index like indexing mode (also called access method), fill factor, predicate, and others are configured in the tab Attributes in the index editing form. Their meaning and accepted values are detailed in the table below.

Attribute Description
Indexing Index access method to be used. Possible values are btree, gin, gist, hash, spgist and brin. Note that some fields will be disabled in the form depending on the access method chosen.
Fill Factor This attribute is related to how much (in a percentage from 10 to 100) the index pages are packed (in terms of physical space usage). This means that if your table is intended to have lots of updates you should use a small fill factor, on the other hand, static tables or least updated ones can use a greater fill factor to save disk space. Details about the fill factor attribute can be seen on PostgreSQL documentation.
Concurrent Creates the index without any locks that prevent concurrent insert, delete, or updates on the table.
Unique Indicates that duplicate values must be checked when the index is created on the table (if data already exist) and each time data is added. In this case, errors will be raised when inserts or updates would cause data duplication.
Fast update Enables fast update techniques for GIN indexes.
Buffering Enables buffering technique for GIST indexes.
Predicate The constraint expression for a partial index.

3.4.2. Index elements

Index elements can be configured using a column of the table which will receive the index or a SQL expression. Reunited all the elements configured will form the DDL command to create the index in the database. Their attributes are described in following the table.

Attribute Description
Column Column that composes the current index element. This attribute is mutually exclusive with Expression.
Expression SQL Expression that composes the current index element. This attribute is mutually exclusive with Column.
Collation Collation associated with the index element.
Operator Class Operator class associated with the index element.
Sorting Enables the ordering on the index element.
Ascending Indicates ascending ordering when Sorting is checked.
Descending Indicates descending ordering when Sorting is checked.
Nulls first Indicates that null values must be ordered before non-nulls.

Mar 8, 2021 at 14:16