Optimizing PostgreSQL chevron_right Covering Indexes in PostgreSQL: The INCLUDE Clause and Index-Only Scans

Covering Indexes in PostgreSQL: The INCLUDE Clause and Index-Only Scans

calendar_today schedule 7 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Explain what covering indexes are and how the INCLUDE clause works in PostgreSQL
  • Identify scenarios where covering indexes provide significant performance benefits
  • Design, create, and analyze covering indexes for real-world queries

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever wondered why some queries are still slow even though you have an index? What if you could make an index that contains all the data your query needs, so PostgreSQL never has to look at the table at all?

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Understanding of basic, multi-column, and expression indexes; SQL and PostgreSQL basics
  • Materials Needed: PostgreSQL instance, SQL client (psql, DBeaver, pgAdmin, etc.)

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. What are covering indexes and how does the INCLUDE clause work?
  2. Use cases and benefits of covering indexes
  3. Designing and testing covering indexes for real queries
  4. Hands-on practice and exercises
  5. Common pitfalls and how to avoid them

Success Criteria:

By the end of this session, you should be able to design, create, and analyze covering indexes for a variety of query patterns, and understand their impact on performance and query plans.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is a Covering Index?

A covering index is an index that contains all the columns needed to satisfy a query, so PostgreSQL can return results using only the index, without reading the table (heap) at all. This is called an index-only scan.

Real-World Analogy: Imagine a summary sheet that contains all the information you need for a report, so you never have to look at the original documents.

Why This Matters: Index-only scans are much faster than regular index scans because they avoid expensive table lookups. Covering indexes can dramatically speed up read-heavy queries.

The INCLUDE Clause

The INCLUDE clause in PostgreSQL allows you to add non-key columns to an index. These columns are not used for searching or sorting, but are available for index-only scans.

  • Key columns: Used for searching and ordering
  • Included columns: Only stored in the index for fast retrieval

Key Terminology

  • Covering Index: An index that contains all columns needed by a query
  • INCLUDE Clause: Adds extra columns to an index for retrieval, not for searching
  • Index-Only Scan: A query plan that reads only the index, not the table

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Covering Indexes and the INCLUDE Clause

Covering indexes are most useful for queries that filter on one set of columns but also need to return additional columns. The INCLUDE clause makes it possible to keep the index small and efficient while still supporting index-only scans.

Practical Applications

  • Reporting queries that select a few columns but filter on others
  • Frequently accessed dashboards or API endpoints
  • Read-heavy workloads where minimizing disk I/O is critical

Common Misconceptions

  1. Misconception: All indexes automatically support index-only scans. Reality: Only indexes that contain all required columns (including those in SELECT) can be used for index-only scans.

  2. Misconception: The order of included columns matters for searching. Reality: Included columns are not used for searching or ordering, only for retrieval.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Creating and Using a Covering Index

-- Create a sample table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total NUMERIC(10,2) NOT NULL
);

-- Insert sample data
INSERT INTO orders (customer_id, status, created_at, total)
SELECT (random()*1000)::int, CASE WHEN g % 5 = 0 THEN 'pending' ELSE 'completed' END, NOW() - (g || ' minutes')::interval, (random()*500)::numeric(10,2)
FROM generate_series(1, 100000) AS g;

-- Create a covering index for index-only scans
CREATE INDEX idx_orders_status_created_total ON orders(status) INCLUDE (created_at, total);

-- Query that can use index-only scan
EXPLAIN ANALYZE SELECT created_at, total FROM orders WHERE status = 'pending';

Explanation:

  • The index is used for filtering on status and retrieving created_at and total without accessing the table.

Example 2: Multi-Column Index with INCLUDE

-- Create a multi-column index with INCLUDE
CREATE INDEX idx_orders_customer_status_total ON orders(customer_id, status) INCLUDE (total);

-- Query using the index
EXPLAIN ANALYZE SELECT total FROM orders WHERE customer_id = 42 AND status = 'completed';

Key Points:

  • The index supports filtering on customer_id and status, and retrieving total efficiently.
  • The order of included columns does not affect search performance.

Example 3: Comparing Index-Only Scan vs. Regular Index Scan

