A functional index, also called an expression index, stores the result of a function or expression applied to one or more table columns. Postgres can use it when the same expression appears in a query’s WHERE or ORDER BY clause. section 4.9
Use a functional index when a frequently executed query repeatedly computes a searchable value:
- Create an index on a built-in function, custom function, or column expression. section 4.9
- Match the query expression exactly; even a small change can make Postgres choose a sequential scan instead. section 5.6.1
- Avoid indexing every possible expression: excessive indexes increase the table’s index count and are mainly justified for frequent, costly queries. section 4.9
Indexing a player-performance margin
A query filtering players by win_count - loss_count initially performs a sequential scan, removes 9,269 rows by filtering, and takes 2.524 ms. Create the matching index: CREATE INDEX idx_perf_margin ON game.player_stats ((win_count - loss_count)); The revised plan uses a Bitmap Index Scan on idx_perf_margin and completes in 1.200 ms, while returning the same 731 players.
Indexing a JSON expression
For JSON data, index the exact extraction expression used by the query: CREATE INDEX idx_pizza_type ON pizzeria.order_items ((pizza ->> 'type')); A count of custom pizzas improves from 1.185 ms with a sequential scan to 0.376 ms with a Bitmap Index Scan. A query using pizza -> 'type' or pizza ->> 'size' does not use this index because it specifies a different expression.
CREATE INDEX functional_index_name ON table_name(function_or_expression(columnA ,[additional_columns]))
Use the generic CREATE INDEX form to adapt a function or expression to a table and its indexed columns. section 4.9
