Indexes

Objectives

By the end of this lesson, you should be able to:

• Explain what an index is and why it speeds up lookups
• Create an index with CREATE INDEX
• Remove an index with DROP INDEX
• List a table’s indexes with pg_indexes

💡 Why this matters: Without help, finding “every employee in Engineering” means PostgreSQL checking every single row in the table, fine for 8 rows, painfully slow for 8 million. An index gives PostgreSQL a fast shortcut, similar to a book’s index letting you skip straight to a topic instead of reading every page.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier lessons.

Indexes That Already Exist

SELECT indexname FROM pg_indexes WHERE tablename = 'employees';

       indexname
-------------------------
 employees_pkey
 employees_email_key

Two indexes already exist, without ever explicitly creating either one. Both PRIMARY KEY and UNIQUE constraints (Module 3, Module 5) automatically create an index behind the scenes, this is precisely how PostgreSQL enforces uniqueness efficiently, checking a new value against an index is far faster than scanning every existing row.

Creating an Index

CREATE INDEX idx_employees_department ON employees(department);

SELECT indexname FROM pg_indexes WHERE tablename = 'employees';

        indexname
----------------------------
 employees_pkey
 employees_email_key
 idx_employees_department

CREATE INDEX index_name ON table(column) builds an index on department, speeding up any query that filters or sorts by it (WHERE department = 'Engineering', ORDER BY department). idx_{table}_{column} is a common naming convention, not a requirement, unlike a constraint’s default name (Module 5), an index name here has to be chosen explicitly.

What an Index Costs

An index isn’t free: it takes up disk space, and every INSERT, UPDATE, or DELETE on the table has to also update the index, some write overhead in exchange for faster reads. This is why indexes are added deliberately, on columns actually queried often, rather than on every column by default.

Dropping an Index

DROP INDEX idx_employees_department;

SELECT indexname FROM pg_indexes WHERE tablename = 'employees';

       indexname
-------------------------
 employees_pkey
 employees_email_key

DROP INDEX removes it, the underlying data in employees is completely unaffected, only the fast lookup shortcut disappears, queries still work afterward, just without whatever speed benefit the index provided.

Try It

1. Create an index on the hire_date column of employees.
2. Query pg_indexes for the employees table and confirm the new index appears.
3. Drop the index you just created, and confirm it’s gone from pg_indexes.
4. Explain, in your own words, why every column in a large table isn’t simply indexed by default.

Recap

• An index is a fast lookup structure PostgreSQL can use instead of scanning every row.
• PRIMARY KEY and UNIQUE constraints automatically create an index, this is how they enforce uniqueness efficiently.
• CREATE INDEX name ON table(column) builds an index explicitly, DROP INDEX name removes it.
• Indexes speed up reads but add overhead to writes and use disk space, so they’re added deliberately rather than universally.

Next lesson: EXPLAIN and EXPLAIN ANALYZE, seeing exactly how PostgreSQL plans to run a query.