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.