When to Use PostgreSQL B-Tree Indexes

For most PostgreSQL queries that look for an exact value, check whether values fall within a span, or return rows in order, start with a B-tree index. A B-tree is an index that keeps values in a defined order, so PostgreSQL can find matching entries or read them in sorted sequence. The PostgreSQL documentation identifies B-tree as the default index type and describes it as suitable for the most common situations.

That makes B-tree a practical first choice for queries such as “find this customer,” “show records after this date,” or “return the newest records first.” The sections below show when that choice fits, when it can help with sorting, and where another search method is needed.

Use B-tree for equality and range conditions

Create a B-tree index when a query checks a column with a defined sort order for one exact value or compares it with another value. For example, this index supports lookups by email:

CREATE INDEX users_email_idx ON users (email);

SELECT id
FROM users
WHERE email = 'alex@example.com';

B-tree also supports range conditions, which check whether a value falls between limits or matches one of several listed values. These include <, <=, >=, and >, along with BETWEEN and IN. PostgreSQL can use an index on created_at to narrow a date range:

CREATE INDEX orders_created_at_idx ON orders (created_at);

SELECT id, created_at
FROM orders
WHERE created_at >= DATE '2025-01-01'
  AND created_at <  DATE '2026-01-01';

The index works when PostgreSQL can sort the column’s data into a well-defined order. B-tree also supports IS NULL and IS NOT NULL conditions, as described in the documentation on B-tree index types.

An index does not guarantee that PostgreSQL will use it. When a query returns a large share of a table, reading the table directly can cost less than visiting the index and then fetching many rows. Check the plan for the query and data you actually have with EXPLAIN.

Use B-tree when the query needs sorted rows

A B-tree index can provide rows in order, which helps queries with ORDER BY, especially when they return only a limited number of rows. For example:

CREATE INDEX orders_created_at_idx ON orders (created_at);

SELECT id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20;

PostgreSQL can scan a B-tree index forward or backward, so an ascending index can also serve a descending sort. The B-tree documentation explains that the index stores values in order, and the index type documentation describes how B-tree supports ordered retrieval.

PostgreSQL's planner, which chooses how to run a query, still compares the cost of using the index with the cost of reading rows and sorting them. For a query that returns many rows, a table scan followed by a sort can be faster. Use EXPLAIN to see whether PostgreSQL chose an index scan and whether that plan suits the query’s actual workload.

Know where B-tree stops helping

A B-tree works well for a string prefix search such as name LIKE 'Al%', because matching values begin in a contiguous part of the sorted index. A leading wildcard, as in name LIKE '%son', does not identify such a range, so a standard B-tree cannot efficiently locate those matches. PostgreSQL also notes that B-tree pattern matching requires the appropriate operator class, which specifies which comparisons the index supports, when the database does not use the C locale. Check the documented pattern-matching conditions before relying on an index for LIKE or regular-expression searches.

B-tree also does not fit full-text search, geometric objects, or multidimensional searches. Those queries need an index method designed for their search operation; PostgreSQL’s index type overview describes the available methods and their intended uses.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com