3.4. Indexes

Indexes are applied to 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 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.

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.

123456789

Attributes
#	Field	Group box	Description
1	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.
2	Concurrent	Options	Creates the index without any locks that prevent concurrent insertion, deletion, or updates on the table.
3	Fast update	Options	Enables fast update techniques for GIN indexes.
4	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 the least updated ones can use a greater fill factor to save disk space. Details about the fill factor attribute can be found in the PostgreSQL documentation.
5	Unique	Options	Indicates that duplicate values must be checked when the index is created on the table (if data already exists) and each time data is added. In this case, errors will be raised when inserts or updates would cause data duplication.
6	Nulls not distinct	Options	Available from PostgreSQL 15. Applies to unique indexes: NULL values are treated as equal, so only one row with a NULL in the indexed columns is accepted (NULLS NOT DISTINCT). By default, nulls are considered distinct.
7	Fill factor value		The fill factor percentage (from 10 to 100). It is enabled when the check box Fill factor is checked.
8	Buffering	Options	Enables the buffering technique for GIST indexes.
9	Predicate		The constraint expression for a partial index.

Index elements

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 the following table.

1

Elements
#	Field	Description
1	Index elements	The elements of the index, each one a table column or a SQL expression with its operator class, collation, sorting, and nulls ordering. Each element is configured in the form described below.

Each element is configured in the following form:

123456789

Index element
#	Field	Description
1	Column	Column that composes the current index element. This attribute is mutually exclusive with Expression.
2	Sorting	Enables the ordering option in the index element.
3	Element column	The column used as the element, when the option Column is selected.
4	Sorting direction	The sorting direction, ascending or descending, used when the check box Sorting is checked.
5	Nulls first	Indicates that null values must be ordered before non-nulls.
6	Expression	SQL expression that composes the current index element. This attribute is mutually exclusive with Column.
7	Element expression	The SQL expression used as the element, when the option Expression is selected.
8	Operator class	Operator class associated with the index element.
9	Collation	Collation associated with the index element.

Non-key columns

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.

12

Include columns
#	Field	Description
1	Column	List of columns owned by the table or view that can be used as non-key columns.
2	Non-key columns	The columns configured as non-key columns of the index (INCLUDE clause).

References

Index DDL
https://postgresql.org/docs/current/static/sql-createindex.html

Index only scan & Covering indexes
https://www.postgresql.org/docs/current/indexes-index-only-scans.html