As a PostgreSQL database grows, queries that were once fast can become slower.

For example, this query may work quickly with a few hundred rows:

SELECT *
FROM users
WHERE email = 'oliver@example.com';

But if the users table contains millions of records, PostgreSQL may need to inspect a large number of rows to find the matching record.

This is where indexes become important.

A PostgreSQL index is a data structure that helps the database find rows more efficiently without scanning the entire table.

Indexes can dramatically improve query performance, but they also come with costs. They use storage and can make INSERT, UPDATE, and DELETE operations more expensive.

In this beginner-friendly guide, we’ll understand PostgreSQL indexing from the basics.

We’ll cover:

What an index is
Why indexes are important
How PostgreSQL searches without an index
How indexes work
Creating indexes
B-tree indexes
Composite indexes
Unique indexes
Partial indexes
Expression indexes
GIN indexes
GiST indexes
BRIN indexes
Index-only scans
Index usage
EXPLAIN
EXPLAIN ANALYZE
Common indexing mistakes
PostgreSQL indexing best practices

What Is a PostgreSQL Index?

An index is a separate database structure that helps PostgreSQL locate rows more efficiently.

Imagine a table containing:

users
--------------------------------
id | name   | email
--------------------------------
1  | Oliver | oliver@example.com
2  | Sophia | sophia@example.com
3  | Ethan  | ethan@example.com
...

Suppose you frequently search by email:

SELECT *
FROM users
WHERE email = 'oliver@example.com';

An index on email can help PostgreSQL locate the matching row more efficiently.

Conceptually:

Query
  ↓
Index
  ↓
Matching Row

Without an appropriate index, PostgreSQL may need to perform a sequential scan.

Why Are Indexes Important?

Imagine a table with:

10 rows

Searching through all rows isn’t a big problem.

Now imagine:

10 million rows

Scanning every row for every query can become expensive.

An index can reduce the amount of data PostgreSQL needs to examine.

For example:

Without Index

Query
  ↓
Scan many rows
  ↓
Find matching rows

With an appropriate index:

Query
  ↓
Index
  ↓
Locate matching rows

This is especially useful for frequently executed queries.

How PostgreSQL Searches Without an Index

Suppose you have:

SELECT *
FROM users
WHERE email = 'oliver@example.com';

If there is no suitable index, PostgreSQL may use a Sequential Scan.

Conceptually:

Row 1 → Check
Row 2 → Check
Row 3 → Check
Row 4 → Check
...
Row N → Check

For a large table, this can require examining many rows.

However, a sequential scan isn’t always bad.

If the query needs a large percentage of the table, scanning the table sequentially can actually be more efficient than using an index.

The PostgreSQL query planner decides which strategy is estimated to be cheaper.

How an Index Changes the Query

Suppose we create:

CREATE INDEX idx_users_email
ON users(email);

Now PostgreSQL has an additional structure that can help locate rows matching an email.

Conceptually:

users table
     │
     └── email index
            │
            └── matching row locations

PostgreSQL’s planner can choose an index scan or another suitable strategy when it estimates that using the index will be beneficial.

Creating an Index

The basic syntax is:

CREATE INDEX index_name
ON table_name(column_name);

For example:

CREATE INDEX idx_users_email
ON users(email);

Now the database has an index on:

users.email

Checking Existing Indexes

In psql, you can inspect a table with:

\d users

This can show table columns, constraints, and indexes.

You can also query PostgreSQL’s system catalogs and views when you need more detailed index information.

The Default Index Type: B-tree

The most commonly used PostgreSQL index type is the B-tree.

If you create:

CREATE INDEX idx_users_email
ON users(email);

PostgreSQL uses a B-tree by default unless another index method is specified.

B-tree indexes are suitable for many common operations, including:

=
<
>
<=
>=
ORDER BY

They are the first index type you should understand as a beginner.

B-tree Example

Suppose you frequently run:

SELECT *
FROM products
WHERE price > 500;

An index can be created:

