How to Optimize PostgreSQL Queries with Indexes

Running the Query Without an Index

Connect to the database and seed it with half a million users:

sudo systemctl status postgresql
sudo -u postgres psql -d app_database

Run the seed script:

\i seed_users.sql

Note: The video shows INSERT 0 500000 from a plain insert. The seed file attached to this tutorial outputs a NOTICE message instead — it checks for existing data first to avoid duplicates. Both produce the same 500,000 rows.

Verify the data:

SELECT COUNT(*) FROM users;

You should see 500,000 rows.

Now see what the planner does without an index. Use EXPLAIN ANALYZE to examine the query plan:

EXPLAIN ANALYZE
SELECT * FROM users WHERE user_email = '[email protected]';

The output shows a parallel sequential scan. The planner chose to split the operation into two workers because it had no better option. The execution time is around 30 milliseconds. At scale, this query is unusable for every login or API call.

Creating an Index

Create a B-tree index on the email column:

CREATE INDEX idx_users_email ON users (user_email);

The index costs something to build, but only once. Now the planner has a new strategy available every time a query filters by email.

Running the Query With the Index

Run the same EXPLAIN ANALYZE query again:

EXPLAIN ANALYZE
SELECT * FROM users WHERE user_email = '[email protected]';

The output now shows an index scan using idx_users_email. The execution time drops to 0.075 milliseconds, roughly 400 times faster. The planner switched from a sequential scan to an index scan because the index provides a logarithmic lookup instead of scanning all half a million rows.

When Not to Use an Index

Indexes can hurt performance in three main cases:

• Low cardinality: If a column has very few distinct values (e.g., a boolean in_stock column with only true or false), the planner will skip the index and perform a sequential scan.
• Tiny tables: For tables with 50–100 rows, a sequential scan is faster than an index lookup.
• Write-heavy tables: Indexes are separate data structures. Every insert, update, or delete on the indexed column also updates the index, adding overhead. On logging tables with millions of inserts, index maintenance is extra cost you don't need.