KAHIBARO
Discord Login Register ☰
☰

10.17. SQL Performance Basics

Table of Contents

Why SQL Performance Matters

When your application is small, almost any SQL query feels fast. Once you have thousands or millions of rows, badly written queries can make your whole backend feel slow.

In this chapter you will learn the basics of SQL performance, enough to:

More advanced optimization topics belong to later chapters, but the foundation starts here.

Goal: Your SQL should return the correct results in the simplest way that uses appropriate indexes and avoids unnecessary work.

How Databases Execute Queries (High Level)

Relational databases have a query planner or optimizer. When you send a SQL query, the database:

  1. Parses the SQL.
  2. Figures out possible ways to execute it.
  3. Chooses what it thinks is the cheapest plan.
  4. Executes that plan.

You can inspect this plan with commands like:

sql 
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

You will see steps like:

You do not need to understand every detail yet. The key idea is:

The same SQL query can be executed in different ways, and some ways are much faster than others.

Your job is to write queries and design schemas so that the planner can choose a fast plan, usually by using indexes and avoiding unnecessary work.

Reading Execution Plans (Conceptually)

Every database has its own format, but common ideas repeat.

Example in PostgreSQL style:

sql 
EXPLAIN
SELECT * FROM users WHERE email = 'alice@example.com';

Output might look like:

text 
Index Scan using users_email_idx on users  (cost=0.29..8.30 rows=1 width=128)
  Index Cond: (email = 'alice@example.com')

Key concepts that matter:

Very simplified rule:

Prefer query plans that use index scans for selective filters and avoid unnecessary sequential scans on large tables.

If you see repeated full table scans on big tables for simple lookups, performance will not scale.

Full Table Scans vs Index Scans

Full Table Scan (Sequential Scan)

A full table scan checks every row. For example:

sql 
SELECT * FROM users WHERE last_login_at > NOW() - INTERVAL '1 day';

If there is no index on last_login_at, the database must read all rows to check the condition.

This might be fine for a table with 100 rows, but painful for 10 million rows.

Index Scan

An index acts like a sorted phone book for one or more columns. With an index, the database can jump directly to the matching rows.

Example:

sql 
CREATE INDEX idx_users_last_login_at ON users(last_login_at);

Now the same query:

sql 
SELECT * FROM users WHERE last_login_at > NOW() - INTERVAL '1 day';

can use idx_users_last_login_at to find the relevant range instead of scanning everything.

Summary:

SituationLikely Scan TypePerformance on Large Table
No index on filter columnSequential scanSlow
Index on filter column, selectiveIndex / index-onlyFast
Filter matches almost all rowsSometimes seq scanSometimes seq scan is cheaper

The optimizer may still choose a full scan if almost all rows match, because jumping through an index may cost more than reading the table once.

Basic Indexing for Performance

You learned about indexes in a separate chapter. Here we focus on how they affect query speed.

Indexes help when:

Example schema:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    country     TEXT,
    created_at  TIMESTAMP NOT NULL
);

Built-in or typical indexes:

If you frequently query by country and creation date:

sql 
SELECT * FROM users
WHERE country = 'US'
  AND created_at >= NOW() - INTERVAL '7 days';

you might add:

sql 
CREATE INDEX idx_users_country_created_at
ON users(country, created_at);

Two important effects:

  1. Filters on country and created_at can be much faster.
  2. Sorting by created_at for a given country might not require an extra sort step.

Create indexes for columns that you query often, especially in WHERE, JOIN, ORDER BY, or GROUP BY, but avoid creating too many indexes because every index slows down writes.

Too many or unnecessary indexes can hurt insert, update, and delete performance, and increase storage.

Writing Efficient WHERE Clauses

How you write conditions can decide whether an index can be used.

Sargable vs Non-sargable Conditions

A "sargable" condition allows the database to use an index on a column. A "non-sargable" condition forces the database to check each row.

Non-sargable example:

sql 
-- Bad for index usage
SELECT * FROM users
WHERE LOWER(email) = 'alice@example.com';

If you have an index on email, this query may not use it because the function LOWER(email) changes the column.

Better approach:

Simpler for beginners:

sql 
-- Store lowercase email at insert time, then query directly
SELECT * FROM users
WHERE email = 'alice@example.com';

Another common non-sargable pattern:

sql 
-- Bad: function on the column
SELECT * FROM orders
WHERE DATE(created_at) = '2026-08-27';

Better:

sql 
-- Good: function on the constant, or no function at all
SELECT * FROM orders
WHERE created_at >= '2026-08-27'::date
  AND created_at <  '2026-08-28'::date;

This allows a regular index on created_at to be used.

Using AND and OR

Indexes work best when the database can use them directly for each condition.

sql 
SELECT * FROM users
WHERE country = 'US'
  AND active = true;

If you have an index on (country, active) or on country, the query can be fast.

OR conditions are often harder to optimize, for example:

sql 
SELECT * FROM users
WHERE country = 'US'
   OR email = 'alice@example.com';

The optimizer may still find a good plan, but OR can sometimes lead to more work. In real projects, you sometimes rewrite complex OR queries as UNION of simpler queries, which advanced chapters will cover.

Limiting Results and Pagination

Always avoid asking for more rows than you need.

sql 
-- Bad pattern in APIs or UIs
SELECT * FROM users;

If your table has a million users, the application will be overwhelmed.

Better:

sql 
SELECT * FROM users
ORDER BY id
LIMIT 50;

For APIs, combine ORDER BY with LIMIT and some pagination logic.

Basic offset pagination:

sql 
SELECT * FROM users
ORDER BY id
LIMIT 50 OFFSET 100;

