KAHIBARO
Discord Login Register ☰
☰

26.5 Query Optimization

Table of Contents

Why Query Optimization Matters

When an application feels “slow,” the database is very often the bottleneck. Most backend requests eventually hit a database, and inefficient queries can turn a fast API into a painful user experience.

Query optimization is the process of making your database queries do less work and return results faster, without changing what they return.

Goal of query optimization:
Reduce the time and resources needed to execute queries, while preserving correctness and readability.

In this chapter you will see how to read queries critically, how to measure them, and how to apply typical optimization techniques.

You will not learn SQL syntax from scratch here, that is covered in the SQL and PostgreSQL chapters. Here we focus on how to make existing queries faster.

Basic Principles of Query Optimization

Do Less Work

Almost every optimization falls into one of these categories:

PrincipleExamples
Read fewer rowsUse WHERE filters, indexes, pagination, LIMIT, avoid SELECT *
Read less data per rowSelect only needed columns, avoid big blobs when not needed
Avoid repeated workCache results, reuse joins, move repeated subqueries
Let the database use indexesWrite predicates that can use indexes, design proper indexes
Use cheaper operationsUse JOIN instead of multiple queries, use aggregation efficiently

A very simple mental model:

Rule:
If a query touches fewer rows and fewer columns, it is usually faster.

Measuring Query Performance

Optimizing without measuring is guesswork. You must:

  1. Measure how slow a query is.
  2. Change something.
  3. Measure again.

Using `EXPLAIN` and `EXPLAIN ANALYZE`

Most relational databases have a way to show how a query will be executed. For example in PostgreSQL:

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

This outputs a query plan. It explains whether the database will:

EXPLAIN shows the plan with estimated costs only.

EXPLAIN ANALYZE actually runs the query and shows:

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

Example output (simplified):

text 
Index Scan using users_email_idx on users  (cost=0.15..8.17 rows=1 width=120)
  Index Cond: (email = 'alice@example.com'::text)
Execution Time: 0.150 ms

Key things to look at:

FieldMeaning
Seq ScanSequential scan, database walks through all table rows
Index ScanUses an index, usually much faster for selective lookups
rowsNumber of rows estimated and actually processed
Execution TimeTotal time to run the query

Rule:
Use EXPLAIN and EXPLAIN ANALYZE to confirm whether your query uses indexes and to see where time is spent.

Reducing the Data You Query

Avoid `SELECT *`

SELECT * returns all columns, even those you do not need. This increases:

Bad:

sql 
SELECT * FROM users WHERE id = 42;

Better:

sql 
SELECT id, email, created_at
FROM users
WHERE id = 42;

This is especially important when:

Filter as Early as Possible

Write queries so that the database can filter rows before doing expensive work like joins or aggregations.

Bad pattern, filters in application:

python 
# Python
rows = db.execute("SELECT * FROM orders")
recent = [r for r in rows if r.created_at >= '2024-01-01']

Better, filter in SQL:

sql 
SELECT id, user_id, total_amount, created_at
FROM orders
WHERE created_at >= '2024-01-01';

Use Pagination

Fetching too many rows at once is a very common performance problem.

Bad:

sql 
SELECT id, title FROM articles ORDER BY created_at DESC;

This could return millions of rows.

Better, limit results:

sql 
SELECT id, title
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

You can then request the next page with a higher OFFSET, or, even better, with keyset pagination (using the last seen value, for example created_at < last_seen_created_at).

Using Indexes Effectively

You already learned what indexes are in earlier chapters. Here you will see how query structure affects whether an index is used.

Check if a Query Uses an Index

Use:

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

If you see Seq Scan on users, it does not use an index:

text 
Seq Scan on users  (cost=0.00..431.00 rows=10 width=120)
  Filter: (email = 'alice@example.com'::text)

If you see Index Scan, it does:

text 
Index Scan using users_email_idx on users ...

Write Index-Friendly Conditions

Some patterns prevent index usage.

Functions on Indexed Columns

Assume an index on created_at:

sql 
CREATE INDEX idx_orders_created_at ON orders(created_at);

Bad, function on column:

