Expression indexes in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an expression index in PostgreSQL?

An expression index is an index built on the result of an expression or function applied to one or more columns, not just on the raw column values.

beginner

Why use an expression index instead of a regular index?

Expression indexes speed up queries that filter or sort based on computed values, like lowercased text or date parts, which regular indexes on raw columns cannot optimize.

intermediate

How do you create an expression index on the lower case of a column named 'name'?

Use: CREATE INDEX idx_lower_name ON table_name (LOWER(name)); This creates an index on the lowercased values of 'name'.

intermediate

Can expression indexes be used with WHERE clauses?

Yes, you can create partial expression indexes by adding a WHERE clause to index only rows meeting certain conditions, improving performance and saving space.

advanced

What happens if you query without using the exact expression in the WHERE clause that matches the expression index?

PostgreSQL cannot use the expression index if the query's expression does not exactly match the index expression, so the index won't speed up that query.

What does an expression index in PostgreSQL index?

A Only the raw column values

B The result of a function or expression applied to columns

C Only primary key columns

D Only text columns

Expression indexes store the result of an expression or function applied to columns, not just raw column data.

Which SQL command creates an expression index on the lower case of column 'email'?

A CREATE INDEX idx_email_lower ON users (LOWER);

B CREATE INDEX idx_email_lower ON users (email);

C CREATE INDEX idx_email_lower ON users (LOWER(email));

D CREATE INDEX idx_email_lower ON users (LOWER(email)) WHERE email IS NOT NULL;

The correct syntax applies LOWER() to the column inside the index definition.

Can expression indexes improve performance for queries filtering by a computed value?

A Only for sorting, not filtering

B No, they only work on raw columns

C Only if the table is small

D Yes, they speed up queries using that computed value

Expression indexes optimize queries filtering or sorting by the computed expression.

What is a partial expression index?

A An index on an expression with a WHERE clause limiting rows indexed

B An index on only part of a column's text

C An index that covers only primary keys

D An index that is created automatically by PostgreSQL

Partial expression indexes index only rows that meet the WHERE condition.

If a query uses a different expression than the one in the expression index, what happens?

A The index is not used for that query

B The index is used anyway

C PostgreSQL automatically adjusts the index

D The query fails

The query must use the exact expression for PostgreSQL to use the expression index.

Explain what an expression index is and give a simple example of when you would use one.

Think about indexing computed values instead of raw columns.

Describe how partial expression indexes work and why they might be useful.

Consider indexing only some rows based on a condition.