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 by basic attributes and a set of elements with their own attributes which reunited form the index as a whole. In the database model pgModeler includes indexes at the bottom of tables in a portion called extended attributes section. The image below shows an example on 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. |
** Index DDL**
https://postgresql.org/docs/current/static/sql-createindex.html