CREATE INDEX idx_products_price
ON products(price);

Now PostgreSQL can potentially use the index to locate products matching the condition.

B-tree indexes are also useful for many equality queries:

SELECT *
FROM users
WHERE id = 101;

Indexes and `ORDER BY`

Indexes can sometimes help PostgreSQL satisfy ordering requirements.

For example:

SELECT *
FROM products
ORDER BY price;

An index on:

CREATE INDEX idx_products_price
ON products(price);

may help avoid a separate sorting step, depending on the query and planner’s cost estimates.

Unique Indexes

A unique index prevents duplicate values.

For example:

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Now PostgreSQL will prevent duplicate non-null email values.

However, when uniqueness is a fundamental data-integrity rule, a UNIQUE constraint is often the clearer schema-level choice:

ALTER TABLE users
ADD CONSTRAINT users_email_unique
UNIQUE (email);

PostgreSQL uses an underlying unique index to enforce the constraint.

Primary Keys and Indexes

When you create a primary key:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT
);

PostgreSQL automatically creates a unique B-tree index to enforce the primary key.

Therefore, you normally don’t need to create another index on the same primary-key column.

Foreign Keys and Indexes

A foreign key does not automatically create an index on the referencing column.

For example:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id)
);

PostgreSQL creates the necessary unique index for the referenced primary key, but it does not automatically create an index on:

orders.user_id

You may want one:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

This can be useful for queries such as:

SELECT *
FROM orders
WHERE user_id = 101;

It can also help certain joins and operations involving the foreign-key column.

Composite Indexes

A composite index contains multiple columns.

For example:

CREATE INDEX idx_users_country_name
ON users(country, name);

This creates one index on:

country + name

Composite indexes are useful when queries commonly filter or sort using multiple columns.

For example:

SELECT *
FROM users
WHERE country = 'India'
AND name = 'Oliver';

Column Order Matters

The order of columns in a composite index is important.

Consider:

CREATE INDEX idx_users_country_name
ON users(country, name);

This index is generally most useful when the query uses the leading column:

country

or a combination such as:

country + name

It isn’t equivalent to having separate indexes on:

country
name

A useful mental model is:

(country, name)
       ↑
Leading column

Design composite indexes based on your actual query patterns.

Example of Composite Index Usage

Suppose your application frequently runs:

SELECT *
FROM orders
WHERE user_id = 101
AND status = 'completed';

A possible index is:

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

The index matches the filtering pattern.

But don’t create composite indexes blindly. Query frequency, selectivity, ordering, table size, and write workload all matter.

Partial Indexes

A partial index indexes only rows that satisfy a condition.

For example:

CREATE INDEX idx_active_users_email
ON users(email)
WHERE active = true;

Instead of indexing every user, PostgreSQL indexes only active users.

This can be useful when a query frequently targets a relatively small subset of a table.

For example:

SELECT *
FROM users
WHERE active = true
AND email = 'oliver@example.com';

Why Partial Indexes Can Be Useful

Suppose you have:

10 million users

but only:

500,000 active users

An index only on active users can be smaller than a full-table index.

Conceptually:

All Users
   │
   └── Partial Index
          ↓
     Active Users

This can reduce index size and maintenance work.

Expression Indexes

Sometimes you don’t search directly on a column.

For example:

SELECT *
FROM users
WHERE LOWER(email) = 'oliver@example.com';

A normal index on email may not be enough for this expression.

You can create an expression index:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

Now PostgreSQL has an index specifically for the expression.

Expression indexes are useful when queries repeatedly apply the same function or expression.

Case-Insensitive Searches

A common use case is case-insensitive email searching.

Without an appropriate index:

SELECT *
FROM users
WHERE LOWER(email) = 'oliver@example.com';

With:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

PostgreSQL can potentially use that expression index for the query.

The query expression and index expression need to be compatible for the planner to use the index.

GIN Indexes

GIN, or Generalized Inverted Index, is useful for certain data types and search patterns.

It is commonly used with:

