Covering indexes with INCLUDE in PostgreSQL - Step-by-Step Execution

Concept Flow - Covering indexes with INCLUDE

Create index with key columns

↓

Add INCLUDE columns to index

↓

Query uses index scan

↓

Index provides all needed columns

↓

No need to access table rows

↓

Faster query

Create an index with key columns and extra included columns so queries can get all needed data from the index alone, speeding up reads.

Execution Sample

PostgreSQL

CREATE INDEX idx_users_email ON users(email) INCLUDE (name, age);

SELECT email, name, age FROM users WHERE email = '[e-mail]';

Create an index on email with name and age included, then query selecting all three columns using the index only.

Execution Table

Step	Action	Index Content	Query Access	Result
1	Create index on email with INCLUDE (name, age)	email (key), name, age (included)	N/A	Index ready
2	Run query filtering by email	email, name, age	Index scan uses email to find row	Partial data from index
3	Retrieve name and age from included columns	email, name, age	No table row access needed	All data from index
4	Return result set	email, name, age	Data fully from index	Query faster
5	Query ends	N/A	N/A	Execution complete

💡 Query finishes after using index with included columns, no table row fetch needed

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Index	None	Created with email key + name, age included	Used for email lookup	Provides name, age from index	Used fully for query
Query Result	Empty	Empty	Partial data from index	Complete data from index	Returned to user

Key Moments - 2 Insights

Why does the query not need to access the table rows after using the index?

Because the index includes all columns needed by the query (email, name, age), the database can get all data directly from the index without extra table lookups, as shown in execution_table rows 3 and 4.

What is the difference between key columns and included columns in the index?

Key columns (like email) are used to organize and search the index, while included columns (name, age) are stored only to provide extra data for queries but do not affect index order. This is shown in execution_table row 1.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the query stop accessing the main table rows?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'Query Access' column in execution_table rows 2 and 3 to see when table row access stops.

According to variable_tracker, what columns does the index contain after creation?

A Only email

B Email as key, name and age included

C Email, name, age all as key columns

D Name and age only

💡 Hint

Look at the 'Index' variable values after Step 1 in variable_tracker.

If the query requested a column not included in the index, what would happen?

A Index would automatically include the missing column

B Query would fail

C Database would access table rows to get missing columns

D Query would run faster

💡 Hint

Consider the purpose of included columns and what happens if data is missing from the index.

Concept Snapshot

CREATE INDEX idx_name ON table(key_columns) INCLUDE (extra_columns);

- Key columns define index order and search.
- INCLUDE columns store extra data for queries.
- Queries using only these columns avoid table row access.
- This speeds up read queries by using index alone.

Full Transcript

Covering indexes with INCLUDE means creating an index that has key columns used for searching and extra columns included to provide additional data. When a query requests only these columns, the database can get all data from the index without reading the main table rows. This makes queries faster. For example, creating an index on the email column and including name and age allows a query filtering by email and selecting email, name, and age to use only the index. The execution steps show index creation, query running, data retrieval from the index, and query completion without table access. Key moments include understanding why table rows are not accessed and the difference between key and included columns. Visual quizzes test understanding of when table access stops, index contents, and behavior when columns are missing from the index.