A partial index stores only rows that meet its predicate. An open-order queue can index its active subset while leaving historical rows out. A query must imply that predicate for PostgreSQL to use the partial index; a parameterized predicate can prevent the planner from proving that implication.
An expression index stores a calculated key such as LOWER(email). It helps a query that uses the corresponding expression. Normalizing text in an index doesn't change the stored source text. A unique expression index can enforce a business rule such as case-insensitive email uniqueness.
Expressions used as index keys must be immutable. Functions whose answers depend on the current time or session settings are unsuitable. Keep the index rule aligned with the query's exact cleanup semantics, including blanks and missing values. These exercises build the definitions, then inspect the resulting PostgreSQL catalog.
A partial index must contain the requested active subset.