JSONB
Arrays
Full-text search

For example:

CREATE INDEX idx_products_metadata
ON products
USING GIN(metadata);

where metadata is a JSONB column.

GIN and JSONB

Suppose:

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    metadata JSONB
);

And you store:

{
  "brand": "NovaTech",
  "features": ["wireless", "compact"]
}

A GIN index can be useful for certain JSONB containment queries:

SELECT *
FROM products
WHERE metadata @> '{"brand": "NovaTech"}';

This is one of the reasons PostgreSQL’s JSONB support is so powerful.

GIN and Arrays

Suppose:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    skills TEXT[]
);

You can create:

CREATE INDEX idx_users_skills
ON users
USING GIN(skills);

This can support suitable array queries.

For example:

SELECT *
FROM users
WHERE skills @> ARRAY['Java'];

GiST Indexes

GiST, or Generalized Search Tree, is another PostgreSQL index framework.

It is useful for certain specialized data types and search operations.

GiST indexes can be used for things such as:

Geospatial data
Range types
Certain full-text search scenarios
Specialized extension types

A common example is using GiST with PostGIS for spatial data.

BRIN Indexes

BRIN stands for Block Range INdex.

BRIN indexes are designed for very large tables where column values have a useful correlation with their physical storage order.

For example, consider an event table:

events
-------------------------
id
created_at
event_type

If rows are naturally inserted in chronological order, a BRIN index on created_at may be useful.

Example:

CREATE INDEX idx_events_created_at
ON events
USING BRIN(created_at);

BRIN indexes can be very small compared with traditional indexes.

They are especially interesting for huge tables with naturally ordered data.

Choosing an Index Type

A simplified guide:

Index Type	Common Use
B-tree	Equality, ranges, ordering
Hash	Equality comparisons
GIN	JSONB, arrays, inverted-style searches
GiST	Specialized and geometric/range searches
BRIN	Very large naturally ordered tables
SP-GiST	Certain specialized data structures

B-tree should be your default starting point.

Use specialized index types when your query and data model justify them.

How to Know If PostgreSQL Uses an Index

One of the most important tools is:

EXPLAIN

For example:

EXPLAIN
SELECT *
FROM users
WHERE email = 'oliver@example.com';

PostgreSQL will show the planned execution strategy.

You might see something conceptually like:

Index Scan
  using idx_users_email

This tells you the planner chose an index scan.

EXPLAIN ANALYZE

EXPLAIN shows the estimated execution plan.

EXPLAIN ANALYZE actually executes the query and reports runtime information.

For example:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'oliver@example.com';

It can show information such as:

Execution Time
Actual Rows
Planning Time
Scan Type
Index Used

This is extremely useful when investigating slow queries.

Important Warning About EXPLAIN ANALYZE

Remember:

EXPLAIN ANALYZE executes the query.

For a SELECT, this is usually straightforward.

But if you run it against an UPDATE, DELETE, or another modifying statement, the operation can actually modify data.

Be careful when using it in production environments.

Index Scan vs Sequential Scan

You may see:

Index Scan

or:

Seq Scan

Index Scan

PostgreSQL uses an index to find relevant rows.

Sequential Scan

PostgreSQL scans the table sequentially.

Don’t assume:

“Index Scan is always better.”

It isn’t.

If a query needs a large percentage of the table, a sequential scan can be cheaper.

The PostgreSQL planner makes its decision based on estimated costs.

Why PostgreSQL Might Not Use Your Index

You created an index:

CREATE INDEX idx_users_email
ON users(email);

but PostgreSQL still performs a sequential scan.

Why?

There are several possible reasons.

The Table Is Small

For a small table, scanning the entire table may be faster.

Too Many Rows Match

If the query returns a large percentage of the table, an index may not provide enough benefit.

Outdated Statistics

PostgreSQL relies on statistics when planning queries.

Running:

ANALYZE users;

can update statistics.

Query Expression Doesn’t Match

For example, an index on:

email

may not directly support:

LOWER(email)

