Indexing in PostgreSQL Explained with Examples

Overview

Having dealt with a production database, you are likely aware of a harsh reality: a sluggish query can incapacitate an entire program. Indexing is one of the most effective ways to address this issue in PostgreSQL.

However, indexing is sometimes misinterpreted. Many developers either avoid it out of fear or utilize it mindlessly. In this article, we shall dissect:

What indexing really is
How it works internally
Types of indexes in PostgreSQL
When to use indexes (and when not to)
Real-world examples with performance impact

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

What is an Index in PostgreSQL?

A data structure called an index enables PostgreSQL to locate entries more quickly without having to search the whole table.

An index can be compared to a book’s table of contents.
You go straight to the topic rather than having to search through every page to find it.

Without an index:

PostgreSQL performs a Sequential Scan (Full Table Scan)
It checks every row one by one
Performance degrades badly as data grows

With an index:

PostgreSQL performs an Index Scan
It directly locates the matching rows
Query time drops from seconds to milliseconds

Example Without an Index

Let’s say you have a table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT,
    name TEXT,
    created_at TIMESTAMP
);

CREATE TABLE users (

id SERIAL PRIMARY KEY,

email TEXT,

name TEXT,

created_at TIMESTAMP

);

And the query:

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

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

If the email does not have an index, PostgreSQL has to:

Go over each row.
Examine each email value separately.
Give back the corresponding row.

For 100 rows, this functions perfectly.

For ten million rows, it turns into a catastrophe.

Creating Your First Index

Now let’s fix this:

CREATE INDEX idx_users_email ON users(email);

1	CREATE INDEX idx_users_email ON users(email);

Now the same query:

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

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

PostgreSQL will:

Use the index
Jump directly to the row location
Skip scanning the entire table

This alone can give 100x to 1000x performance improvements.

How PostgreSQL Indexes Work Internally (Simplified)?

PostgreSQL primarily uses B-Tree indexes by default.

Internally:

Index values are kept in structures that resemble sorted trees.
Every node refers to its offspring nodes.
Logarithmic time is used for lookups. O(log n) as opposed to O(n)

That is why indexes scale extremely well, even for millions of records.

Types of Indexes in PostgreSQL

PostgreSQL provides multiple index types for different workloads.

B-Tree Index (Default & Most Common)

Best for:

=, <, >, <=, >=
Sorting
Range queries

Example:

CREATE INDEX idx_users_created_at ON users(created_at);

1	CREATE INDEX idx_users_created_at ON users(created_at);

Use this for:

Filtering
Ordering
Primary key lookups

Hash Index

Optimized for:

Equality comparisons (= only)

CREATE INDEX idx_users_email_hash ON users USING HASH(email);

1	CREATE INDEX idx_users_email_hash ON users USING HASH(email);

Not commonly used anymore because B-Tree already handles equality very well.

GIN Index (Generalized Inverted Index)

Best for:

JSONB
Arrays
Full-text search

Example:

CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

1	CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

Used heavily in:

Analytics apps
Search engines
Tag-based filtering

Composite Index (Multi-column Index)

CREATE INDEX idx_users_name_email ON users(name, email);

1	CREATE INDEX idx_users_name_email ON users(name, email);

Important rule:

This index works for:
- WHERE name = ‘A’
- WHERE name = ‘A’ AND email = ‘x’
But not efficiently for:
- WHERE email = ‘x’ alone

Order matters.

How to Check If Your Index Is Being Used?

Use:

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

1 2	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'sonam@example.com';

If you see:

Index Scan using idx_users_email – Good
Seq Scan on users – Index not used

Always trust EXPLAIN ANALYZE, not assumptions.

When You SHOULD Create an Index?

Create an index when:

A column is frequently used in WHERE
A column is used in JOIN conditions
A column is used in ORDER BY
The table has a large volume of data
Query runs frequently in production

Example:

SELECT * FROM orders WHERE user_id = 42;

1	SELECT * FROM orders WHERE user_id = 42;

