Covering indexes with INCLUDE in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of INCLUDE columns on index-only scans

Given a table employees(id INT, name TEXT, department TEXT, salary INT) with an index created as CREATE INDEX idx_dept_salary ON employees(department) INCLUDE (salary);, which query can fully use the index without accessing the table heap?

A SELECT department, salary FROM employees WHERE department = 'Sales';

B SELECT department, name FROM employees WHERE department = 'Sales';

C SELECT salary FROM employees WHERE salary > 50000;

D SELECT id, salary FROM employees WHERE department = 'Sales';

💡 Hint

Think about which columns are in the index key and which are included.

The index idx_dept_salary has department as key and salary as included column. Queries selecting only these columns and filtering by department can use index-only scans, avoiding heap access.

📝 Syntax

intermediate

Correct syntax for creating an index with INCLUDE

Which of the following is the correct syntax to create a covering index on orders(order_date) including customer_id and total_amount columns in PostgreSQL?

A CREATE INDEX idx_orders_date ON orders(order_date, customer_id, total_amount);

B CREATE INDEX idx_orders_date ON orders(order_date) INCLUDE (customer_id, total_amount);

C CREATE INDEX idx_orders_date ON orders INCLUDE (customer_id, total_amount) (order_date);

D CREATE INDEX idx_orders_date ON orders(order_date) WITH INCLUDE (customer_id, total_amount);

💡 Hint

Remember the position of the INCLUDE clause in the syntax.

The correct syntax places the INCLUDE clause after the indexed columns list. Option B follows this pattern.

🧠 Conceptual

advanced

Why use INCLUDE columns in indexes?

What is the main advantage of adding columns with INCLUDE in a PostgreSQL index?

A It enforces uniqueness on the included columns.

B It creates a composite primary key with included columns.

C It allows storing additional columns in the index to support index-only scans without increasing index key size.

D It compresses the included columns to save disk space.

💡 Hint

Think about how included columns affect index size and query performance.

Including columns in an index stores them only in the index leaf nodes, not in the key, allowing index-only scans without bloating the index key size.

🔧 Debug

advanced

Why does this index not support index-only scans?

Consider the table products(id INT, category TEXT, price NUMERIC, stock INT) and the index:

CREATE INDEX idx_cat_price ON products(category, price);

Why might a query SELECT category, stock FROM products WHERE category = 'Books'; not use an index-only scan?

A Because <code>stock</code> is not part of the index key or included columns, so the heap must be accessed.

B Because the index is on <code>category</code> and <code>price</code>, but the query filters on <code>stock</code>.

C Because the index is not unique, index-only scans are disabled.

D Because the query does not select <code>price</code>, the index cannot be used.

💡 Hint

Think about which columns are available in the index and which are requested by the query.

The query selects stock, which is not in the index key or included columns, so PostgreSQL must access the table heap to get that data, preventing index-only scans.

❓ optimization

expert

Optimizing a query with covering indexes

You have a large table sales(order_id INT, customer_id INT, order_date DATE, total NUMERIC, status TEXT). You often run this query:

SELECT order_date, total FROM sales WHERE status = 'completed';

Which index will best optimize this query for index-only scans?

A CREATE INDEX idx_status_date ON sales(status, order_date, total);

B CREATE INDEX idx_total ON sales(total) INCLUDE (order_date, status);

C CREATE INDEX idx_order_date ON sales(order_date) INCLUDE (status, total);

D CREATE INDEX idx_status ON sales(status) INCLUDE (order_date, total);

💡 Hint

Consider which columns are filtered and which are selected, and how INCLUDE affects index size.

Option D indexes status as key for filtering and includes order_date and total to cover the query columns without bloating the key size, enabling efficient index-only scans.

Practice

1. What is the main purpose of using INCLUDE in a PostgreSQL index?

easy

A. To change the data type of indexed columns

B. To create a unique constraint on the indexed columns

C. To delete columns from the index

D. To add extra columns to the index for faster SELECT queries without searching on them

Solution

1. Step 1: Understand the role of INCLUDE in indexes

   INCLUDE adds extra columns to the index that are not used for searching but can be returned in queries.

2. Step 2: Identify the benefit of these extra columns

   These extra columns help avoid reading the main table, speeding up SELECT queries that need those columns.

3. Final Answer:

   To add extra columns to the index for faster SELECT queries without searching on them -> Option D

4. Quick Check:

   INCLUDE adds columns for SELECT speed [OK]

Hint: INCLUDE adds columns to speed SELECT, not for searching [OK]

Common Mistakes:

• Thinking INCLUDE creates unique constraints
• Believing INCLUDE removes columns
• Assuming INCLUDE changes data types