without an appropriate expression index.

Cost Estimates

The planner may simply estimate that another plan is cheaper.

Index Selectivity

Selectivity describes how effectively a condition narrows down rows.

Suppose:

10 million rows

and a query matches:

5 rows

That’s highly selective.

An index can be very useful.

But if a query matches:

9 million rows

the index may provide little benefit compared with reading the table sequentially.

This is one reason indexing decisions should consider actual data distribution.

Indexes and Writes

Indexes improve many reads, but they aren’t free.

When you insert:

INSERT INTO users (...);

PostgreSQL must also maintain relevant indexes.

The same applies to updates and deletes.

So:

More Indexes
     ↓
Potentially Faster Reads
     +
More Write & Storage Cost

This is the central indexing trade-off.

Indexes Consume Storage

An index is stored separately from the table data.

If you create many indexes:

Table
 +
Index 1
 +
Index 2
 +
Index 3
 +
Index 4

storage usage increases.

Large indexes can also increase backup size and maintenance requirements.

Don’t Index Every Column

A common beginner mistake is:

“Indexes make queries faster, so I’ll index everything.”

That’s usually a bad idea.

Instead, index columns that are frequently used for:

Filtering
Joining
Sorting
Certain uniqueness requirements
Specific search patterns

Use actual query workloads to guide your decisions.

Indexing Foreign Keys

Foreign-key columns are often good candidates for indexes.

For example:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

This can help queries such as:

SELECT *
FROM orders
WHERE user_id = 101;

It can also help certain joins:

SELECT
    u.name,
    o.total
FROM users u
JOIN orders o
    ON o.user_id = u.id;

Again, verify actual workload and query plans.

Indexing Search Columns

If your application frequently searches by:

email
username
product_code
order_number

these columns may be good index candidates.

For example:

CREATE INDEX idx_users_username
ON users(username);

The index should reflect how the application actually queries the data.

Indexing `ORDER BY`

Suppose your application frequently runs:

SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 20;

An index such as:

CREATE INDEX idx_products_created_at
ON products(created_at DESC);

may help PostgreSQL efficiently find the newest rows.

This pattern is common in:

News feeds
Activity logs
Blog listings
Recent orders
Notifications

Indexing with `LIMIT`

Queries such as:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

can benefit from an appropriate index because PostgreSQL may be able to retrieve the first required rows without processing and sorting the entire table.

This is especially useful for large tables.

Covering Indexes and `INCLUDE`

PostgreSQL supports INCLUDE columns in indexes.

For example:

CREATE INDEX idx_users_email
ON users(email)
INCLUDE (name);

The indexed column is:

email

while name is stored as an included payload column.

This can sometimes allow an index-only scan, depending on the query and table visibility information.

What Is an Index-Only Scan?

An index-only scan allows PostgreSQL to satisfy a query using information available in the index without fetching every matching row from the table.

For example, an index might contain:

email
name

and the query might only need:

SELECT name
FROM users
WHERE email = 'oliver@example.com';

Under suitable conditions, PostgreSQL may be able to avoid accessing the table for each matching row.

This can improve performance.

Index Maintenance

PostgreSQL automatically maintains indexes when rows are inserted, updated, or deleted.

However, indexes can still require maintenance and monitoring.

Important concepts include:

VACUUM
ANALYZE
REINDEX

VACUUM

Helps reclaim storage from dead row versions and supports healthy database operation.

ANALYZE

Updates statistics used by the query planner.

REINDEX

Rebuilds an index when there is a reason to do so, such as certain forms of index corruption or bloat.

These operations should be performed according to PostgreSQL’s maintenance behavior and your application’s requirements.

Index Bloat

Because PostgreSQL uses MVCC, updates and deletes can leave dead row versions.

Indexes can also accumulate unused space over time.

This is commonly referred to as index bloat.

Proper vacuuming and monitoring help keep database storage and performance healthy.

Don’t automatically rebuild every index.

Investigate whether reindexing is actually necessary.

Concurrent Index Creation

