Practical Database Indexing: Why Your Queries Are Slow

You launch your application. The first week is great. The second week is fine. By month three, users start complaining that the dashboard takes five seconds to load. You check your server logs, and the CPU is hovering at 10%, but your database is screaming.

Welcome to the bottleneck.

Almost every performance problem in a mature application eventually traces back to the database. More specifically, it traces back to how the database searches for data. When tables have a few thousand rows, a full table scan is imperceptible. When tables hit millions of rows, a full table scan will bring your application to a grinding halt.

The solution is indexing. However, indexing is not a silver bullet. You cannot simply index every column. In this guide, we will break down exactly how database indexes work, how to choose the right columns, and how to analyze query performance using tools like EXPLAIN ANALYZE. We will focus primarily on PostgreSQL, but these concepts apply to MySQL, SQL Server, and mostly any relational database.

1. What is an Index?

Imagine you are looking for a specific chapter in a textbook. If the textbook has no table of contents or index at the back, you have to flip through every single page from beginning to end until you find what you are looking for. In database terminology, this is a Sequential Scan (or Full Table Scan).

An index in a database is exactly like the index at the back of a book. It is a separate data structure that stores a subset of the table's columns along with a pointer to the physical location of the full row on the disk.

When you query an indexed column, the database searches the highly-optimized index structure (usually a B-Tree), finds the pointer, and then jumps directly to the exact location on the disk to retrieve the row. This transforms a search operation that takes $O(N)$ time into one that takes $$\begin{gathered} O( \\ logN) \end{gathered}$$ time.

The Cost of Indexing

If indexes make reads so much faster, why not index every column?

Because indexes are not free. Every time you INSERT, UPDATE, or DELETE a row in the main table, the database must also update every single index associated with that table. If a table has 10 indexes, a single INSERT requires 11 write operations (1 for the table, 10 for the indexes).

Over-indexing will drastically slow down your write performance and consume a massive amount of disk space. You must strike a balance: index the columns you frequently use for searching, sorting, and filtering, but no more.

2. Under the Hood: The B-Tree

The most common index type in almost every relational database is the B-Tree (Balanced Tree).

A B-Tree is a self-balancing tree data structure that maintains sorted data and allows searches, sequential access, insertions, and deletions in logarithmic time.

How B-Trees Work

In a B-Tree, the data is stored in nodes. The top node is the root. It contains a range of values and pointers to child nodes. The child nodes contain smaller ranges, pointing to further child nodes, all the way down to the "leaf" nodes.

The leaf nodes contain the actual index values and the pointer (often called a Tuple ID or Row ID) to the physical row on the disk.

Crucially, the leaf nodes in a B-Tree are linked together in a doubly-linked list. This means that once the database finds a specific value, it can easily traverse the leaf nodes to find all subsequent values. This makes B-Trees incredible for both exact matches (WHERE age = 30) and range queries (WHERE age > 30).

Other Types of Indexes

While B-Trees cover 90% of use cases, PostgreSQL offers several other index types:

• Hash Indexes: Only support equality checks (=). They do not support range queries. They are slightly smaller and faster for exact matches, but their limited utility means B-Trees are usually preferred.
• GIN (Generalized Inverted Index): Essential for indexing complex data types that contain multiple values, such as arrays or JSONB documents. If you are querying a JSONB column in Postgres, you need a GIN index.
• GiST (Generalized Search Tree): Used for indexing geometric data types (e.g., finding points within a polygon for spatial queries) and full-text search.
• BRIN (Block Range Index): Designed for massive tables where the data is physically sorted on disk (like time-series data). It stores the minimum and maximum values for blocks of pages, making it incredibly space-efficient.

3. Single-Column vs. Composite Indexes

The simplest index covers a single column.

This is perfect if you frequently look up users by their email address.

However, queries often filter on multiple columns simultaneously.

While Postgres can use a technique called "Bitmap And" to combine the results of two separate single-column indexes (one on last_name, one on first_name), it is often much faster to create a Composite Index (a multi-column index).

The Rule of Column Order

The order of columns in a composite index is critical. A composite index works like a telephone directory. The telephone directory is sorted by Last Name, and then by First Name.

If you search for "Smith, John", the directory is perfectly optimized for this. If you search for everyone with the last name "Smith", the directory is still highly optimized. But if you search for everyone with the first name "John", the directory is completely useless. You would have to read the entire book.

Therefore, a composite index on (A, B, C) can speed up queries that filter on:

• A
• A and B
• A, B, and C

It cannot effectively speed up queries that filter only on B or C. Always put the most frequently queried column, or the column with the highest cardinality (most unique values), first.

4. Understanding Cardinality and Selectivity

Cardinality refers to the number of unique values in a column.

• A uuid column has incredibly high cardinality (every row is unique).
• A boolean column (is_active) has incredibly low cardinality (only two possible values: true or false).

Selectivity refers to the percentage of rows returned by a query. A highly selective query returns very few rows (e.g., WHERE email = '...'). A query with low selectivity returns many rows (e.g., WHERE is_active = true).

Why the Database Ignores Your Index

Sometimes, you will create an index, run a query, and find that the database completely ignores the index and performs a Sequential Scan anyway. Why?

Because the PostgreSQL query planner is incredibly smart. It maintains statistics about the distribution of data in your tables. Before executing a query, it estimates how many rows match the WHERE clause.

If the planner estimates that your query will return a massive portion of the table (for example, 60% of the rows because you queried WHERE is_active = true), it knows that reading the index, getting the pointers, and then jumping around the disk to fetch the rows will actually be slower than just sequentially reading the entire table off the disk in one go.

