This chapter covers more advanced PostgreSQL subjects including optimizing table access with indices, advanced
table concepts such as inheritance and constraints, the practical use of non-atomic array values, and explicit use of
transactions and cursors. These sophisticated features greatly set PostgreSQL apart from many other relational
database management systems.
This chapter also documents programmatic concepts such as triggers and sequences. Finally, for programmers wanting to add
customized routines to the database, we document how to extend PostgreSQL through the addition of user-defined functions and
operators.
Indices are database objects that can greatly increase database performance, enabling faster
execution of statements involving comparative criteria. An index tracks the data on one or more columns in a table, allowing
conditional clauses (such as the WHERE clause) to find their targeted rows more
efficiently.
The internal workings of indices vary, and there are several implementations to choose from. This section describes the
different types of indices available, and explains when you should use one type over the other.
While indices exist to enhance performance, they also contribute to system overhead. Indices must be updated as data
in the column that they are applied to fluctuates. Maintaining infrequently used indices decreases performance when the amount of time spent maintaining them outweighs the time saved through using them. In general, indices
should be applied only to columns that you expect to use frequently in comparative expressions.
