Functional index
A functional index is a database index whose entries are computed from a function or expression applied to table data.
Just Use Postgres!
A functional index is a database index whose entries are computed from a function or expression applied to one or more table columns.
- How it is used
- Optimizes frequent filters or ordering operations that compute values from one or more columns.
- How deep it goes
- Explains syntax, execution-plan changes, exact-expression matching, and over-indexing, without deeper index implementation details.
- Examples
- Demonstrates player performance margins and JSON pizza fields, comparing sequential scans with bitmap index scans.
- What you can do after
- Apply it via PostgreSQL CREATE INDEX and EXPLAIN to optimize matching computed-value queries and verify plan changes.
PostgreSQL Mistakes and How to Avoid Them
An expression index is a database index whose indexed keys are computed from expressions over table data.
- How it is used
- Speeds recurring queries whose predicates apply a known function or transformation to stored values.
- How deep it goes
- Explains expression matching, query-plan consequences, workarounds, and function-or-cast limitations, but stops short of index implementation internals.
- Examples
- Uses timestamp truncation in payments and expression-based indexes on JSON keys as concrete cases.
- What you can do after
- Diagnose index usage with EXPLAIN and create predicates or expression indexes that match the indexed representation.
Read how PostgreSQL Mistakes and How to Avoid Them explains functional index →
Related concepts
- indexingkind of · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.
- B-treeused by · A B-tree is a balanced multiway search tree that maintains ordered keys and supports efficient searching, insertion, deletion, and sequential access.
- sequential scancontrasts with · A sequential scan is a search or query operation that examines items in a table or collection in sequence to identify those matching a condition, rather than locating them through an index.
- database functionuses · A database function is a named routine stored or executed in a database that accepts inputs and computes a result, often a scalar or table-valued result.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.