CodeBlanks Logo Icon CodeBlanks
Login Register

Covering index

Cover all columns with one index.

Covering index

A covering index holds every column a query needs. In PostgreSQL the INCLUDE clause adds extra columns to the leaf nodes.

CREATE INDEX idx_orders_cust
ON orders(customer_id)
INCLUDE (amount);

A covering index enables index-only scan.

The index idx_orders_cust is created.

Covering index — details

If the query reads only from the index, the table is never touched. This is an index-only scan and it sharply cuts IO cost.

Practice Covering index

Create a customer_id index on orders and add the amount column with INCLUDE.

Starter code

query.sql
-- covering index
CREATE INDEX idx_orders_cust ON orders(customer_id)

Sample solution

query.sql
CREATE INDEX idx_orders_cust ON orders(customer_id) INCLUDE (amount);

Expected output

CREATE INDEX

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 INCLUDE clause

Question 
CREATE INDEX idx_orders_cust ON orders(customer_id) ____ (amount)
Answer 
CREATE INDEX idx_orders_cust ON orders(customer_id) INCLUDE (amount)

Explanation: INCLUDE is the right answer.

2 What does a covering index enable?

  • Full table scan
  • Index-only scan Correct answer
  • Seq scan
  • A trigger

Explanation: A covering index enables index-only scan.

3 Covering index: match the terms

Term Meaning
covering covering
INCLUDE add
index-only index-only
leaf leaf

All lessons in Expert

1 Database design

  1. ER model
  2. Cardinality
  3. Surrogate keys
  4. Natural keys
  5. Denormalization
  6. Summary - Design

2 Index tuning

  1. B-tree structure
  2. Covering index
  3. Partial index
  4. Index selectivity
  5. Index maintenance
  6. Summary - Index tuning

3 Query optimizer

  1. What is an optimizer?
  2. Statistics
  3. Cost model
  4. Join strategies
  5. Plan cache
  6. Summary - Optimizer

4 Project: Performance tuning

  1. Scenario design
  2. Reading plans
  3. Adding indexes
  4. Rewriting queries
  5. Comparing results

5 Replication and sharding

  1. What is replication?
  2. Master-slave
  3. Sync vs async
  4. What is sharding?
  5. Shard key
  6. Summary - Replication and sharding

6 Distributed and big data

  1. Distributed queries
  2. MPP
  3. Columnar storage
  4. SQL on Hadoop
  5. SQL vs NoSQL
  6. Summary - Distributed and big data

7 Security and backup

  1. SQL injection
  2. Parameterised queries
  3. Authorisation
  4. Backup
  5. Recovery
  6. Summary - Security and backup

8 Expert final

  1. Design challenge
  2. Optimization challenge
  3. Security challenge
  4. Backup challenge