2. Which of the following is the correct syntax to create a covering index on table users for column email and include last_login?

easy

A. CREATE INDEX idx_email ON users(email) INCLUDE (last_login);

B. CREATE INDEX idx_email ON users(email, last_login);

C. CREATE INDEX idx_email ON users INCLUDE (email, last_login);

D. CREATE INDEX idx_email ON users(email) WITH (last_login);

Solution

1. Step 1: Recall the syntax for INCLUDE in PostgreSQL indexes

   The correct syntax is to specify indexed columns first, then use INCLUDE for extra columns.

2. Step 2: Match the syntax to the options

   CREATE INDEX idx_email ON users(email) INCLUDE (last_login); correctly uses CREATE INDEX idx_email ON users(email) INCLUDE (last_login);

3. Final Answer:

   CREATE INDEX idx_email ON users(email) INCLUDE (last_login); -> Option A

4. Quick Check:

   Indexed columns first, INCLUDE for extras [OK]

Hint: Indexed columns before INCLUDE clause [OK]

Common Mistakes:

• Putting all columns inside parentheses without INCLUDE
• Using WITH instead of INCLUDE
• Including columns in wrong order

3. Given the index CREATE INDEX idx_name ON employees(last_name) INCLUDE (first_name, department);, what will the query SELECT last_name, first_name FROM employees WHERE last_name = 'Smith'; most likely do?

medium

A. Use the index but still access the table to get first_name

B. Use the index to find rows and return both last_name and first_name without accessing the table

C. Scan the whole table because first_name is not indexed

D. Return an error because first_name is not indexed

Solution

1. Step 1: Understand what INCLUDE columns do in the index

   INCLUDE columns are stored in the index to avoid accessing the main table for those columns.

2. Step 2: Analyze the query and index usage

   The query filters on last_name (indexed) and selects first_name (included). The index covers both, so no table access needed.

3. Final Answer:

   Use the index to find rows and return both last_name and first_name without accessing the table -> Option B

4. Quick Check:

   INCLUDE columns avoid table access [OK]

Hint: INCLUDE columns can be returned without table access [OK]

Common Mistakes:

• Assuming INCLUDE columns are not stored in the index
• Thinking table scan is always needed
• Confusing INCLUDE with indexed columns

4. You wrote this index: CREATE INDEX idx_order ON orders(order_date) INCLUDE (customer_id; but get a syntax error. What is the problem?

medium

A. INCLUDE cannot be used with order_date

B. You must list customer_id before order_date

C. Missing closing parenthesis after customer_id

D. INCLUDE requires at least two columns

Solution

1. Step 1: Check the syntax of the CREATE INDEX statement

   The statement has an opening parenthesis after INCLUDE but no closing parenthesis.

2. Step 2: Identify the syntax error

   Missing closing parenthesis causes the syntax error.

3. Final Answer:

   Missing closing parenthesis after customer_id -> Option C

4. Quick Check:

   Parentheses must be balanced [OK]

Hint: Check parentheses carefully in INCLUDE clause [OK]

Common Mistakes:

• Forgetting closing parenthesis
• Misordering columns
• Thinking INCLUDE needs multiple columns

5. You want to speed up this query: SELECT product_id, price, stock FROM products WHERE product_id = 123; by creating a covering index. Which index is best?

hard

A. CREATE INDEX idx_product ON products(product_id) INCLUDE (price, stock);

B. CREATE INDEX idx_product ON products(price, stock) INCLUDE (product_id);

C. CREATE INDEX idx_product ON products(product_id, price, stock);

D. CREATE INDEX idx_product ON products(product_id);

Solution

1. Step 1: Identify the filtering and selected columns in the query

   The query filters on product_id and selects price and stock.

2. Step 2: Choose an index that filters on product_id and includes price and stock

   CREATE INDEX idx_product ON products(product_id) INCLUDE (price, stock); indexes product_id and includes price and stock, covering the query efficiently.

3. Step 3: Compare other options

   CREATE INDEX idx_product ON products(price, stock) INCLUDE (product_id); indexes price and stock, not filtering column; CREATE INDEX idx_product ON products(product_id, price, stock); indexes all columns but includes unnecessary columns in index key; CREATE INDEX idx_product ON products(product_id); lacks included columns, so table access needed.

4. Final Answer:

   CREATE INDEX idx_product ON products(product_id) INCLUDE (price, stock); -> Option A

5. Quick Check:

   Filter column indexed, others included [OK]

Hint: Index filter column, INCLUDE others for covering [OK]

Common Mistakes:

• Including filter columns instead of indexing them
• Indexing all columns as keys unnecessarily
• Not including selected columns causing table access