Covering indexes with INCLUDE in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a covering index in PostgreSQL?

A covering index is an index that contains all the columns needed to satisfy a query, so the database can get the data directly from the index without looking up the main table.

beginner

What does the INCLUDE clause do in a PostgreSQL index?

The INCLUDE clause adds extra columns to the index as non-key columns. These columns are stored in the index but not used for sorting or searching, allowing queries to be covered by the index.

intermediate

Why use INCLUDE columns instead of adding them as key columns in an index?

INCLUDE columns do not affect the index's sort order or size as much as key columns do. They help cover queries without slowing down index scans or increasing index maintenance cost significantly.

beginner

Write a PostgreSQL command to create an index on column 'user_id' and include 'email' and 'created_at' columns.

CREATE INDEX idx_user_id_include ON users (user_id) INCLUDE (email, created_at);

beginner

How does a covering index improve query performance?

It allows the database to answer queries using only the index without accessing the main table, reducing disk reads and speeding up query execution.

What is the main benefit of using INCLUDE columns in a PostgreSQL index?

A They make the index smaller by removing key columns.

B They allow the index to cover more queries without increasing sort complexity.

C They speed up data insertion by skipping index updates.

D They automatically create foreign key constraints.

INCLUDE columns add extra data to the index to cover queries without affecting the index's sort order or complexity.

Which of these is true about columns added with INCLUDE in PostgreSQL indexes?

A They replace the key columns in the index.

B They are used for sorting and searching in the index.

C They cannot be used to cover queries.

D They are stored in the index but not used for sorting.

INCLUDE columns are stored in the index to cover queries but do not affect sorting or searching.

Which SQL command creates a covering index on 'order_id' including 'order_date' and 'customer_id'?

A CREATE INDEX idx_order ON orders (order_id, order_date, customer_id);

B CREATE INDEX idx_order ON orders (order_date, customer_id) INCLUDE (order_id);

C CREATE INDEX idx_order ON orders (order_id) INCLUDE (order_date, customer_id);

D CREATE INDEX idx_order ON orders INCLUDE (order_id, order_date, customer_id);

The key column is 'order_id' and 'order_date' and 'customer_id' are included as non-key columns.

What happens if a query can be answered entirely from a covering index?

A The database uses only the index, avoiding main table access.

B The query runs slower because indexes are ignored.

C The database reads the main table to get missing data.

D The database rebuilds the index before running the query.

Covering indexes allow queries to be answered using only the index, which is faster.

Which PostgreSQL version introduced the INCLUDE clause for indexes?

A PostgreSQL 11

B PostgreSQL 9.2

C PostgreSQL 8.4

D PostgreSQL 13

The INCLUDE clause for indexes was introduced in PostgreSQL 11.

Explain what a covering index is and how the INCLUDE clause helps create one in PostgreSQL.

Think about how indexes can store extra columns to answer queries fully.

Describe the difference between key columns and INCLUDE columns in a PostgreSQL index.

Consider how sorting and searching work in indexes.