Database Management · Advanced level

Advanced level · Module 2: Performance Tuning · Lesson 3 of 4

Rewriting Slow Queries

Improve queries by changing how they are written: avoiding N+1 patterns, functions on indexed columns and unnecessary work.

In this lesson you will

  • Rewrite queries to use indexes and avoid repeated work
  • Fix N+1 patterns from applications

Key concepts

sargable conditionsN+1 queriespagination with keysavoiding SELECT *

The lesson

Conditions the database can use an index for are called sargable. WHERE visit_date >= '2026-01-01' AND visit_date < '2026-02-01' can use an index; WHERE extract(month FROM visit_date) = 1 usually cannot.

Applications sometimes run one query per item in a list — the N+1 problem. Replace them with a single join or an IN query; in logs, a burst of near-identical queries is the sign.

Fetch only the columns needed rather than SELECT *, and paginate large lists by key (WHERE id > last_seen ORDER BY id LIMIT 50) rather than large OFFSET values, which get slower page by page.

Step by step

  1. Rewrite a date filter to be sargable and compare plans.
  2. Replace an N+1 pattern with one query.
  3. Replace OFFSET pagination with key-based pagination.
  4. Measure each change.

Practice activity

Rewrite the remaining slow queries and complete the before-and-after tuning report.

Expected result: Each rewritten query shows a measured improvement with the same results.

Check your understanding

  1. Why can WHERE extract(month FROM visit_date) = 1 be slow?

    Show answer

    The function on the column usually prevents index use.

  2. Why is key-based pagination faster than large OFFSETs?

    Show answer

    The database does not have to read and skip all the earlier rows.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.