SQL Query Performance: Table Scan vs Index Lookup

SQL query performance is one of the most critical aspects of backend and database scaling. When an application fetches data, the database management system (DBMS) query planner must decide the most efficient way to access the requested records.

Understanding how the database chooses between a table scan and an index lookup is the key to tuning slow commands.

---

How Database Planners Work

When you submit a query, the SQL engine compiles the statement and passes it to the query planner. The planner calculates estimated costs for different execution plans using table statistics (like row counts and data distribution). The plan with the lowest estimated cost is selected and executed.

The two main execution paths for reading rows are:

1. Sequential/Table Scan: Scanning every record in the table from first to last.
2. Index Scan/Lookup: Scanning a pre-sorted index structure to find target rows instantly.

---

Table Scan vs Index Lookup

Here is how these two common data access methods compare:

The Sequential Table Scan

A sequential table scan occurs when the database engine reads every page of data in a table to locate rows matching your search criteria. If your query filters on a column that does not have an index, the database has no choice but to perform a table scan. For large tables containing millions of rows, table scans consume massive amounts of memory, disk I/O, and CPU time, severely degrading application throughput.

The Index Lookup

An index lookup is similar to the index at the back of a book. It allows the database query engine to jump directly to the pages containing the relevant records. Databases typically structure index keys in B-Trees. When you filter on an indexed column, the query engine traverses the tree branches in logarithmic time, skipping millions of irrelevant records instantly.

---

Analyzing Query Execution with EXPLAIN ANALYZE

To identify if a slow query is performing a sequential scan instead of an index lookup, you can prepend your query with EXPLAIN or EXPLAIN ANALYZE. The database will execute the query and print a detailed execution plan:

EXPLAIN ANALYZE
SELECT id, username, email
FROM users
WHERE email = 'developer@cosharex.com';

Reading the Output

In PostgreSQL, the output might look like this:

Seq Scan on users  (cost=0.00..35.40 rows=1 width=64) (actual time=0.045..0.120 rows=1 loops=1)
  Filter: ((email)::text = 'developer@cosharex.com'::text)
  Rows Removed by Filter: 14502
Planning Time: 0.082 ms
Execution Time: 0.145 ms

The output shows Seq Scan (Sequential Scan), indicating that the engine checked all 14,000+ records sequentially. To optimize slow database queries, we must introduce a index.

---

How to Optimize Slow Database Queries

To resolve sequential scans on frequently filtered columns, you should construct target indexes on your tables:

CREATE UNIQUE INDEX idx_users_email ON users(email);

After creating the index, running EXPLAIN ANALYZE again demonstrates a massive change:

Index Scan using idx_users_email on users  (cost=0.15..8.17 rows=1 width=64) (actual time=0.012..0.015 rows=1 loops=1)
  Index Cond: ((email)::text = 'developer@cosharex.com'::text)
Planning Time: 0.095 ms
Execution Time: 0.032 ms

The access method has shifted to Index Scan, reducing execution time significantly.

Performance Best Practices

• Index Foreign Keys: Always index fields used in table joins to prevent expensive sequential scans during join evaluations.
• Avoid Over-Indexing: Every index adds overhead to insert, update, and delete actions. Choose index targets strategically.
• Maintain Clean Formatting: Formatting SQL queries makes it easier to verify structural indexes and avoid complex subquery pitfalls.

---

FAQ

What is a table scan?

A table scan (sequential scan) occurs when the database reads every data block in a table to find rows that match query filters. It is slow on large tables.

When is a table scan faster than an index lookup?

If a query returns a large percentage of rows in the table (e.g. over 20-30%), the engine may decide that loading the index files and fetching rows individually is slower than scanning the table sequentially in bulk.

How do I write indexes for multi-column filters?

If you frequently query using multiple filters (e.g. WHERE status = 'active' AND role = 'admin'), write a composite index:

CREATE INDEX idx_users_status_role ON users(status, role);