Partial indexes with WHERE clause in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a partial index in PostgreSQL?

A partial index is an index built over a subset of table rows that satisfy a specific condition defined by a WHERE clause. It helps speed up queries that filter on that condition.

beginner

How do you create a partial index with a WHERE clause?

Use the syntax: <br>CREATE INDEX index_name ON table_name(column_name) WHERE condition;<br>This creates an index only on rows where the condition is true.

intermediate

Why use a partial index instead of a full index?

Partial indexes save space and improve performance by indexing only relevant rows. They make queries faster when filtering on the indexed condition and reduce maintenance overhead.

intermediate

Can a partial index speed up queries that do not use the WHERE condition in the index?

No. Partial indexes only help queries that filter rows matching the WHERE condition used in the index. Queries outside that condition won't use the partial index.

beginner

Example: What does this partial index do?<br>CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

It creates an index on the last_login column but only for rows where the user is active (active = true). Queries filtering active users by last_login will be faster.

What does a partial index in PostgreSQL do?

A Indexes all rows in a table

B Indexes only the first 100 rows

C Indexes columns with NULL values only

D Indexes only rows that satisfy a WHERE condition

A partial index indexes only the rows that meet the specified WHERE condition.

Which SQL clause is used to define a partial index?

A WHERE

B HAVING

C GROUP BY

D ORDER BY

The WHERE clause specifies the condition for the partial index.

What is a benefit of using a partial index?

A It indexes all columns in a table

B It disables query optimization

C It reduces index size by indexing fewer rows

D It automatically updates table data

Partial indexes reduce index size by indexing only rows that meet the condition.

Can a partial index speed up queries that do not filter on the indexed condition?

A Yes, always

B No, only queries matching the condition benefit

C Only if the table is small

D Only if the index is rebuilt

Partial indexes only help queries that filter rows matching the WHERE condition.

Which of these is a valid partial index creation statement?

A CREATE INDEX idx ON table(column) WHERE column > 100;

B CREATE INDEX idx ON table(column);

C CREATE INDEX idx ON table WHERE column > 100;

D CREATE INDEX idx WHERE column > 100 ON table(column);

The correct syntax places the WHERE clause after the column list.

Explain what a partial index is and why you might use one in PostgreSQL.

Think about indexing only some rows instead of the whole table.

Describe how to create a partial index and give an example with a WHERE clause.

Remember the order: CREATE INDEX, ON table(column), WHERE condition.