Creating a large index can consume significant resources.

PostgreSQL provides:

CREATE INDEX CONCURRENTLY

For example:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

This is useful when adding indexes to production tables where reducing blocking of normal writes is important.

However, concurrent index creation has its own trade-offs and operational considerations, so it should be planned carefully.

Dropping an Unused Index

Unused indexes consume resources.

You can remove an index with:

DROP INDEX idx_users_email;

Before dropping an index in production, make sure it isn’t needed by an important query, constraint, or operational process.

How to Find Unused Indexes

PostgreSQL provides statistics views that can help identify index usage.

For example, pg_stat_user_indexes contains index usage statistics.

You can inspect it with:

SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;

A low idx_scan count doesn’t automatically mean an index is useless.

Some indexes support rare but critical queries, and statistics may reset or reflect only the observation period.

Use this information together with application knowledge.

Common Indexing Mistakes

1. Creating Too Many Indexes

Every index adds storage and maintenance overhead.

Only create indexes that provide a meaningful benefit.

2. Ignoring Query Patterns

An index should be designed around real queries.

Don’t index columns simply because they exist.

3. Using the Wrong Column Order

For composite indexes:

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

the order matters.

Don’t assume:

(user_id, status)

and:

(status, user_id)

are interchangeable.

4. Forgetting Foreign-Key Indexes

A foreign key doesn’t automatically mean the referencing column has an index.

Check common join and filtering patterns.

5. Assuming Every Query Needs an Index

Small tables and low-selectivity queries may perform better with sequential scans.

6. Ignoring `EXPLAIN ANALYZE`

Don’t guess about query performance.

Inspect the execution plan.

7. Creating an Index Without Measuring

An index should solve a real performance or integrity requirement.

Measure before and after when possible.

A Practical Indexing Workflow

A good workflow is:

1. Identify Slow Query
        ↓
2. Run EXPLAIN ANALYZE
        ↓
3. Understand Query Plan
        ↓
4. Identify Filtering / Join / Sort Columns
        ↓
5. Create Appropriate Index
        ↓
6. Run Query Again
        ↓
7. Compare Performance
        ↓
8. Monitor in Production

This is much better than randomly creating indexes.

Example: Optimizing a Slow Query

Suppose you frequently run:

SELECT *
FROM orders
WHERE user_id = 101
ORDER BY created_at DESC
LIMIT 20;

A possible index is:

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Why?

The query:

Filters by
user_id
   ↓
Orders by
created_at
   ↓
Returns
20 rows

The index reflects that access pattern.

Whether this is actually the best index should still be verified using the query plan and real workload.

PostgreSQL Indexing Best Practices

Start with B-tree

For most basic equality, range, and ordering queries, B-tree is the natural starting point.

Index Based on Queries

Design indexes around actual access patterns.

Consider Composite Indexes

Use multi-column indexes when queries commonly filter or sort using multiple columns.

Don’t Over-Index

Every additional index has a cost.

Use `EXPLAIN ANALYZE`

Measure query behavior rather than guessing.

Keep Statistics Updated

PostgreSQL relies on statistics to make good query-planning decisions.

Consider Partial Indexes

They can be useful when queries repeatedly target a specific subset of rows.

Use Specialized Index Types When Appropriate

GIN, GiST, BRIN, and other index types solve specialized problems.

Monitor Index Usage

Use PostgreSQL statistics and application monitoring to understand whether indexes are providing value.

PostgreSQL Indexing Cheat Sheet

Index Type	Best Known Uses
B-tree	Equality, ranges, sorting
Hash	Equality comparisons
GIN	JSONB, arrays, full-text search
GiST	Ranges, geometric and specialized searches
SP-GiST	Specialized partitioned search structures
BRIN	Huge tables with naturally ordered data

Important Commands

Create Index

CREATE INDEX idx_users_email
ON users(email);

Create Unique Index

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Create Composite Index

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

Create Partial Index

CREATE INDEX idx_active_users
ON users(email)
WHERE active = true;

