Postgres Indexes Explained: What They Are and When to Use Them

by Souy Soeng /October 13, 2026

Diagram of a Postgres B-tree index leading from root to branch to leaf pages and pointing to a row in the table

Postgres indexes are separate lookup structures that help the database find rows without reading the whole table. If you have ever had a page that felt instant on launch day and slow a few months later, an index is often part of the fix.

This topic also comes up in almost every Postgres interview, usually as one question: what is an index, and when should you use one? This article answers both halves. It walks from the basic idea to what happens inside Postgres, then covers where indexes help, what they cost, and the mistakes that trip people up.

In short

An index is a separate structure of sorted values and pointers that leads Postgres to matching rows.
Without one, Postgres often has to run a sequential scan and check every row.
CREATE INDEX builds a B-tree by default, and lookups walk down it in a few steps.
The query planner decides whether to use an index, and EXPLAIN shows you its choice.
Indexes speed up reads but cost storage and slow down writes, so add them deliberately.

Why does a fast Postgres app slow down over time?

Picture an online shop. A shopper opens their order history, the app server sends a SQL query to Postgres, and Postgres has to find that shopper's rows in the orders table. On launch day this feels instant. Months later the same page takes seconds to load, even though nobody changed the code. Only the amount of data changed.

With a few hundred rows, reading everything costs almost nothing, so nobody notices a problem. As the table grows, Postgres may have to read far more rows to answer the same question, and users feel the lag. This is why slow queries often hide during development and only show up in production, where the real data lives.

What is an index in Postgres?

An index is a separate structure, stored alongside your table, that helps Postgres find rows without scanning the whole table. It works like the index at the back of a textbook. You don't read every page to find one topic. You check the index, and it tells you which page to turn to.

Say a query asks for one email address. Instead of reading the users table, Postgres checks an email index, which holds the email values in sorted order. Each entry points to the matching row. The index stores only the indexed values plus pointers, so it is smaller and easier to search than the table itself.

An index doesn't copy or replace your data. It is a map that tells Postgres where to look, and Postgres still visits the table to read the full row. In an interview, a definition this plain shows you understand the idea rather than repeating a textbook line.

Separate from the table: its own object on disk
Holds indexed values plus pointers to rows
Points to data; it does not replace it

Why full table scans get slow

Without an index, Postgres often has only one option: check every row. This is called a sequential scan. It reads a chunk of rows from disk, tests each one against your WHERE filter, and keeps the few that match. The number of rows scanned climbs much faster than the number matched.

The cost grows with the table. Double the rows and a scan has roughly double the work, even if you only want one row. That is why a table that was fine last year can feel slow today. An index lets Postgres jump close to the matching rows instead, and the bigger the table, the bigger that difference becomes.

How CREATE INDEX builds a B-tree

Creating an index takes a single command. Here, CREATE INDEX idx_users_email names the index, and ON users (email) picks the table and column. The last lines show the same index with USING btree written out. You don't need to type that part, because B-tree is the default type.

Postgres supports other index types, but the B-tree is the one you will use most and the one interviewers expect you to know. It is a balanced tree, meaning it stays evenly shaped as you insert and delete data, so lookups stay quick wherever a value sits. It also keeps values sorted, like names in a phone book, which makes fast lookups and range searches possible.

# Index the email column
CREATE INDEX idx_users_email
  ON users (email);
# Same type, written out in full:
# ... ON users USING btree (email);

How does a B-tree lookup work?

A B-tree search doesn't check every value. It walks from the top down, making one small decision at each level, a bit like a guessing game where someone answers higher or lower. Every answer throws away a large share of the possibilities.

The search starts at the root page, which splits values into broad ranges. Postgres compares your value against a few boundaries and follows the right pointer to a branch page with a narrower range, then to a leaf page holding sorted values. The leaf points to the row in the table. Because each level divides the data, even a huge table needs only a handful of levels.

Does Postgres always use an index?

No. Creating an index doesn't force Postgres to use it. Each time a query runs, a part of Postgres called the planner works out the cheapest way to get the answer. It considers options such as a full scan or an index scan and estimates the cost of each, using statistics it keeps about your data.

If the filter matches only a few rows, the index usually wins. If it matches a large share of the table, a plain scan can be cheaper, so Postgres picks that. Mentioning this in an interview shows you know an index is an option, not an order.