But OFFSET can become slow for very large offsets, because the database still counts through the skipped rows.

Keyset pagination pattern is more efficient:

sql 
-- Get first page
SELECT * FROM users
ORDER BY id
LIMIT 50;
-- Then use the last seen id for the next page
SELECT * FROM users
WHERE id > 50
ORDER BY id
LIMIT 50;

This avoids scanning many skipped rows.

Never fetch unbounded result sets in production APIs. Always use LIMIT and some form of pagination.

Selecting Only Needed Columns

Selecting fewer columns means:

sql 
-- Bad if you only need names
SELECT * FROM users;

Better:

sql 
SELECT id, email FROM users;

This becomes more important when tables have large text or JSON columns.

In some databases, a query that only needs indexed columns can use an "index only scan," which can be even faster because it does not have to read the main table rows.

Avoiding N+1 Query Problems

The N+1 query problem happens when your application runs one query for a list, then another query for each item.

Example in pseudocode:

text 
1) SELECT * FROM users LIMIT 100;     -- 1 query
2) For each user, SELECT * FROM posts WHERE user_id = ?;   -- 100 queries
Total = 101 queries

If you have 10 000 users, this pattern can produce 10 001 queries.

Better: fetch related data in a single query with a JOIN.

sql 
SELECT u.id AS user_id,
       u.email,
       p.id AS post_id,
       p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.id IN (1, 2, 3, ..., 100);

Or:

sql 
SELECT * FROM posts
WHERE user_id IN (1, 2, 3, ..., 100);

Then group posts by user_id in your application code.

Summary:

PatternQueriesPerformance on N items
N+1 (per item)N + 1Usually slow
Single join or IN query1Usually much faster

If you see your backend making one small query in a loop, consider rewriting it to a single SQL query that fetches everything in one go.

Using JOINs Efficiently

JOINs are powerful but can also be slow if misused.

Index Join Columns

When you join tables, the join columns should usually be indexed.

Example:

sql 
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'alice@example.com';

Indexes that help:

Without index on orders.user_id, the database might:

With an index on orders.user_id, it can jump directly to the user's orders.

Avoid Unnecessary JOINs

Do not join tables if you do not need their columns.

Bad:

sql 
SELECT o.id
FROM orders o
JOIN users u ON o.user_id = u.id;

If you do not use any users columns or filters, the join is useless and only adds work.

Better:

sql 
SELECT id FROM orders;

Aggregations and GROUP BY

Queries with aggregates like COUNT, SUM, AVG, and GROUP BY can be expensive on large tables.

Example:

sql 
SELECT country, COUNT(*) AS users_count
FROM users
GROUP BY country;

The database must:

  1. Scan rows.
  2. Group them by country.
  3. Count each group.

Effective tips:

Less efficient:

sql 
SELECT country, COUNT(*)
FROM users
GROUP BY country
HAVING COUNT(*) > 1000;

Better:

sql 
-- Often similar, but in some cases pre-filtering helps:
SELECT country, COUNT(*)
FROM users
WHERE created_at >= '2026-01-01'
GROUP BY country
HAVING COUNT(*) > 1000;

You will learn more advanced aggregation optimizations later. For now, remember that aggregates on large tables can be heavy, so use filters and proper indexes.

Simple Query Rewriting Examples

Small changes sometimes make a big difference.

Example 1: LIKE vs Prefix Search

Query:

sql 
SELECT * FROM users
WHERE email LIKE '%example.com';

The leading % means the database cannot use a regular index on email. It must check every row.

If you only need to check the domain and you store emails as local_part@domain, you can split the domain into a separate column and index it:

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    local_part TEXT NOT NULL,
    domain TEXT NOT NULL,
    ...
);
CREATE INDEX idx_users_domain ON users(domain);
SELECT * FROM users
WHERE domain = 'example.com';

This is much faster on large datasets.

Example 2: In-application vs in-database filtering

Bad pattern:

text 
1) SELECT * FROM orders;
2) Filter orders in application code by created_at, status, etc.

Better:

sql 
SELECT * FROM orders
WHERE status = 'PAID'
  AND created_at >= NOW() - INTERVAL '30 days';

Let the database filter as much as possible. It is optimized for that work.

Measuring and Observing Query Performance

To improve performance, you must measure it.

Basic things you can do:

Example in PostgreSQL:

sql 
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';

This shows both the plan and real execution time.

Do not guess about performance. Measure query times and inspect query plans to understand what is slow.

Practical Checklist for Beginners

When you write or review a query that might run on a big table, use this checklist:

  1. WHERE conditions
    • Are they written so indexes can be used (no unnecessary functions on columns)?
    • Do you avoid SELECT * if not needed?
  2. Indexes
    • Are there indexes on the columns used in WHERE and JOIN?
    • Are there too many indexes on a table you update frequently?
  3. Result size
    • Does the query use LIMIT where appropriate?
    • Are you using pagination for API responses?
  4. JOINs
    • Are join columns indexed?
    • Are you joining only the tables you really need?
  5. N+1 issues
    • Is your application sending one query per item in a list?
    • Can you replace this with a single query using JOIN or IN?
  6. Aggregations
    • Are you aggregating only what you need and with appropriate filters?
    • Does a GROUP BY on a huge table actually need all rows?

If you follow this checklist, you will already avoid a large portion of common SQL performance problems that beginners run into.

You now have a working understanding of SQL performance basics: how indexes affect speed, what to look for in query plans, and how to avoid typical pitfalls such as full table scans, N+1 queries, and unbounded result sets. More advanced tuning will come later, but these foundations are what you will use every day as a backend developer.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3439 / 24h
Impressum / Imprint
Datenschutz / Data Policy