PostgreSQL Indexes Explained with Examples

As your database grows, queries can become slow.
Indexes are used in PostgreSQL to speed up data retrieval and improve query performance.

In this article, you will learn:

• What indexes are
• Why indexes are important
• How indexes work
• Types of indexes in PostgreSQL
• When to use (and not use) indexes

This guide is beginner‑friendly.

---

What Is an Index in PostgreSQL?

An index is a data structure that allows PostgreSQL to find rows faster without scanning the entire table.

Without an index:

• PostgreSQL checks every row (sequential scan)

With an index:

• PostgreSQL jumps directly to the required data

Indexes are similar to an index in a book — they help you find information quickly.

---

Why Are Indexes Important?

Indexes help:

• Improve query performance
• Speed up SELECT queries
• Optimize WHERE, JOIN, ORDER BY, and GROUP BY

Indexes are critical for large tables.

---

How PostgreSQL Queries Data Without an Index

Example query:

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

Without an index:

• PostgreSQL scans every row in the table
• Performance decreases as data grows

---

How Indexes Improve Performance

With an index on email:

• PostgreSQL searches the index
• Finds the exact row location
• Fetches data quickly

This reduces disk reads and query time.

---

Creating an Index

Basic Index Syntax

CREATE INDEX index_name

ON table_name (column_name);

---

Example

CREATE INDEX idx_users_email

ON users (email);

This index improves performance for queries filtering by email.

---

Types of Indexes in PostgreSQL

PostgreSQL supports multiple index types. The most common ones are:

1. B‑tree Index
2. Unique Index
3. Composite Index
4. Partial Index
5. GIN Index

---

1. B‑tree Index (Default)

The B‑tree index is the default and most commonly used index type.

Used for:

• =
• <, >
• <=, >=
• ORDER BY

Example

CREATE INDEX idx_users_name

ON users (name);

---

2. UNIQUE Index

A UNIQUE index ensures that indexed column values are unique.

Example

CREATE UNIQUE INDEX idx_users_email_unique

ON users (email);

This prevents duplicate email values.

---

3. Composite Index

A composite index is created on multiple columns.

Example

CREATE INDEX idx_users_name_email

ON users (name, email);

Used when queries filter by both columns.

---

4. Partial Index

A partial index indexes only rows that meet a condition.

Example

CREATE INDEX idx_active_users

ON users (email)

WHERE is_active = true;

This is useful when only a subset of data is frequently queried.

---

5. GIN Index

The GIN (Generalized Inverted Index) is used for:

• JSONB
• Arrays
• Full‑text search

Example

CREATE INDEX idx_users_profile

ON users

USING GIN (profile);

Best for searching inside JSONB data.

---

Viewing Existing Indexes

To view indexes on a table:

\d users

Or query system catalog:

SELECT indexname, indexdef

FROM pg_indexes

---

Dropping an Index

If an index is no longer needed:

DROP INDEX index_name;

Example:

DROP INDEX idx_users_email;

---

When NOT to Use Indexes

Avoid creating indexes when:

• Tables are very small
• Columns are rarely used in queries
• Data is frequently updated

Indexes increase:

• Storage usage
• Insert and update time

Use indexes only when needed.

---

Index Best Practices

• Index columns used in WHERE clauses
• Index foreign key columns
• Avoid indexing every column
• Monitor performance before adding indexes
• Remove unused indexes

Balanced indexing leads to better performance.

---

Summary

• Indexes improve query performance
• B‑tree is the default index type
• Unique indexes prevent duplicates
• Composite indexes support multi‑column queries
• GIN indexes are ideal for JSONB
• Indexes should be used wisely

You now understand PostgreSQL indexes clearly.

---

What’s Next?

Next, we will learn about views in PostgreSQL.

Next article:
PostgreSQL Views Explained with Examples