sql 
SELECT * FROM orders
WHERE DATE(created_at) = '2024-08-01';

The database must compute DATE(created_at) for each row, so it cannot use the index properly.

Better, rewrite against a range:

sql 
SELECT * FROM orders
WHERE created_at >= '2024-08-01'
  AND created_at < '2024-08-02';

Operations That Break Index Use

Patterns that often block index usage on a column:

PatternProblemBetter alternative
WHERE LOWER(email) = 'x'Function on columnStore normalized emails and index that column
WHERE created_at + INTERVAL '1d' > now()Expression with columnRewrite as created_at > now() - INTERVAL '1d'
WHERE price * 1.2 > 100Expression with columnPrecompute or rewrite inequality

LIKE and Prefix Searches

With B-tree indexes, a pattern like 'abc%' can use an index. But '%abc' or '%abc%' cannot use the index effectively.

Good, index-usable:

sql 
SELECT * FROM users WHERE email LIKE 'alice%';

Bad, usually not index-usable:

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

For full text search or flexible LIKE patterns you might need:

Simplifying Joins and Subqueries

Combine Related Queries

A common beginner pattern is to send many small queries instead of one well-written join. This is called the N + 1 query problem.

Example in code:

python 
# Get last 100 orders
orders = db.execute("SELECT id, user_id, total FROM orders ORDER BY created_at DESC LIMIT 100")
users = []
for order in orders:
    user = db.execute("SELECT id, email FROM users WHERE id = %s", (order["user_id"],))
    users.append(user)

This runs 1 query for orders + 100 queries for users.

Better, use a join:

sql 
SELECT o.id as order_id,
       o.total,
       u.id as user_id,
       u.email
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 100;

This is a single query. The database can optimize it, use indexes, and avoid repeated network trips.

Avoid Unnecessary Joins

Sometimes you join tables you do not really need.

Bad:

sql 
SELECT o.id, o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01';

If you do not use any column from users, remove the join:

sql 
SELECT id, total
FROM orders
WHERE created_at >= '2024-01-01';

This often allows a simpler and faster query plan.

Replace Subqueries with Joins (When Helpful)

Correlated subqueries run once per row, which can be slow.

Example:

sql 
SELECT u.id,
       u.email,
       (
         SELECT COUNT(*)
         FROM orders o
         WHERE o.user_id = u.id
       ) AS order_count
FROM users u;

This may run the inner query many times.

Better, use a join and aggregation:

sql 
SELECT u.id,
       u.email,
       COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

The database can handle aggregation more efficiently than running many separate subqueries.

Using Aggregations Efficiently

Aggregations like COUNT, SUM, AVG, and GROUP BY can be expensive if performed on many rows.

Aggregate Only What You Need

Bad:

sql 
SELECT COUNT(*)
FROM orders;

If you only need the number of orders placed this year:

sql 
SELECT COUNT(*)
FROM orders
WHERE created_at >= '2024-01-01';

The filter reduces the number of rows that the database must aggregate.

Group on Indexed or Selective Columns

When doing GROUP BY, it helps if the grouping columns have indexes, especially if combined with a WHERE filter.

Example:

sql 
-- Suppose you have an index on (status, created_at)
CREATE INDEX idx_orders_status_created_at
  ON orders(status, created_at);
SELECT status, COUNT(*)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status;

The index can help the database locate only the relevant rows.

Query Rewriting Techniques

Sometimes small changes in SQL wording generate a very different query plan.

Use `IN` Instead of Many `OR`s

Bad:

sql 
SELECT *
FROM products
WHERE id = 1 OR id = 2 OR id = 3;

Better:

sql 
SELECT *
FROM products
WHERE id IN (1, 2, 3);

Most optimizers handle both, but IN is clearer and sometimes generates better plans, especially for longer lists.

Move Conditions Into JOINs

Consider:

sql 
SELECT *
FROM orders o
LEFT JOIN payments p ON o.id = p.order_id
WHERE p.status = 'FAILED';

This query is effectively an inner join, because rows with no payment will be filtered out by WHERE p.status = 'FAILED'.

If you actually want all orders and mark which ones have failed payments, change it:

