Sequential scan vs index scan in PostgreSQL - When to Use Which

The Big Idea

What if you could find any piece of data instantly without flipping through everything?

The Scenario

Imagine you have a huge phone book and you want to find all people named "John." You start at the first page and look at every single name until you find all the Johns.

The Problem

This method is very slow and tiring because you check every name, even if the Johns are only on a few pages. It wastes time and energy, especially when the phone book is very big.

The Solution

Using an index is like having a special list that tells you exactly which pages have the name "John." You can jump straight to those pages without flipping through the whole book.

Before vs After

✗ Before

SELECT * FROM people WHERE name = 'John'; -- scans all rows

✓ After

CREATE INDEX idx_name ON people(name);
SELECT * FROM people WHERE name = 'John'; -- uses index to find rows faster

What It Enables

This lets the database find data quickly and efficiently, saving time and resources.

Real Life Example

When you search for a product on an online store, the system uses indexes to quickly show matching items instead of checking every product one by one.

Key Takeaways

Sequential scan checks every row one by one, which is slow for big data.

Index scan uses a special shortcut to find data faster.

Indexes make searching large databases much quicker and efficient.