Planned lessonMID-013-L06

Query rewrites that help

Lesson 6 of 8
Mid-level

On This Lesson 4 topics

In this lesson
Planned topics
Draft outline details
Practice

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

apply common rewrites that make queries index-friendly.

Planned topics

No function on an indexed column: WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01', not date_trunc(...) = ... (or add an expression index).
Keyset pagination (WHERE (created_at, id) < (?, ?) ORDER BY ... LIMIT n) instead of deep OFFSET.
Select only needed columns; avoid SELECT *.
EXISTS instead of IN (big subquery) or COUNT(*) > 0.
Fix N+1 from the ORM at the source with a join or batch fetch (details in MID-021).

Draft outline details

Demonstration

sql

-- slow: OFFSET 500000 reads and discards 500k rows
-- fast:
SELECT * FROM orders WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 20;

Check your understanding

Why is OFFSET 500000 slow?

Show answer

PostgreSQL must produce and throw away all skipped rows.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson