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.
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.
|Index access method to be used. Possible values are
brin. Note that some fields will be disabled in the form depending on the access method chosen.
|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.
|Creates the index without any locks that prevent concurrent insert, delete, or updates on the table.
|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.
|Enables fast update techniques for GIN indexes.
|Enables the buffering technique for GIST indexes.
|The constraint expression for a partial index.
Index elements can be configured using a table column that will receive the index or a SQL expression. When all index elements are configured they will form the DDL command to create the index in the database. Their attributes are described in following the table.
|Column that composes the current index element. This attribute is mutually exclusive with
|SQL Expression that composes the current index element. This attribute is mutually exclusive with
|Collation associated with the index element.
|Operator class associated with the index element.
|Enables the ordering option in the index element.
|Indicates ascending ordering when
Sorting is checked.
|Indicates descending ordering when
Sorting is checked.
|Indicates that null values must be ordered before non-nulls.
Non-key columns can be used to make effective use of index-only scans, by choosing to create a covering index, which is an index specifically designed to include the columns needed by a particular type of query that is run frequently. Since queries typically need to retrieve more columns than just the ones they search on, PostgreSQL allows one to create an index in which some columns are just "payload" and are not part of the search key. This is done by adding an
INCLUDE clause listing the extra columns. In pgModeler, this can be achieved by using the tab Include columns on the index form, as shown below.
|List of columns owned by the table or view that can be used as non-key.
|Holds the columns currently configured as non-key columns of the index.
Index only scan & Covering indexes