When the planner does choose the index, a lookup for ana@mail.com goes like this: the plan is an Index Scan, Postgres walks the B-tree from root to leaf, gets a pointer to the row's location, and reads one table row. It touches a few index pages and one row instead of the whole table.

When should you use an index?

There are four classic situations where indexes pay off, and naming them is a strong answer to the "when" half of the interview question. The most common is a WHERE filter on a column, such as an email lookup. Joins benefit too: an index on a join column like customer_id makes matching orders to customers far cheaper.

Because a B-tree keeps values sorted, Postgres can sometimes read rows in order straight from the index and skip a separate sort for ORDER BY. Unique constraints rely on an index to check for duplicates quickly, and primary keys get one automatically, which is why lookups by id are usually fast.

Back to the slow order history page. Its query selects orders where customer_id equals the logged-in customer, but that column had no index, so every page view read the entire orders table. One CREATE INDEX on customer_id lets the planner choose an index scan and read only that shopper's rows. Same code, same query, a smarter path to the data.

WHERE filters: jump to matching rows
JOIN conditions: match rows across tables cheaply
ORDER BY: read rows already sorted
Unique constraints: check duplicates on every insert

How to check if Postgres uses your index with EXPLAIN

Don't guess whether an index is working. Ask Postgres with EXPLAIN, placed in front of a normal SELECT, here with the same customer_id filter. It shows the plan Postgres will use. A good sign is Index Scan using your index name. Related types such as Bitmap Index Scan also mean the index is being used.

A warning sign is Seq Scan on orders, meaning the whole table gets read. When you see it, ask why: the table may be tiny, the filter may match too many rows, or the index may be on the wrong column. EXPLAIN turns guessing into a clear answer, and mentioning it shows you test your ideas.

# Ask Postgres for its plan
EXPLAIN SELECT * FROM orders
  WHERE customer_id = 42;
# Good sign:
#   Index Scan using idx_orders_cust
# Warning sign: Seq Scan on orders

Index trade-offs and common mistakes

Indexes aren't free. Without one, only the table changes on a write; with one, every insert and update must also maintain the index. An index uses extra disk space and grows with your data. The reward is faster reads, so add an index when a column is often filtered, joined or sorted and the table is large enough to matter.

The most common mistake is indexing every column. It feels safe, but writes slow down and the disk fills with indexes that rarely get used. The second is ignoring write overhead on busy tables such as logs or events. The third is skipping EXPLAIN: run it on the real query, and check again as your data grows.

Only index columns your queries filter, join or sort on
Remove indexes that aren't pulling their weight
Consider write load before adding an index
Confirm with EXPLAIN instead of assuming

Key takeaways

An index is a separate lookup structure of sorted values and pointers.
It helps Postgres avoid slow sequential scans on large tables.
The default type is a B-tree, which stays balanced and keeps values sorted.
The planner picks the cheapest plan, so an index is an option, not an order.
Indexes help WHERE, JOIN, ORDER BY and unique constraints.
Reads get faster, writes get slower: index what you query, then check with EXPLAIN.

Frequently asked questions

What is the default index type in Postgres?

The default is a B-tree. When you run CREATE INDEX without naming a type, Postgres builds a B-tree, which keeps values sorted and supports fast lookups and range searches.

Why isn't Postgres using my index?

The planner chooses whichever plan it estimates is cheapest. If the table is tiny, the filter matches a large share of rows, or the index is on a different column than the query uses, a sequential scan may win.

Do indexes slow down inserts and updates?

Yes. Every insert and update has to maintain each index on the table as well as the table itself. On write-heavy tables like logs or events, each extra index adds a real cost.

Should I index every column in a table?

No. Unused indexes still cost storage and slow every write. Index only the columns your queries actually filter, join or sort on.

How do I check whether a query uses an index?

Put EXPLAIN in front of the query. If the plan shows Index Scan, or a related type like Bitmap Index Scan, with your index name, the index is being used; Seq Scan means the whole table is read.

Do primary keys need a separate index?

Postgres creates an index for a primary key automatically, which is why lookups by id are usually fast. Unique constraints also rely on an index behind the scenes.

Watch the full video on YouTube →

Tag

Ad