Database Indexing & Query Optimization Basics
Most slow applications are slow because of the database. Here are the indexing and query-optimization basics that fix it, and the mistakes that cause it.
- When an application is slow, the database is usually the culprit, and database indexing plus query optimization is the highest-leverage fix, well ahead of adding hardware.
- An index lets the database find rows without scanning the whole table; the skill is indexing the columns your real queries filter, join and sort by, not every column.
- Query plans (EXPLAIN / EXPLAIN ANALYZE) tell you what is slow and why; most slowness traces to a few missing indexes or inefficient queries, not the engine itself.
- Indexes speed reads but cost a little on every write and use storage, so over-indexing is a real failure mode: index deliberately, then re-measure to confirm the gain.
Database indexing and query optimization are the fastest way to fix a slow application, because the database is the most common bottleneck and indexes are the highest-leverage lever you have. An index is a data structure that lets the database jump straight to the rows a query needs instead of scanning the whole table, so a query that filters, joins or sorts by an indexed column runs in a fraction of the time. The practical workflow is short: find the slow queries, read their execution plans, add the right index or rewrite the query, then re-measure. This guide covers how indexes work, which columns to index, how to read a query plan, what drives performance, and the mistakes that quietly cripple databases.
How Indexes Work
An index is a data structure (usually a B-tree) that lets the database find rows matching a condition without scanning the entire table, much like a book's index versus reading every page. Query a column with no index and the database may perform a full table scan, reading every row, which grows steadily slower as the table grows. The right index turns that scan into a targeted lookup that stays fast at scale. The trade-off is deliberate: indexes accelerate reads but add a small maintenance cost on every insert, update and delete, and they consume storage, so you index for the queries you actually run rather than indexing everything.
The difference between a full table scan and an index lookup is the difference most teams feel as their tables grow. A scan reads every row and gets linearly slower with size; an index lookup navigates directly to matching rows and barely changes as the table grows.
| Aspect | Full Table Scan | Index Lookup |
|---|---|---|
| How it finds rows | Reads every row in the table | Navigates a sorted structure to the matches |
| Behaviour as data grows | Gets steadily slower | Stays fast at scale |
| Best for | Tiny tables, or reading most of the table | Selective filters, joins and lookups |
| Cost | High CPU and I/O on large tables | Small storage plus write overhead |
The classic cause of a slow query is a missing index on a column you filter or join by. The fix is often a single, well-chosen index.
Types Of Indexes
Most everyday performance wins come from a few common index types, and knowing which one fits a query pattern is half the battle. The table below maps the common types to when they help.
| Index Type | What It Does | When To Use It |
|---|---|---|
| Single-column | Indexes one column | You filter, join or sort by that column alone |
| Composite (multi-column) | Indexes several columns in order | Queries filter on the same columns together |
| Unique | Enforces and indexes uniqueness | Emails, usernames, natural keys |
| Covering | Includes all columns a query needs | Read-heavy queries you can satisfy from the index |
| Full-text | Indexes tokens within text | Searching words inside large text fields |
Composite index column order matters: an index on (tenant_id, created_at) helps queries that filter tenant_id first, but not queries that filter created_at alone.
Which Columns To Index
Index the columns your queries filter, join and sort by, and resist the urge to index the rest. Use this decision matrix to judge a candidate column before adding an index.
- Columns you filter by (WHERE), join on, or sort by (ORDER BY).
- Composite indexes for queries that filter on multiple columns together.
- Foreign keys, which are frequently joined and often left unindexed.
- Avoid over-indexing: every index slows writes and consumes storage.
| Column Pattern | Index It? | Why |
|---|---|---|
| Used in WHERE / JOIN / ORDER BY | Yes | Directly enables fast lookups |
| Foreign key column | Usually | Joins and referential checks depend on it |
| High-cardinality, selective column | Yes | Filters return few rows, so the index pays off |
| Low-cardinality flag (for example a boolean) | Rarely | Matches too many rows to be selective |
| Rarely queried column | No | Pure write and storage cost, no read benefit |
How To Find And Fix Slow Queries
Fixing slow queries is a repeatable loop, not guesswork: measure, read the plan, change one thing, and measure again. Follow this order.
- Find the slow queries using slow-query logs and monitoring rather than intuition.
- Read the plan with EXPLAIN or EXPLAIN ANALYZE to see scans versus index use.
- Add the right index, targeting the exact columns the plan is scanning.
- Rewrite the query to remove SELECT *, N+1 patterns and needless work.
- Re-measure to confirm the change actually helped, and check write impact.
- Remove indexes that no monitoring shows being used, to protect write speed.
Reading A Query Execution Plan
A query execution plan shows exactly how the database runs a query, and reading it is the single most useful database skill for performance work. Use EXPLAIN to see the plan, or EXPLAIN ANALYZE to see the plan with real timings and row counts. Look for full table scans (often shown as a sequential scan) on large tables, steps where the estimated and actual row counts diverge wildly, and expensive join methods on big inputs. A scan on a large table where you expected an index lookup usually means the index is missing, unusable, or the query is written in a way that prevents the index from being used.
Wrapping an indexed column in a function, for example WHERE lower(email) = ..., usually prevents the index from being used. Store or index the computed form instead.
What Drives Query Performance
Query performance is driven by a handful of qualitative factors, not a magic setting. The highlights below capture where the leverage genuinely is, without inventing precise numbers your workload will not match.
Common Mistakes To Avoid
Most database performance problems come from a short list of recurring mistakes, and recognising them is faster than any tuning trick.
- No indexes on columns used in WHERE, JOIN or ORDER BY.
- N+1 queries: fetching related data in a loop instead of in one query.
- SELECT * pulling back columns (and rows) the application never uses.
- Functions on indexed columns in WHERE, which prevent the index from being used.
- Over-indexing: too many indexes that slow every write and bloat storage.
- Adding indexes without reading the plan or re-measuring, so the real cause stays hidden.
Slow Database Dragging Your App Down?
We profile and tune databases - indexing, query optimization and schema design - so slow applications get fast without a rewrite. Tell us where it hurts and we will read the plans with you.
How Acqurio Tech Approaches Database Performance
We treat database performance as a measurable engineering problem, starting from the slow queries and their plans rather than guesses. Our teams profile the workload, add or remove indexes deliberately, rewrite the queries that matter, and pair the fix with monitoring so regressions surface early. If you also depend on caching, our guide to caching strategies for high-traffic apps pairs well with the indexing work here. Where we help:
- Custom software development - efficient data access designed in, not bolted on.
- PostgreSQL expertise - indexing, tuning and query optimization on real workloads.
- API development - endpoints backed by queries that stay fast under load.
- Cloud & DevOps - monitoring that catches slow queries before users do.
Conclusion
Most application slowness traces back to the database, and database indexing plus query optimization is the highest-leverage fix. Understand that indexes turn full table scans into fast lookups, index the columns your queries actually filter, join and sort by, read query plans to find what is truly slow, and avoid the classic mistakes like N+1 queries, SELECT * and functions on indexed columns. A few well-chosen indexes and tuned queries usually transform performance. If you would rather have experienced engineers do it with you, get in touch and we will start from your slowest queries.
Frequently asked questions
What Is Database Indexing And Why Does It Matter?
Database indexing is the practice of building data structures that let the database find rows matching a condition without scanning the entire table, like a book's index versus reading every page. It matters because it turns slow full table scans into fast lookups for the columns it covers, dramatically speeding up queries that filter, join or sort by those columns, which is usually the single biggest performance win available.
How Do I Optimize A Slow Query?
Find slow queries via slow-query logs or monitoring, use EXPLAIN or EXPLAIN ANALYZE to see whether the query scans tables or uses indexes, add the right index on the columns being scanned, rewrite the query to avoid SELECT *, N+1 patterns and needless work, then re-measure to confirm the fix helped and did not hurt write performance.
Which Columns Should I Index?
Index columns you filter by (WHERE), join on, or sort by (ORDER BY), use composite indexes for queries that filter on multiple columns together, and index foreign keys that are frequently joined. Favour high-cardinality, selective columns, and avoid over-indexing: every index speeds reads but slows writes and uses storage, so index deliberately for your actual queries.
Can Too Many Indexes Hurt Performance?
Yes. While indexes speed up reads, each one adds overhead to writes (inserts, updates and deletes) because it must be maintained, and each uses storage. Over-indexing slows down write-heavy workloads and wastes space, so you should index the columns your queries genuinely need rather than indexing everything, and remove indexes that monitoring shows are never used.
What Is An N+1 Query Problem?
It is when an application runs one query to fetch a list, then a separate query for each item to fetch related data, resulting in N+1 queries instead of one or two. It is a common cause of slow applications and is fixed by fetching related data together, with a join or a single batched query, instead of querying in a loop.
How Do I Read A Query Execution Plan?
Use EXPLAIN, or EXPLAIN ANALYZE for actual timings, to see how the database executes a query: whether it uses an index or scans the whole table, the join methods, and where time is spent. Full table scans on large tables, and big gaps between estimated and actual row counts at expensive steps, point to missing indexes or inefficient queries to fix.
Will Adding Indexes Slow Down Writes?
A little, and that trade-off is the whole point of indexing deliberately. Each index must be updated on every insert, update or delete that touches its columns, so a table with many indexes writes more slowly. The fix is balance: keep the indexes your read queries genuinely rely on, drop the ones nothing uses, and re-measure both read and write paths after changes.