sql 
SELECT *
FROM orders o
LEFT JOIN payments p
  ON o.id = p.order_id
 AND p.status = 'FAILED';

This keeps the left join semantics and may lead to more efficient plans.

Replace `DISTINCT` With Better Modeling

Using SELECT DISTINCT to fix duplicate results sometimes hides a modeling or join problem.

Bad pattern:

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

If you really want only users who have at least one order, and each user should appear once, you can:

sql 
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

EXISTS tells the database that you only care whether at least one row exists, not how many.

Avoiding N+1 in ORMs

In backends you often use an ORM like SQLAlchemy instead of hand-written SQL. ORMs can create performance problems automatically.

Example with a fictional ORM:

python 
posts = db.query(Post).limit(100).all()
for post in posts:
    print(post.author.email)

If author is lazily loaded, each access may run a separate query:

sql 
SELECT * FROM posts LIMIT 100;
SELECT * FROM users WHERE id = ...;  -- repeated 100 times

Typical solutions:

The idea is the same as with raw SQL. Replace many small queries with one well-structured query that joins the necessary tables.

Caching and Query Results

Sometimes the best optimization is not to hit the database at all.

Application-Level Caching

If you frequently run the same query with the same parameters, you can cache the result in memory or in Redis.

Example:

You can:

  1. Compute the result once.
  2. Store it in cache with a short expiration, for example 60 seconds.
  3. Serve many user requests from the cache.

Pseudo-flow:

text 
if cache has key "top10:2024-08-28":
    return cached result
else:
    run SQL query
    store result in cache with TTL 60s
    return result

This reduces database load, especially in high-traffic endpoints.

Rule:
Cache results of expensive, frequently requested queries that do not change very often. Always set an expiration time.

Practical Step-by-Step Optimization Workflow

When you encounter a slow endpoint that uses the database, you can follow a systematic process.

  1. Find the exact query
    • Enable query logging or log SQL from your ORM.
  2. Measure
    • Run EXPLAIN ANALYZE in a tool like psql or a database UI.
    • Note execution time and key operations (Seq Scan, Index Scan, etc).
  3. Reduce result size
    • Remove unused columns.
    • Add WHERE filters.
    • Add pagination (LIMIT).
  4. Make sure indexes are used
    • Check predicates for functions or expressions on indexed columns.
    • Add or adjust indexes if needed and if justified.
  5. Simplify joins and subqueries
    • Remove unnecessary joins.
    • Convert correlated subqueries to joins and aggregation, when appropriate.
  6. Consider caching
    • If the query is still heavy and requested often, cache its result.
  7. Re-measure
    • Run EXPLAIN ANALYZE again and compare.

Example:

Concrete Examples

Example 1: Slow “User Orders” Page

You have an endpoint:

sql 
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

Problem: It is slow for users with many orders.

Step 1: Limit results

The UI only shows 20 latest orders:

sql 
SELECT id, user_id, total_amount, created_at, status
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Step 2: Add an index

Create an index that matches the filter and sort:

sql 
CREATE INDEX idx_orders_user_created_at
  ON orders(user_id, created_at DESC);

Run EXPLAIN ANALYZE again and confirm Index Scan usage.

Example 2: Dashboard with Aggregations

Query:

sql 
SELECT
  DATE(created_at) AS day,
  COUNT(*) AS orders_count,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY day DESC
LIMIT 30;

This runs on millions of rows and is slow.

Possible optimizations:

  1. Restrict date range:
sql 
WHERE created_at >= now() - INTERVAL '60 days'
  1. Use materialized summary table:
    • Create a daily summary table order_daily_stats(day, orders_count, revenue).
    • Update it once per day or incrementally.
    • Dashboard reads from this smaller table:
sql 
   SELECT day, orders_count, revenue
   FROM order_daily_stats
   ORDER BY day DESC
   LIMIT 30;

This is a form of precomputation, which is a powerful optimization when raw data becomes very large.

Summary

You have seen how to think about and improve query performance:

Query optimization is a practical skill. The next time an endpoint is slow, use this chapter as a checklist and iterate with real measurements.

Views: 9

Comments

Please login to add a comment.

Don't have an account? Register now!

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