Explain the process of creating and using an index on a table column.

Instruction: Describe how to create an index on a table column and explain how it improves query performance.

Context: This question evaluates the candidate's understanding of the importance of indexes in database optimization and their capability to implement indexing in SQL.

Updated

Official answer

For a disposable PostgreSQL table practice_employees(id integer PRIMARY KEY, last_name text), create an ordinary B-tree index and inspect a matching query:

CREATE INDEX practice_employees_last_name_idx
  ON practice_employees (last_name);

EXPLAIN
SELECT id, last_name
FROM practice_employees
WHERE last_name = 'Ng';

The planner decides whether to use the index. A small table or a predicate matching many rows may favor a sequential scan. An index does not change the query's meaning or guarantee faster execution.

Measure representative workloads and inspect estimates, selectivity and actual resource use. EXPLAIN alone does not execute the SELECT; EXPLAIN ANALYZE does execute its statement, so understand that distinction before applying it to data-changing SQL.

Indexes consume storage and require maintenance during writes. A normal CREATE INDEX can block writes while it builds. PostgreSQL's CONCURRENTLY option changes the locking and operational tradeoffs and has restrictions; do not add it mechanically. Plan the operation for the actual database, workload and failure recovery.

Sources

PostgreSQL: CREATE INDEX; EXPLAIN.

Related Questions