INDEX and EXPLAIN

An index helps PostgreSQL find rows faster. It works like the index at the back of a book. Instead of reading every page, you look up a word and jump straight to the right page.

Indexes help most on columns that you often use in WHERE, JOIN and ORDER BY.


Syntax

CREATE INDEX index_name
ON table_name (column_name);

To remove an index:

DROP INDEX index_name;

Example: Create an Index on Name

CREATE INDEX idx_name
ON employees (name);

Output:

CREATE INDEX

You can see the new index with \d employees, at the bottom, under "Indexes":

\d employees

Output:

                      Table "public.employees"
   Column   |         Type          | Collation | Nullable | Default 
------------+-----------------------+-----------+----------+---------
 id         | integer               |           | not null | 
 name       | character varying(50) |           |          | 
 age        | integer               |           |          | 
 department | character varying(50) |           |          | 
 gender     | character varying(50) |           |          | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)
    "idx_name" btree (name)

The employees_pkey index was already there. PostgreSQL creates an index for every primary key by itself.


See How a Query Runs with EXPLAIN

Put EXPLAIN before a query to see the plan: the steps PostgreSQL will take to run it. (COSTS OFF hides some numbers that we don't need here.)

EXPLAIN (COSTS OFF)
SELECT * FROM employees WHERE name = 'Alice';

Output:

                QUERY PLAN                
------------------------------------------
 Seq Scan on employees
   Filter: ((name)::text = 'Alice'::text)
(2 rows)

Seq Scan means PostgreSQL reads every row of the table. It did not use the new index! With only 20 rows, reading them all is faster than using an index, so PostgreSQL chose not to.

Indexes matter for big tables. This example creates a table with 100,000 rows. (generate_series(1, 100000) makes the numbers from 1 to 100,000.)

CREATE TABLE visits AS
SELECT g AS id, 'user' || g AS username
FROM generate_series(1, 100000) AS g;

CREATE INDEX idx_visits_id ON visits (id);
ANALYZE visits;

EXPLAIN (COSTS OFF)
SELECT * FROM visits WHERE id = 500;

Output:

SELECT 100000
CREATE INDEX
ANALYZE
                QUERY PLAN                
------------------------------------------
 Index Scan using idx_visits_id on visits
   Index Cond: (id = 500)
(2 rows)

Index Scan means PostgreSQL used the index to jump straight to the row. ANALYZE tells PostgreSQL about the new data, so it can choose a good plan.


Index on Several Columns

You can create one index on more than one column:

CREATE INDEX idx_dept_age
ON employees (department, age);

This helps queries that filter by department, or by department and age together.


Key Points

  • Indexes make reading faster.
  • But every insert, update and delete must also update the indexes, so too many indexes slow down writes.
  • Add indexes for the columns your queries use most.

In the exercise, create an index on the department column.