Performance basics

In plain English

EXPLAIN ANALYZE is a statement that runs your query and prints a plan showing what the database actually did and how long it took. A sequential scan reads every row in a table, while an index scan uses an index, a sorted lookup structure, to jump straight to the matching rows. A missing index means the database has no shortcut for your query, so it falls back to reading everything. VACUUM reclaims space from deleted rows and keeps tables healthy, while ANALYZE refreshes the statistics the planner uses to choose a good plan.

Step by step

1. 1Run explain analyze in front of your query to see its plan.
2. 2Look for a Seq Scan, which means every row is being read.
3. 3Create an index on the columns in your where clause.
4. 4Run analyze so the planner knows about your data.
5. 5Run vacuum to reclaim space from deleted and updated rows.

create table lessons (
  id integer generated always as identity primary key,
  course_id integer not null,
  title text not null
);

explain analyze
select title
from lessons
where course_id = 7;

create index lessons_course_idx on lessons (course_id);

analyze lessons;

vacuum lessons;

Common mistakes

• Reading a plan from EXPLAIN without ANALYZE can mislead you, because the row estimates are guesses until the query actually runs.
• Adding an index to a column no query filters on wastes storage and slows every insert.
• Skipping vacuum lets dead rows pile up and tables bloat, which makes scans slower.
• Assuming an index always wins is wrong, because on small tables a sequential scan is often cheaper.