CPU Bottlenecks

Think of the CPU like a chef: if every order makes the chef chop every ingredient from scratch, the kitchen gets hot fast. Interviewers love this topic because the fix is often not 'buy a bigger server' but 'make the database do less work'.

Question: What are CPU bottlenecks in SQL, and how do you spot and fix them?

Answer: A CPU bottleneck means the database is spending most of its time computing instead of waiting on disk or locks. Common causes are scanning too many rows, sorting large result sets, joining badly, or using functions and casts that stop indexes from helping. The fix is usually to reduce the rows the engine must touch, make predicates sargable, and choose the right index for the filter and sort pattern.

Interview-Ready Answer: I look at CPU bottlenecks as 'too much work per query'. My first step is to check the execution plan and row counts to see whether the engine is scanning, sorting, hashing, or evaluating expressions on lots of rows. Then I try to cut the work early with a more selective or covering index, rewrite non-sargable predicates like functions on indexed columns, and remove unnecessary sorts or joins. The key idea is that a good index does not just make reads faster; it lowers the amount of CPU the engine spends per request.

What a CPU bottleneck really means

CPU is the part of the database that evaluates predicates, compares rows, builds hash tables, sorts results, and returns data. When a query is CPU-bound, the server is not mostly waiting for storage; it is busy doing computations on the rows it already has.

How it happens under the hood

1. The optimizer picks a plan based on table statistics, indexes, and estimated row counts. If estimates are wrong, it may choose a plan that looks cheap on paper but burns CPU in reality.
2. The executor starts reading rows and checking each predicate. A sargable predicate, meaning one the engine can search with an index, lets the database jump to matching rows instead of testing everything.
3. If the plan needs ordering or grouping, the engine sorts or hashes rows. Sorting is usually O(n log n); a scan is O(n); an index seek is closer to O(log n + k), where k is the number of matched rows.
4. If the query touches many rows, the CPU cost multiplies quickly. A million-row scan with a small per-row expression can still use a full core for hundreds of milliseconds or seconds.
5. If memory is too small for the sort or hash work, some engines spill to temp storage. That turns a pure CPU problem into both CPU and I/O pain.
6. Concurrency makes it worse: ten queries that are each 'just a bit heavy' can saturate all cores and slow the whole app.

When indexes help, and when they do not

An index helps CPU when it lets the engine skip most rows. A narrow, selective index on the exact filter columns often reduces both row reads and per-row comparisons. A covering index, meaning an index that contains every column the query needs, can also avoid extra table lookups and save CPU.

But indexes are not magic. Too many indexes increase write cost because every insert, update, and delete must maintain them. Also, if the query matches a large part of the table, a full scan can be cheaper than bouncing through the index row by row.

How to fix it in practice

1. Read the plan first. Look for full scans, big sorts, hash joins, and repeated expressions on many rows.
2. Filter earlier. Move selective conditions into the WHERE clause so fewer rows reach later steps.
3. Make predicates sargable. For example, prefer created_at >= '2024-01-01' over DATE(created_at) = '2024-01-01' unless you have an expression index.
4. Choose the right index shape. Put the most selective columns first when they support the query pattern, and include columns only when they avoid extra lookups.
5. Watch for expensive per-row work: implicit casts, scalar functions, JSON extraction, and repeated string operations can dominate CPU.
6. Check memory and concurrency. A query can look fine alone but become a CPU bottleneck when 50 sessions run it at once.

Edge cases interviewers like

• A small table may be faster with a scan than with an index, because the index adds extra steps.
• A low-selectivity index, such as one on a column with only a few repeated values, may not reduce CPU much.
• Parallel query can help, but it also adds coordination overhead, so it is not always faster.
• Version and engine matter: the exact plan choices, memory settings, and index behavior differ between PostgreSQL, SQL Server, MySQL, and Oracle.

Memory idea: reduce rows first, then reduce work per row. That is the whole game.

Real-World Story: An e-commerce checkout service had a 'recent paid orders' report that joined orders and order_items. A developer wrapped created_at in DATE() to make the filter look clean, but that stopped the normal timestamp index from being useful. During peak traffic the CPU hit 95%, p95 latency rose from 120 ms to nearly 5 seconds, and the logs showed no major disk waits, which fooled the team into thinking storage was the problem.

The real fix was to rewrite the filter into a sargable range and add a matching index for the access pattern. After that, the query touched far fewer rows, the hot core cooled down, and the autoscaling bill dropped because the service no longer needed extra pods just to do useless per-row work.

What goes wrong when people miss it: the app looks 'healthy' at the infrastructure layer, but users see slow pages, queue growth, and retries. The database logs often show long-running queries with huge row counts, while the app sees timeouts and occasional 500 errors.

Follow-up & Tricky Questions:

• How do you tell CPU-bound from I/O-bound? I look at wait events, elapsed time, and the plan. If disk waits are low but CPU stays high and the query is still slow, it is usually compute-heavy rather than storage-heavy.
• Can an index increase CPU? Yes. Every extra index adds write overhead, and a poorly chosen index can still be expensive if it matches too many rows or causes lots of random lookups.
• What does sargable mean? It means the predicate can be used to search an index efficiently. col >= value is usually sargable; DATE(col) = value often is not unless you add an expression index.
• When is a scan better than an index seek? When the query needs a large fraction of the table or the table is tiny. In those cases, reading sequentially can be cheaper than jumping through index pages.
• How do bad statistics cause CPU issues? Bad row estimates make the optimizer choose the wrong join type, wrong sort strategy, or wrong index. The plan may look sensible but end up doing far more work than expected.
• Tricky: Does more indexing always reduce CPU? No. More indexes can slow writes and increase maintenance work, so the net CPU cost can go up even if some reads get faster.
• Tricky: Does parallel query always help? No. Parallelism can reduce wall-clock time, but if the query is already small or coordination is expensive, total CPU can stay high or even get worse.
• Tricky: Is a covering index always best? No. A very wide covering index may be too big to cache well, and that can hurt both reads and writes. The best index is usually the smallest one that removes the most work.

Common Mistakes:

• Blaming the database too early: The fix is not always hardware. First check whether the query is doing extra work, then tune the SQL and index design.
• Using functions on indexed columns: That often blocks index use. Rewrite the predicate into a range, or add an expression index only when the pattern is common.
• Adding indexes everywhere: More indexes can slow inserts and updates. Keep the ones that match real queries and remove the rest.
• Ignoring row counts in the plan: A plan can look short and still be expensive if each node processes far more rows than expected.

Memory Hook: Feed the CPU fewer rows, not faster rows. If the engine touches less data, the CPU naturally cools down.

Cheat Sheet:

• CPU bottleneck = too much computation, not just slow storage.
• Look for scans, sorts, hashes, and repeated expressions.
• Sargable predicates let indexes search; non-sargable ones often force more work.
• Good indexes reduce rows and CPU; bad indexes add write cost.
• Full scans can be best for tiny tables or low-selectivity filters.
• Always read the plan with actual row counts when possible.

Practice Tasks:

1. Take one slow query and rewrite any DATE(), LOWER(), or cast on the filtered column into a range predicate.
2. Add an index that matches both the WHERE clause and the ORDER BY clause, then compare the plan.
3. Find a query that returns many rows and remove one unnecessary join or sort step to lower CPU work.