How to Create Covering Index in PostgreSQL: Syntax and Examples

In PostgreSQL, create a covering index by adding extra columns with the INCLUDE clause in the CREATE INDEX statement. This stores additional columns in the index to avoid fetching from the main table, improving query speed.

Syntax

The basic syntax to create a covering index in PostgreSQL is:

CREATE INDEX index_name ON table_name (key_column1, key_column2, ...) INCLUDE (included_column1, included_column2, ...);

Explanation:

• index_name: Name you give to the index.
• table_name: The table on which you create the index.
• key_column1, key_column2, ...: Columns used for searching or sorting.
• INCLUDE (included_column1, ...): Extra columns stored in the index only for faster data retrieval, not used for searching.

sql

CREATE INDEX idx_example ON my_table (search_column) INCLUDE (extra_column1, extra_column2);

Example

This example creates a covering index on the users table. The index uses last_name as the key column and includes first_name and email as extra columns. This helps queries filtering by last_name and selecting first_name and email to run faster without reading the full table.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

CREATE INDEX idx_users_lastname ON users (last_name) INCLUDE (first_name, email);

-- Sample query that benefits from the covering index
EXPLAIN ANALYZE SELECT first_name, email FROM users WHERE last_name = 'Smith';

Output

Index Scan using idx_users_lastname on users  (cost=0.29..8.31 rows=3 width=64) (actual time=0.010..0.012 rows=2 loops=1)
  Index Cond: ((last_name)::text = 'Smith'::text)
Planning Time: 0.123 ms
Execution Time: 0.030 ms

Common Pitfalls

• Not using INCLUDE for columns that are only selected but not filtered or sorted, which misses the benefit of a covering index.
• Including too many columns in INCLUDE can increase index size and slow down writes.
• Trying to use INCLUDE columns for filtering or ordering does not work; only key columns can be used for that.

sql

/* Wrong: Including columns in key list that are only selected, causing larger index and slower writes */
CREATE INDEX idx_wrong ON users (last_name, first_name, email);

/* Right: Use INCLUDE for columns only selected, not filtered */
CREATE INDEX idx_right ON users (last_name) INCLUDE (first_name, email);

Quick Reference

Feature	Description
CREATE INDEX	Command to create an index on a table
Key Columns	Columns used for searching and sorting
INCLUDE Columns	Extra columns stored in the index for faster retrieval, not used for searching
Use Case	Speeds up queries that select extra columns but filter on key columns
Limitations	INCLUDE columns cannot be used in WHERE or ORDER BY clauses

Key Takeaways

Use the INCLUDE clause in CREATE INDEX to add extra columns for covering indexes in PostgreSQL.

Covering indexes speed up queries by avoiding table lookups for included columns.

Only key columns can be used for filtering and sorting; included columns are for retrieval only.

Avoid including too many columns to keep the index size manageable.

Covering indexes are especially useful for queries that select additional columns beyond the filter keys.