Sequential disk I/O is vastly faster than random disk I/O. The query planner will only use an index if the query is highly selective (usually returning less than 10-15% of the table). This is why indexing low-cardinality columns like booleans or status enums is often a waste of space unless combined with other techniques.

5. Partial Indexes

What if you have a users table with 10 million rows, but only 10,000 of them are is_admin = true? You frequently run a query to find admins:

Indexing the is_admin column is a bad idea because it has low cardinality. However, you can create a Partial Index. A partial index is an index built over a subset of a table defined by a conditional expression.

This index is tiny because it only contains 10,000 entries instead of 10 million. It is incredibly fast to update and consumes almost no memory, yet it will massively accelerate queries targeting the admin users. Partial indexes are one of the most powerful features in PostgreSQL.

6. Covering Indexes (Index-Only Scans)

When you query an indexed column, the database performs two steps:

1. Search the index to find the pointer.
2. Read the physical table (the "heap") to fetch the required columns.

Step 2 involves disk I/O, which is slow. What if we could eliminate Step 2 entirely?

If the index itself contains all the columns requested by the SELECT statement, the database can perform an Index-Only Scan. It gets everything it needs directly from the index structure without ever touching the main table.

You can achieve this using the INCLUDE clause.

If you run:

The database uses the B-Tree to find the email. Because first_name and last_name are included directly in the leaf node of the index (they are not part of the search tree structure, just attached to the payload), it returns the data immediately. This is a Covering Index.

7. The Power of EXPLAIN ANALYZE

You should never guess if an index is working. You must measure it. PostgreSQL provides the EXPLAIN command to show you the execution plan the query planner has generated.

If you add ANALYZE, Postgres actually executes the query and provides the real, measured timings.

Reading the Output

The output can be intimidating, but you only need to look for a few key things initially:

• Seq Scan: The database read the entire table. Bad for large tables.
• Index Scan: The database used the index to find pointers, then read the table. Good.
• Index Only Scan: The database got all data directly from the index. Excellent.
• Bitmap Heap Scan / Bitmap Index Scan: The database built a bitmap of row locations in memory using one or more indexes, then fetched the rows. Very efficient for queries returning a moderate number of rows.
• Execution Time: The total time taken.
• Rows Removed by Filter: If you see a sequential scan that reads 1,000,000 rows and "Removes 999,990 rows by filter", you absolutely need an index on the column being filtered.

Cost Estimates

The output also shows cost=0.00..15.32. This is an arbitrary unit calculated by the planner based on estimated disk reads and CPU cycles. The first number is the startup cost (time before the first row is returned), and the second is the total cost. You use these to compare different query rewrites, but focus on the actual Execution Time provided by ANALYZE.

8. Indexing for Sorting (ORDER BY)

Indexes are fundamentally ordered data structures. Therefore, they are not only used for WHERE clauses but also for ORDER BY clauses.

If you run:

Without an index, the database must read the entire table into memory, sort it, and then pick the top 10. If the table is too large for memory (work_mem), it will write temporary files to disk, which is devastating for performance.

If you have an index on created_at, the database can simply traverse the index backwards, grab the first 10 pointers, and fetch the rows. The EXPLAIN output will show this as avoiding a "Sort" operation entirely.

For composite indexes, the order matters immensely for sorting. If you have an index on (team_id, created_at), it can instantly solve:

Because within the team_id = 5 section of the index, the records are already perfectly sorted by created_at.

9. Common Indexing Mistakes

Indexing Every Column

Frameworks and ORMs sometimes make it too easy to add indexes. Remember that every index penalizes write performance. Only add indexes based on actual slow query logs.

Using Functions in WHERE Clauses

If you have an index on email, and you write:

The database cannot use the index. The index stores the exact values, not the lowercased values. To fix this, you must use a Function-Based Index (or Expression Index):

Or, preferably, ensure all emails are lowercased at the application level before insertion, or use a case-insensitive collation (like citext in Postgres).

The LIKE Operator

An index on a string column can be used for LIKE 'prefix%' (because the B-Tree is ordered alphabetically, it knows exactly where 'prefix' starts and ends).

However, an index cannot be used for LIKE '%suffix' or LIKE '%substring%'. If you need to search for substrings anywhere within a string, a standard B-Tree index is useless. You must look into pg_trgm (trigram indexes) and use a GIN or GiST index.

10. Maintaining Indexes

Indexes fragment over time, just like hard drives. As rows are updated and deleted, Postgres leaves "dead tuples" behind until the Autovacuum process cleans them up. This leaves empty space in the index pages, causing the index to become bloated and slow to read.

REINDEX

You can rebuild an index from scratch using the REINDEX command. However, standard REINDEX blocks writes to the table. In modern PostgreSQL versions, you should always use REINDEX CONCURRENTLY, which builds a new index in the background without locking the table, and then smoothly swaps it in.

Finding Unused Indexes

Since indexes consume disk space and slow down writes, you should aggressively delete unused indexes. PostgreSQL tracks index usage in the pg_stat_user_indexes system view.

You can query it to find indexes that are never being read:

If an index has existed in production for a month and has idx_scan = 0, drop it immediately.

Conclusion

Database performance optimization is not magic; it is an engineering discipline. It requires understanding the underlying data structures of your storage engine and systematically analyzing how your application queries that data.

Start by logging slow queries (setting log_min_duration_statement in Postgres). Take those queries, run EXPLAIN ANALYZE, identify the missing indexes, apply them using CREATE INDEX CONCURRENTLY, and watch the execution time drop from seconds to milliseconds.

Mastering indexing is one of the most critical skills a backend engineer can possess. It is the line that separates a prototype from a scalable, production-ready system.