Create Expression Index

CREATE INDEX idx_lower_email
ON users(LOWER(email));

Create GIN Index

CREATE INDEX idx_metadata
ON products
USING GIN(metadata);

Create BRIN Index

CREATE INDEX idx_events_created
ON events
USING BRIN(created_at);

Analyze a Query

EXPLAIN
SELECT *
FROM users
WHERE email = 'oliver@example.com';

Analyze Actual Execution

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'oliver@example.com';

Drop an Index

DROP INDEX idx_users_email;

Final Thoughts

PostgreSQL indexes are one of the most important tools for improving database performance.

The basic idea is simple:

Without Index
Query
  ↓
Potentially scan many rows
  ↓
Result

With Suitable Index
Query
  ↓
Index
  ↓
Relevant rows
  ↓
Result

But indexing isn’t simply about creating as many indexes as possible.

Every index has a cost:

Index
 ↓
Faster Suitable Reads
 +
More Storage
 +
More Write Maintenance

The best approach is to understand your application’s actual queries.

Start with B-tree indexes, learn how composite indexes work, understand partial and expression indexes, and then explore specialized indexes such as GIN, GiST, and BRIN.

Most importantly, use:

EXPLAIN ANALYZE

to understand what PostgreSQL is actually doing.

Good indexing is not about having more indexes. It’s about having the right indexes for the queries your application actually runs.

PostgreSQL Indexing Explained: How to Improve Query Performance

What Is a PostgreSQL Index?

Why Are Indexes Important?

How PostgreSQL Searches Without an Index

How an Index Changes the Query

Creating an Index

Checking Existing Indexes

The Default Index Type: B-tree

B-tree Example

Indexes and ORDER BY

Unique Indexes

Primary Keys and Indexes

Foreign Keys and Indexes

Composite Indexes

Column Order Matters

Example of Composite Index Usage

Partial Indexes

Why Partial Indexes Can Be Useful

Expression Indexes

Case-Insensitive Searches

GIN Indexes

GIN and JSONB

GIN and Arrays

GiST Indexes

BRIN Indexes

Choosing an Index Type

How to Know If PostgreSQL Uses an Index

EXPLAIN ANALYZE

Important Warning About EXPLAIN ANALYZE

Index Scan vs Sequential Scan

Index Scan

Sequential Scan

Why PostgreSQL Might Not Use Your Index

The Table Is Small

Too Many Rows Match

Outdated Statistics

Query Expression Doesn’t Match

Cost Estimates

Index Selectivity

Indexes and Writes

Indexes Consume Storage

Don’t Index Every Column

Indexing Foreign Keys

Indexing Search Columns

Indexing ORDER BY

Indexing with LIMIT

Covering Indexes and INCLUDE

What Is an Index-Only Scan?

Index Maintenance

VACUUM

ANALYZE

REINDEX

Index Bloat

Concurrent Index Creation

Dropping an Unused Index

How to Find Unused Indexes

Common Indexing Mistakes

1. Creating Too Many Indexes

2. Ignoring Query Patterns

3. Using the Wrong Column Order

4. Forgetting Foreign-Key Indexes

5. Assuming Every Query Needs an Index

6. Ignoring EXPLAIN ANALYZE

7. Creating an Index Without Measuring

A Practical Indexing Workflow

Example: Optimizing a Slow Query

PostgreSQL Indexing Best Practices

Start with B-tree

Index Based on Queries

Consider Composite Indexes

Don’t Over-Index

Use EXPLAIN ANALYZE

Keep Statistics Updated

Consider Partial Indexes

Use Specialized Index Types When Appropriate

Monitor Index Usage

PostgreSQL Indexing Cheat Sheet

Important Commands

Create Index

Create Unique Index

Indexes and `ORDER BY`

Indexing `ORDER BY`

Indexing with `LIMIT`

Covering Indexes and `INCLUDE`

6. Ignoring `EXPLAIN ANALYZE`

Use `EXPLAIN ANALYZE`