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 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 insertion, deletion, or updates on the table. |
Unique |
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. |
Fast update |
Enables fast update techniques for GIN indexes. |
Buffering |
Enables the 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 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.
| 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 option in 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. |
3.4.3. 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.
| Attribute | Description |
|---|---|
Column |
List of columns owned by the table or view that can be used as non-key. |
Columns grid |
Holds the columns currently configured as non-key columns of the index. |
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