-- Query that cannot use index-only scan (missing column in index)
EXPLAIN ANALYZE SELECT created_at, total, status FROM orders WHERE customer_id = 42;

-- Add a covering index
CREATE INDEX idx_orders_customer_all ON orders(customer_id) INCLUDE (created_at, total, status);

-- Query now uses index-only scan
EXPLAIN ANALYZE SELECT created_at, total, status FROM orders WHERE customer_id = 42;

Debugging Demonstration

Show a common mistake: forgetting to include all needed columns.

-- Index missing a column needed by the query
CREATE INDEX idx_orders_status_only ON orders(status);
EXPLAIN ANALYZE SELECT created_at FROM orders WHERE status = 'pending';
-- The query cannot use index-only scan.

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Create and use a covering index for a reporting query.

Instructions:

  1. Create a table called products with columns id, category, name, and price.
  2. Insert 10,000 sample products.
  3. Create a covering index on category and INCLUDE name and price.
  4. Query for all products in a category, selecting name and price, and compare performance with and without the index.

Starter Code:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category TEXT NOT NULL,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

INSERT INTO products (category, name, price)
SELECT 'Category ' || (g % 10), 'Product ' || g, (random() * 100)::numeric(10,2)
FROM generate_series(1, 10000) AS g;

Solution:

CREATE INDEX idx_products_category_cover ON products(category) INCLUDE (name, price);
EXPLAIN ANALYZE SELECT name, price FROM products WHERE category = 'Category 5';

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Optimize a query for index-only scans using INCLUDE.

Requirements:

  • Create a multi-column index with INCLUDE for a query filtering on two columns and selecting a third.
  • Test queries with and without the covering index.
  • Observe the query plan and performance.

Hints:

  • Use EXPLAIN ANALYZE to check for index-only scans.
  • Try different combinations of key and included columns.

Exercise 3: Creative Application (15 minutes)

Objective: Design a covering index strategy for a dashboard query.

Challenge: Suppose you need to generate a dashboard showing recent orders for a specific customer, displaying created_at, status, and total. What covering index would you create? Test your strategy and measure performance.

Extension Ideas:

  • Try adding more columns to the INCLUDE clause and see the effect on index size and performance.
  • Experiment with covering indexes on other tables or queries.

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Not including all columns needed for index-only scans. Cause: The query cannot use index-only scan, leading to slower performance. Solution: Ensure your index includes all columns needed by the query.

  2. Problem: Overusing INCLUDE for rarely selected columns. Cause: Unnecessary index bloat and maintenance overhead. Solution: Only include columns that are frequently selected together.

Debugging Tips

  • Use EXPLAIN and EXPLAIN ANALYZE to check for index-only scans.
  • Check index definitions with \di+ in psql.
  • Use pg_stat_user_indexes to monitor effectiveness.

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Use covering indexes for read-heavy queries that select a few columns.
  • Keep the number of included columns minimal to avoid index bloat.
  • Regularly review and drop unused covering indexes.

Next Steps in Your Learning Journey

  • Learn about specialized index types (GIN, GiST, BRIN)
  • Explore advanced query tuning and index maintenance
  • Practice analyzing and tuning real-world queries

9. Summary and Review (5 minutes)

Key Takeaways

  1. Covering indexes enable fast index-only scans for queries that select all needed columns from the index.
  2. The INCLUDE clause adds non-key columns for retrieval, not for searching.
  3. Always analyze and test your covering indexes with real queries.

Quick Review Questions

  1. When should you use a covering index with INCLUDE?
  2. How does the INCLUDE clause affect index-only scans?
  3. How can you verify if a query is using an index-only scan?

For Instructors

Teaching Tips

  • Use live demos to show the impact of covering indexes and index-only scans.
  • Encourage students to experiment with different combinations of key and included columns.
  • Discuss trade-offs between index size and query performance.

Assessment Ideas

  • Quiz: Identify which queries would benefit from a covering index.
  • Project: Optimize a sample database for a set of reporting queries.
  • Peer review: Analyze and critique each other’s covering index strategies.

Adaptation Notes

  • For beginners, focus on simple covering indexes and queries.
  • For advanced students, introduce multi-column and complex covering indexes.
  • In online settings, use shared SQL sandboxes or cloud PostgreSQL instances for exercises.