You need fast case-insensitive lookups on an email column and fast filtering on a field buried inside a JSON document. How does an indexed generated column solve this, and how does it compare with indexing the expression directly?
answer
- lower(email) is not sargable against an index on email
- generated column + index = named, constrainable, ORM-visible
- expression index = no row cost, must match the query text
- JSON: promote hot fields to typed generated columns
- UNIQUE on email_lower = case-insensitive uniqueness
basics
~20 sAdd a STORED generated column holding lower(email) or the extracted JSON field, then index that column. Queries filter on the column name and get an index seek. An expression index stores the same computed keys without a visible column, and the planner matches it when the query repeats the expression literally.
solid answer
~60 sA predicate like `WHERE lower(email) = ?` cannot use a plain index on `email`, because the index is ordered by the raw value, not the folded one. Two fixes: - **Generated column + index.** `email_lower text GENERATED ALWAYS AS (lower(email)) STORED`, then an index on `email_lower`. The computed value becomes a real column - self-documenting, usable in UNIQUE constraints, visible to ORMs, and matched trivially by the planner. Cost: extra bytes per row and a table rewrite to add it. - **Expression (functional) index.** An index built directly on `lower(email)`. No row-width cost; the computed keys live only in the index. The planner uses it when the query contains the same expression, so existing queries need no change - but a query written with a slightly different expression will not match. For JSON, the same pattern extracts a scalar so a value inside a document behaves like a typed, indexable column. Either way the expression must be deterministic, and either way you have added a structure that costs write time.
code
sql · 14 lines-- route 1: named generated column, indexed and uniquely constrained
ALTER TABLE users
ADD COLUMN email_lower text GENERATED ALWAYS AS (lower(email)) STORED;
CREATE UNIQUE INDEX users_email_lower_uk ON users (email_lower);
SELECT id FROM users WHERE email_lower = '[email protected]';
-- route 2: expression index, no schema-visible column
CREATE INDEX users_email_lower_ix ON users (lower(email));
SELECT id FROM users WHERE lower(email) = '[email protected]';
-- promoting a hot JSON field to a typed, indexable column
ALTER TABLE documents
ADD COLUMN status text GENERATED ALWAYS AS (payload->>'status') STORED;
CREATE INDEX documents_status_ix ON documents (status);go deeper
Know that a function applied to a column defeats a plain index, and that indexing the computed value - via a generated column or an expression index - fixes it.
Compare the two routes on row width, planner matching, constraint support, and the JSON promotion pattern.
Talk about diagnosing the missed match in a plan, write amplification from column plus index, composite ordering, and the migration cost of adding a stored column to a hot table.
Frame it as deciding which derived values become part of the schema contract versus staying private to one access path, and how that choice ages as query patterns change.
## Why the plain index does not help A B-tree index stores keys in sorted order and can only answer questions phrased in terms of those keys. An index on `email` is ordered by the exact stored string, so `WHERE lower(email) = '[email protected]'` is not sargable against it: the engine would have to apply `lower()` to every key to know whether it matches, which is a full scan of the index or the table. The same is true for a predicate on a field extracted from a JSON document when the index covers the document as a whole, and for a predicate on a truncated timestamp against an index on the raw timestamp. The fix in every case is the same idea: build a structure whose keys are the computed values. ## Route 1: generated column plus an ordinary index Declare the derived value as a STORED generated column and index it like any column. The payoff is that the derived value becomes a first-class part of the schema. It appears in the catalog and in ORM-generated models, it can carry a UNIQUE constraint (giving you case-insensitive uniqueness on email, a genuinely useful invariant that is otherwise awkward), it can be part of a composite index, and no one has to remember the exact expression to get the index. Statistics are collected on it as a real column, so selectivity estimates for the derived value can be better than for a raw column the planner does not understand. The costs: every row carries the extra bytes, writes recompute and re-log it, and adding the column to an existing large table normally requires a full rewrite under a heavy lock. On MySQL the same pattern with a VIRTUAL column plus a secondary index is common - the index materializes the value while the row does not, which gets you indexability without widening rows. ## Route 2: expression index Most engines let you index an expression directly. Nothing is added to the row; the computed keys exist only inside the index. This is cheaper in row width and requires no schema change visible to application code. The catch is matching. The planner uses an expression index when the query contains an expression it can prove equivalent to the indexed one - in practice, when the query repeats it. Write `WHERE lower(email) = ?` and you get the index; use a case-insensitive LIKE variant or a different normalization and you silently get a sequential scan. This is a classic production surprise: the index exists, the dashboard says the query is slow, and the plan shows the index untouched. A named column removes that ambiguity, which is why teams with many hand-written queries often prefer it. ## JSON specifically Document columns are the strongest case for the generated-column route. Extracting a scalar field and declaring it as a generated column gives you a typed, named, constrainable field with an ordinary B-tree index on it, while the rest of the document stays schemaless. That is often better than a general document index, which is larger and answers containment questions rather than range or sort queries. When you know which two or three fields are hot, promote exactly those. ## Things that bite - **Determinism is still required.** Both routes reject volatile expressions; a functional index on a mislabelled immutable function can corrupt just like a stored column. - **Write amplification.** Each index is maintained on every write that changes its inputs. Adding a generated column plus its index is two costs, not one. - **The value must actually be selective.** Indexing a folded two-value status column buys nothing. - **Case-insensitive collations.** Some engines offer them, which solve the email case without any derived structure - worth naming as the simpler option when the whole column should be compared that way. - **Order matters for composites.** If the hot query filters on tenant and case-folded email, index the pair rather than adding a second single-column index. ## How to choose Prefer the generated column when the derived value is part of the domain (a normalized email, an extracted status, a computed total), when you want a constraint on it, or when many hand-written queries would otherwise have to repeat the expression exactly. Prefer the expression index when the derivation is an implementation detail of one access path, when row width matters on a very wide or very hot table, or when you cannot afford the rewrite that adding a stored column implies.
- Your team added an index on lower(email) but the slow query still does a full scan. What do you check first?Whether the query's predicate is textually equivalent to the indexed expression. A case-insensitive LIKE, a different normalization function, an implicit cast, or a collation mismatch all prevent the match, and the planner falls back to a scan. Check the plan, then either rewrite the predicate to match the index or convert the derivation into a named generated column so the ambiguity disappears.
- When is a case-insensitive collation a better answer than a generated column plus index?When the whole column should always compare case-insensitively, everywhere. Then a case-insensitive collation makes ordinary indexes, comparisons, and uniqueness behave correctly with no derived structure and no extra bytes. The generated column wins when you need the folded form as a distinct value alongside the original, or when the engine or the ORM does not handle per-column collations well.
saying these in an interview costs you the question
- Believing an index on email already accelerates a predicate on lower(email)
- Thinking an expression index is used regardless of how the predicate is written
- Adding a generated column and forgetting to create the index on it
- Assuming indexing a JSON column as a whole makes every field inside it fast for equality and range queries
- Ignoring the write-side cost of maintaining both a stored column and its index