Databases run into this problem whenever a query filters on lower(email), on a date pulled from a timestamp, or on something derived from two columns. If an index holds only the raw column data, the optimizer does not always have a direct way to search by that transformed result. An expression index fixes that by storing the result of the expression inside the index, which lets the search target the same transformed result named in the predicate. PostgreSQL calls these indexes indexes on expressions, Oracle calls them function-based indexes, MySQL supports functional index parts, SQL Server usually gets there through indexed computed columns, and SQLite supports indexes on expressions.
What an Expression Index Really Is
Most indexes store values taken straight from table columns and arrange those values so the database can search them quickly. An expression index changes that arrangement by storing the result of an expression instead. That stored value could be a concatenated name, a numeric calculation, or some other derived result built from one or more columns. Because the index holds the computed result rather than only the source column data, the optimizer can search for the transformed value named in the query instead of recalculating that value row by row.
Names differ across database engines, but the idea stays very close. PostgreSQL refers to indexes on expressions. Oracle uses the term function-based index. MySQL supports functional key parts. SQL Server usually gets there through computed columns that can be indexed. SQLite allows expressions inside CREATE INDEX in place of plain column names. Syntax changes from engine to engine, yet the storage idea remains the same. The index is built from the value produced by the expression, so a plain index on first_name is not the same as an index on first_name || ' ' || last_name, and an index on amount is not the same as an index on abs(amount). Those indexes are ordered by different values, which means they support different searches.
Same Idea Across Database Engines
For major SQL products, expression indexing comes down to precomputing the value the query wants to search. PostgreSQL lets you place the expression directly inside the index definition. Oracle does the same through function-based indexes. MySQL also supports direct expression-based index parts, though its rules have their own wording and syntax details. SQL Server takes a nearby route by defining a computed column first and then indexing that computed column. SQLite allows expressions directly inside CREATE INDEX as well. Different spellings and DDL forms can make the feature look less related than it really is, yet the stored value is still a computed result rather than raw column data.
PostgreSQL makes that idea easy to see with a full-name lookup. Instead of indexing first_name and last_name separately, the index stores the concatenated result, which means the query can search the same value it asks for.
CREATE INDEX people_full_name_idx
ON people ((first_name || ' ' || last_name));
SELECT *
FROM people
WHERE (first_name || ' ' || last_name) = 'Alex Carter';That index is ordered by the combined full-name value, not by first_name alone and not by last_name alone. If the query filters on that same concatenation, the optimizer has an indexed value that matches the predicate directly.
SQL Server reaches the same destination in a different form. Rather than placing the expression directly inside CREATE INDEX, you first add a computed column and then build the index on that column.
ALTER TABLE people
ADD full_name AS CONCAT(first_name, ' ', last_name) PERSISTED;
CREATE INDEX ix_people_full_name
ON people(full_name);
SELECT *
FROM people
WHERE full_name = 'Kaitlyn Brooks';What changes here is the syntax, not the idea. The database is still indexing a derived value rather than the raw source columns. The computed column simply gives that derived value a named place in the table definition before the index is built.
MySQL also supports direct expression-based indexing, and its syntax has its own small rules. Parentheses are part of the definition, and regular column parts can appear in the same index beside functional parts. That puts MySQL closer to PostgreSQL and SQLite in how the statement looks, while the internal storage route is tied to hidden virtual generated columns. All of these engine-specific details matter at DDL time, but the larger point stays steady. The index stores the result of a calculation, string expression, or function call so the search can target that computed value directly.
The Query Must Match
Matching the query text to the indexed expression is where this topic becomes more exact than people usually expect. SQLite is very strict about this. Its planner looks for the same expression form in the WHERE clause or ORDER BY clause that appears in the index definition, apart from very small syntax differences such as whitespace. That means mathematically equivalent expressions are not always treated as the same thing for index matching.
This example makes that easier to see:
CREATE TABLE t2(x, y, z);
CREATE INDEX t2_xy_sum_idx ON t2(x + y);
SELECT *
FROM t2
WHERE y + x = 22;
SELECT *
FROM t2
WHERE x + y = 22;The two predicates return the same rows, but they are not written the same way. With SQLite, the index definition stores x + y, so the second query lines up with the indexed expression while the first query does not. That rule gives you a practical habit to keep in mind while writing queries. When an index is built on an expression, keep the predicate in the same form as the indexed expression instead of rewriting it into a nearby equivalent form.
MySQL has a similar idea with functional key parts. If an index is built on SUBSTRING(col1, 1, 10), the predicate needs to ask for that same expression form to benefit from the index. Change the arguments or alter the expression, and the match can disappear. That is an important part of how expression indexes behave. The database is not promising fast access for every related transformation. It is storing a particular computed value, and the query has to ask for that same value in a compatible form.
PostgreSQL follows the same general logic. If the index is built on (first_name || ' ' || last_name), then a predicate on that same concatenation is the natural fit for the index. If the index is built on upper(last_name), then a query filtering on upper(last_name) lines up with the stored value. Small wording changes in documentation from one engine to the next can make this feel more abstract than it is. What helps most is remembering what the index actually contains. It contains the result of the indexed expression, so the search has to target that same result rather than a loosely related version of it.
Good Fits for Expression Indexes
Some queries do not search raw column values. They search transformed text, a calendar date taken from a timestamp, or a numeric result computed from other columns. That is where expression indexes tend to help most. The common thread is repetition. When the same derived value keeps appearing in filters, sorting, or lookup logic, storing that derived value in the index gives the optimizer a direct route to the value named in the predicate. That does not mean every expression belongs in an index, but a recurring query form can line up very well with this feature.
Lowercased Text Searches
Case-insensitive lookup is one of the most natural places for an expression index. Text may be stored exactly as entered, with uppercase and lowercase letters preserved, while the search condition folds both sides to a common form through lower() or upper(). If the only index is on the original text column, the database may have to apply that text function during filtering before it can decide which rows match. When the transformed value is stored in an index, the search can target that stored form directly.
PostgreSQL can express that directly:
CREATE INDEX users_email_lower_idx
ON users (lower(email));
SELECT user_id, email
FROM users
WHERE lower(email) = 'alex@example.com';That index is ordered by the lowercased email value, so the predicate is searching the same value stored in the index. When a table is queried this way again and again, indexing the transformed text can be far more helpful than keeping only an index on the original email column. PostgreSQL also allows a UNIQUE expression index here, which means a system can block entries that differ only by case rather than treating them as separate values.
Oracle reaches the same goal with a function-based index:
CREATE INDEX emp_last_name_upper_idx
ON employees (UPPER(last_name));
SELECT employee_id, last_name
FROM employees
WHERE UPPER(last_name) = 'BROOKS';Searches like this are a strong fit because the expression is stable, repeated, and tied closely to the filter itself. A database can still search text without this type of index, but the engine has a far better chance of picking an indexed route when the transformed value already exists in index form. That becomes more helpful as the table grows and the same predicate keeps appearing in login checks, customer lookups, or administrative searches.
Date Buckets
Reporting queries frequently slice timestamps down to a day, month, or year. That sounds minor, yet it changes what the predicate is asking for. An index on order_ts is ordered by full timestamp values, while a filter on CONVERT(date, order_ts) or a related date-only expression is asking for a derived calendar value. If the same date bucket keeps appearing in filters, grouping, or joins, an expression index or an indexed computed column gives that derived value a place in indexed storage instead of forcing repeated recalculation during filtering.
SQL Server handles this through a computed column that can be indexed:
ALTER TABLE Sales
ADD order_day AS CONVERT(date, order_ts) PERSISTED;
CREATE INDEX ix_sales_order_day
ON Sales(order_day);
SELECT order_id, order_ts
FROM Sales
WHERE order_day = CONVERT(date, '20260501', 112);Two details help frame why this form is useful. First, SQL Server places rules on indexed computed columns. The expression has to be deterministic, and index-column expressions also have to be precise. Second, Microsoft recommends explicit date conversion with a deterministic style when date literals are involved. That avoids string-to-date ambiguity and keeps the computed value in a form the engine can treat consistently.
PostgreSQL, Oracle, and MySQL can support this broader idea as well, though the syntax changes from engine to engine. The reason date buckets fit so well is tied to the query itself. If the business question keeps landing on the date pulled from a timestamp instead of the full timestamp value, the indexed value that helps most is the extracted date rather than the raw column.
Derived Numeric Values
Arithmetic expressions are another strong match. Filters and sorts sometimes care less about a stored number than about a value derived from it, such as the magnitude of a balance change, a discounted total, or a formula built from several columns. Oracle allows function-based indexes on arithmetic expressions, and SQLite gives a practical example with abs(amt). In both cases, the same idea applies. The query is not asking for the base value in its original form. It is asking for a computed numeric result, so indexing that result can be a better fit than indexing only the source columns.
SQLite’s abs(amt) example captures this well:
CREATE INDEX acctchng_magnitude_idx
ON account_change(acct_no, abs(amt));
SELECT acct_no, amt
FROM account_change
WHERE acct_no = 17
ORDER BY abs(amt) DESC;That index does two jobs at the same time. It groups rows by account number and then orders them by the absolute value of the amount change. If the query keeps asking for rows in that form, indexing the derived numeric result gives the planner much better alignment with the requested order. The point is not limited to abs(). Similar thinking applies to recurring formulas such as price * quantity, subtotal - discount, or a rating score built from stored columns. What makes these expressions a good fit is repetition. The more the query logic returns to the same derived number, the more sense it makes to store that number in index form.
Write Cost Matching Rules Engine Limits
Read speed is only half of the decision. Every expression index has a write-side cost because the database has to compute the expression when relevant rows are inserted or updated. PostgreSQL states that expression indexes are relatively expensive to maintain because the derived value must be computed for each row insertion and for each non-HOT update. Oracle makes a similar point and states that function-based indexes on columns that are modified frequently are expensive for the database to maintain. That is why the best fit is usually a recurring expression that helps enough on reads to justify the added write overhead.
Engine rules can narrow what is allowed. SQL Server requires indexed computed-column expressions to be deterministic, and index-column expressions must also be precise. That rules out a range of expressions tied to float or real values, cross-row calculations, or nondeterministic behavior. It also places SET option requirements on sessions that create, change, and query these indexes. SQLite has a similar restriction in spirit. Functions inside expression indexes must be deterministic, and subqueries are not allowed. MySQL ties functional index parts to hidden virtual generated columns, which brings generated-column restrictions along with them. That means no subqueries, parameters, variables, stored functions, or loadable functions inside those indexed expressions. MySQL also does not allow primary keys to include these functional parts.
Oracle has a few limits worth keeping in view as well. A function-based index cannot contain NULL, so NVL may be needed when null-producing expressions are part of the search logic. Oracle also requires any user-defined PL/SQL function referenced by the index expression to be declared DETERMINISTIC, and if the function’s semantics change later, dependent function-based indexes need a rebuild so they stop reflecting the prior function behavior. Those rules are not just side notes. They affect daily query tuning, storage choices, and update cost. Expression indexes pay off best when the expression is stable, the predicate repeats, and the engine can legally store that derived result in a dependable form.
Conclusion
Expression indexes let a database store the result of a repeated calculation inside the index instead of relying only on raw column values. That changes how the optimizer can search, because predicates on lowercased text, date-derived values, or numeric formulas can match an indexed computed result rather than forcing extra row-by-row evaluation. The tradeoff is added index maintenance during inserts and updates, which is why they make the most sense when the same derived expression appears frequently enough to justify the extra storage and write cost.
PostgreSQL
CREATE INDEXDocumentationOracle Database
CREATE INDEXDocumentationMySQL Documentation on
CREATE INDEXand Functional Key Parts