user_id should absolutely be indexed.

When You Should NOT Create an Index

Indexes are not free. Avoid indexing when:

The table is very small
Column changes very frequently (heavy updates)
The column has very low cardinality (like gender or boolean)
Column is rarely used for filtering

Why?

Because every INSERT, UPDATE, and DELETE must also update the index, adding write overhead.

Indexing and Write Performance Trade-off

Indexes:

Improve read performance
Slow down write performance

If your system is:

Read-heavy (dashboards, analytics, APIs) → Index more
Write-heavy (logging systems, streaming ingestion) → Index carefully

Good database design is always a balance.

Partial Indexes (Hidden Power Feature)

PostgreSQL allows partial indexes:

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

CREATE INDEX idx_active_users

ON users(email)

WHERE is_active = true;

Benefits:

Smaller index size
Faster scans
Less memory usage

Use this when:

Queries always filter on a condition

Real-World Performance Impact Example

Before indexing:

SELECT * FROM transactions WHERE user_id = 101;

1	SELECT * FROM transactions WHERE user_id = 101;

12 million rows
Execution time: 3.4 seconds
Seq Scan

After indexing:

CREATE INDEX idx_transactions_user_id ON transactions(user_id);

1	CREATE INDEX idx_transactions_user_id ON transactions(user_id);

Execution time: 9 milliseconds
Index Scan

That’s a 377x performance improvement with a single index.

Common Indexing Mistakes

Creating too many indexes blindly
Indexing columns that are never queried
Ignoring EXPLAIN ANALYZE
Creating duplicate or overlapping indexes
Forgetting composite index ordering

Indexes should be strategic, not emotional decisions.

Final Thoughts

Indexing is one of the most powerful and dangerous tools in PostgreSQL:

Used correctly → Massive performance gains
Used blindly → Wasted storage + slower writes

A senior backend engineer is not defined by how many indexes they create, but by where and why they create them.

Before adding any index, always ask:

Is this query actually slow?
Is this column heavily used in filtering?
Will this index help more than it hurts?

If the answers align, add the index with confidence.

Drop a query if you have any questions regarding PostgreSQL and we will get back to you quickly.

Empowering organizations to become ‘data driven’ enterprises with our Cloud experts.

Reduced infrastructure costs
Timely data-driven decisions

Get Started

About CloudThat

CloudThat is an award-winning company and the first in India to offer cloud training and consulting services worldwide. As a Microsoft Solutions Partner, AWS Advanced Tier Training Partner, and Google Cloud Platform Partner, CloudThat has empowered over 850,000 professionals through 600+ cloud certifications winning global recognition for its training excellence including 20 MCT Trainers in Microsoft’s Global Top 100 and an impressive 12 awards in the last 8 years. CloudThat specializes in Cloud Migration, Data Platforms, DevOps, IoT, and cutting-edge technologies like Gen AI & AI/ML. It has delivered over 500 consulting projects for 250+ organizations in 30+ countries as it continues to empower professionals and enterprises to thrive in the digital-first world.

FAQs

1. What is an index in PostgreSQL?

ANS: – An index is a data structure that speeds up data retrieval by avoiding full table scans.

2. Which index type should I use?

ANS: – Use B-Tree for most queries, GIN for full-text or JSON, and BRIN for large sequential data.

3. How do I check if my index is used?

ANS: – Run EXPLAIN on your query and look for Index Scan or Bitmap Index Scan in the plan.

WRITTEN BY Sonam Kumari

Sonam is a Software Developer at CloudThat with expertise in Python, AWS, and PostgreSQL. A versatile developer, she has experience in building scalable backend systems and data-driven solutions. Skilled in designing APIs, integrating cloud services, and optimizing performance for production-ready applications, Sonam also leverages Amazon QuickSight for analytics and visualization. Passionate about learning and mentoring, she has guided interns and contributed to multiple backend projects. Outside of work, she enjoys traveling, exploring new technologies, and creating content for her Instagram page.