Partial indexes with WHERE clause in PostgreSQL - Step-by-Step Execution

Concept Flow - Partial indexes with WHERE clause

Start: Create Index

↓

Check WHERE condition

Yes↓

Include row in index

↓

No

→Skip row

↓

Repeat for all rows

↓

Index created with subset of rows

The database creates an index only for rows that meet the WHERE condition, skipping others.

Execution Sample

PostgreSQL

CREATE INDEX idx_active_users ON users (last_login) WHERE active = true;

Creates an index on last_login only for users where active is true.

Execution Table

Step	Row Data (active, last_login)	WHERE Condition (active = true)	Action	Index State
1	(true, '2024-06-01')	true	Include in index	Index contains last_login for this row
2	(false, '2024-05-20')	false	Skip row	No change
3	(true, '2024-06-03')	true	Include in index	Index updated with last_login for this row
4	(false, '2024-04-15')	false	Skip row	No change
5	(true, '2024-06-02')	true	Include in index	Index updated with last_login for this row
6	All rows processed	-	-	Index creation complete with partial rows

💡 All rows checked; only rows with active = true included in index

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Index contents	empty	last_login='2024-06-01'	last_login='2024-06-01'	last_login='2024-06-01','2024-06-03'	last_login='2024-06-01','2024-06-03'	last_login='2024-06-01','2024-06-03','2024-06-02'	last_login='2024-06-01','2024-06-03','2024-06-02'

Key Moments - 3 Insights

Why are some rows skipped when creating the index?

Rows are skipped because they do not meet the WHERE condition (active = true), as shown in execution_table rows 2 and 4 where the condition is false and the action is to skip.

Does the index include all rows from the table?

No, the index only includes rows where the WHERE clause is true. The execution_table shows only rows with active = true are included.

What happens if the WHERE clause is omitted?

If omitted, the index includes all rows. Here, the WHERE clause filters rows, so only a subset is indexed.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What is the action taken for the row with data (true, '2024-06-03')?

A Skip row

B Include in index

C Update row

D Delete row

💡 Hint

Check the 'Action' column in execution_table row 3.

At which step does the index first get updated with a row's last_login value?

A Step 4

B Step 2

C Step 1

D Step 6

💡 Hint

Look at the 'Index State' column in execution_table rows 1 and 2.

If the WHERE clause was changed to active = false, how would the index contents change after step 5?

A Index would contain last_login values from rows where active is false

B Index would be empty

C Index would contain all rows

D Index would contain last_login values from rows where active is true

💡 Hint

Refer to variable_tracker and execution_table to see how WHERE condition controls included rows.

Concept Snapshot

Partial Indexes with WHERE clause:
CREATE INDEX index_name ON table(column) WHERE condition;
Only rows matching condition are indexed.
Improves performance by indexing relevant subset.
Saves space and speeds queries filtering on condition.

Full Transcript

Partial indexes in PostgreSQL allow creating an index on only a subset of table rows that meet a specific condition defined by a WHERE clause. During index creation, each row is checked against the WHERE condition. If the condition is true, the row's indexed column value is included in the index. If false, the row is skipped. This results in a smaller, more efficient index that speeds up queries filtering on the condition. For example, creating an index on last_login for only active users includes only those rows where active is true. Rows where active is false are not indexed. This selective indexing saves space and improves query speed for relevant data.