Expression indexes: the column was always a degenerate case

Why is every index really on an expression, and what silently invalidates one?

The constraint is that a B-tree answers predicates on the key it stores, and only in the form it stores them. An index on email cannot serve WHERE lower(email) = 'x', because lower() is not guaranteed to preserve the stored order and the planner will not assume it does. An index on lower(email) serves exactly that predicate and not WHERE email = 'X'.

Generalizing: an index on f(x) answers predicates of the form f(x) op c. A plain column index is the case where f is the identity. An index is not on a column. It is on an expression, and a column is the expression you happened to also store. That makes expression indexes the general case and ordinary indexes the special one, which is the right way round.

CREATE INDEX users_email_lower ON users (lower(email));
-- serves:  WHERE lower(email) = 'priya@example.com'

The requirement is determinism (the same input always gives the same output), because the index is a cache of the function's output. Nothing in the system tracks the function's definition as a cache key. Two failure modes follow directly.

1. Mark a volatile function as immutable to get it indexed, and the index silently disagrees with reality from the first insert. PostgreSQL will let you do this; it cannot check.
2. Worse, because it is not user error: Collation drift. Text index ordering depends on the operating system's collation library (the rules for sorting strings). When glibc 2.28 changed its collation rules, text indexes built on older systems became silently invalid after an OS upgrade: queries returned wrong results, unique constraints admitted duplicates, and there was no error anywhere. This is a live operational hazard, and it is why ICU collations with explicit version tracking exist.

Standing questions again. What does this system assume it cannot verify? That f is stable across time, platforms and library upgrades. Who pays? Everyone downstream, silently, until someone notices a duplicate.

The alternative is a stored generated column plus an ordinary index on it. You pay row width instead of index-build cost, and you get a column the planner treats as first-class: other indexes can include it, other predicates can reference it, and it appears in ordinary column statistics. (PostgreSQL does gather statistics on expression indexes, so the estimation gap is smaller than folklore claims, but the composability advantage is real.)