Lesson 1 of 5
QuerySets & Laziness
"QuerySets are lazy — the act of creating a QuerySet doesn't involve any database activity." — Django documentation, "Making queries"
That sentence is the most important one in the ORM documentation, and it is responsible for both Django's best ergonomics and its worst performance surprises.
The good half: because building a queryset does nothing, you can pass one around, add filters in three different functions, hand it to a template, and pay for exactly one query at the end. A view can compose a query out of conditions scattered across a codebase without anyone coordinating.
The bad half is the same property seen from a different angle. Nothing tells you when the query runs. A queryset in a template that gets iterated twice, a len() where you meant .count(), a loop that touches a related object — each of those is a database round trip you did not write and cannot see in the line that caused it.
So this chapter builds a queryset. By the end you will know precisely which operations trigger SQL, what gets cached, what silently discards the cache, and how to read a piece of ORM code and count the queries before running it.
Three chained .filter() calls issue no queries and one len() issues one. What exactly is a queryset holding before it is evaluated, which operations force the trip to the database — and why does if orders: followed by orders.count() cost two queries when if orders.exists(): costs one?
Building a queryset runs no query
Click Run to execute the code.
Three chained calls, zero queries. A queryset is a description of a query — a table, a list of conditions, an ordering — plus an empty result cache. Nothing happens until something asks for the rows.
That is what makes composition free. A get_queryset() on a view, a filter() in a mixin and an order_by() in a template tag can each add to the description, and the cost is one query at the end rather than three.
A queryset is an order form, not a delivery. You can add lines to it, change the delivery address, cross something out and hand it to a colleague to add their items — and nothing has been picked, packed or driven anywhere. The warehouse only moves when someone submits it. That is why building a query costs nothing and why it composes so freely across functions that never coordinate. The trap is the same one you would hit with a real order form: it is not obvious from looking at the paper whether it has been submitted, and if you submit it twice you get two deliveries. That is the whole of this chapter — knowing which gestures are "still writing" and which are "send it", because the two look almost identical in the code.
What triggers evaluation
Click Run to execute the code.
The list is short and worth memorising: iteration, list(), len(), bool(), repr(), and indexing all evaluate. Everything that returns another queryset — filter, exclude, order_by, values, unevaluated slicing — does not.
repr() is the one that catches people, because it means printing a queryset runs it. A print(qs) added while debugging, or a queryset interpolated into a log message, issues a query that was not there before — and in a loop, one per iteration.
The result cache, and how to lose it
Click Run to execute the code.
The cache lives on the queryset object, so reusing the same object is free and rebuilding an identical query is not. Django caches the whole result list on first evaluation, and every later iteration reads from it.
Two practical consequences. A view that will use the rows more than once should do rows = list(qs) and pass the list — which is explicit about the single query and immune to accidental re-evaluation. And any chaining method returns a new queryset with an empty cache, so qs.filter(...) after evaluating qs costs a second query even though it looks like a refinement of work already done.
Chaining clones, so the original is untouched
Click Run to execute the code.
Every chaining method returns a new queryset. That immutability is why passing a queryset into a function is safe: the callee cannot narrow the caller's version by accident, which is exactly the property that makes the get_queryset() override pattern work across a mixin chain.
It is also the reason the cache is lost on every refinement — a new object has a new empty cache, and there is no way for it to inherit one, because its query is different.
filter, exclude, and combining conditions
Click Run to execute the code.
filter(**kwargs) combines its arguments with AND, which covers most cases and cannot express OR. Q objects are conditions as values — buildable, combinable with &, | and ~, and passable around before any query exists.
The teaser at the end matters and gets its own treatment next chapter: across a join, filter(a=1).filter(b=2) and filter(a=1, b=2) mean genuinely different things, and the difference is the single most surprising thing about Django's ORM.
Slicing is LIMIT/OFFSET — until it is not
Click Run to execute the code.
An unevaluated slice becomes LIMIT and OFFSET, so Order.objects.all()[10:20] fetches ten rows rather than all of them and throws the rest away. That is what makes pagination cheap.
Three edges are worth knowing. Indexing (qs[5]) evaluates immediately, because a single object cannot be a lazy queryset. A step (qs[::2]) evaluates, because SQL has no equivalent. And you cannot filter after slicing — Django raises, because applying WHERE after LIMIT would change which rows the limit selected.
count() vs len(), exists() vs bool()
Click Run to execute the code.
count() issues SELECT COUNT(*) and exists() issues SELECT 1 ... LIMIT 1. Both are dramatically cheaper than loading five thousand rows to ask a question about them — which is what len() and bool() do.
But the guidance reverses when you are going to use the rows anyway. if qs.exists(): rows = list(qs) is two queries; rows = list(qs); if rows: is one. The optimisation is only an optimisation when the answer is all you wanted, and the common mistake is applying it reflexively.
values(), values_list() and only()
Click Run to execute the code.
values() and values_list() skip model instantiation entirely and hand you dicts or tuples. For a report, an export or an aggregation that never calls a model method, that is a genuine saving on both query size and object construction.
The cost is that you lose the model. No __str__, no properties, no get_absolute_url() — which is why a template rendering values() output often shows blanks rather than errors, given the silent-lookup behaviour from Part II.
only() and defer() keep the instances and defer specific columns — useful for a table with a large TextField you rarely read. The trap is that touching a deferred field triggers a query per instance, so defer() plus a loop that eventually reads the field is worse than not deferring at all.
iterator(): opting out of the cache
Click Run to execute the code.
iterator() is Volume 14's laziness argument arriving in the ORM. It uses a server-side cursor where the backend supports one, yields rows in chunks, and keeps no result cache — so memory stays flat regardless of table size.
The trade is exactly the one Part IV of Volume 14 described for generators: no length, no second pass, no reuse. That makes it correct for a data migration or a nightly export and wrong for anything a template touches more than once.
The lazy queryset arrived with the magic-removal branch in 2006, which moved querying from module-level functions like orders.get_list(status="open") onto the chainable objects manager that exists today. Laziness was the enabling decision: once a queryset is a description rather than a result, chaining composes and a framework can build queries across layers it does not control — which is what makes generic class-based views possible at all. Q objects came in the same reorganisation, for the case keyword arguments structurally cannot express: OR. Later additions were about scale rather than expressiveness — values_list(flat=True), only()/defer() for wide tables, and iterator() for tables that do not fit in memory, which gained an explicit chunk_size in Django 2.0 and support for prefetching in 4.1, closing a long-standing gap where iterator() and prefetch_related were mutually exclusive.
Print the SQL before you trust your reading of it: print(qs.query) in a shell shows exactly what a queryset would issue, without running it, and it is the fastest way to check whether your chained filters produced the WHERE clause you expected. Then count queries rather than guessing — from django.db import connection; connection.queries after DEBUG=True, or better, wrap a block in django.test.utils.CaptureQueriesContext and assert the number in a test, which turns "this endpoint should be one query" into something CI enforces. The habit worth building is to read every view asking "how many times is this queryset evaluated?" — the answers that surprise people are a template looping twice, a len() where .count() was meant, and a print() left in a loop, since repr() evaluates. And django-debug-toolbar exists precisely to make that count visible per request; installing it on a project you have not measured is usually an unpleasant afternoon and a valuable one.
Laziness produces a specific and very common production incident: a queryset passed into a template that iterates it in two places, doubling the query count on a page nobody changed — the code looks identical and the database load is not. len(qs) on a large table is the other classic, loading every row to produce a number that COUNT(*) would have returned without leaving the database. The exists()-then-iterate anti-pattern shows up wherever someone applied an optimisation tip without the condition attached to it, and it silently doubles queries on the happy path. On the memory side, management commands that loop over Model.objects.all() without .iterator() are a routine cause of workers being killed by the OOM reaper on tables that grew past expectations — the code was correct when the table had 50,000 rows. And the reason django-debug-toolbar is close to universal in Django development is that none of these are visible in the source; they are only visible in the query count.
Common Mistakes
- Assuming a chained filter reuses previous work. Every chaining call returns a new queryset with an empty cache, so refining after evaluating costs another query.
- Iterating the same queryset in two places. The cache lives on the object, so rebuilding an identical query is a second trip. Materialise with
list()and pass the list. len(qs)when you want a count. It loads every row;count()issuesSELECT COUNT(*).if qs.exists():followed by iterating it. That is two queries;rows = list(qs); if rows:is one. The optimisation only applies when the answer is all you need.- Printing a queryset in a loop.
repr()evaluates, so debug output can add a query per iteration. - Expecting
filter()to express OR. Keyword arguments are ANDed;ORneedsQobjects. - Filtering after slicing.
LIMITis already applied, so Django raises rather than silently changing which rows were selected. - Rendering
values()output in a template expecting model behaviour. They are dicts — no methods, no properties — and the template's silent lookup makes that show as blanks. defer()on a field the code later touches. Each access triggers another query, per instance, which is worse than never deferring.- Looping a large table without
iterator(). The whole result set is cached in memory, which is how management commands get OOM-killed on tables that grew.
Interview Corner
filter(), exclude() or order_by() returns a new queryset that just describes the query, and no SQL runs. It evaluates when something needs the rows: iterating it, calling list(), len() or bool(), indexing it like qs[0], or repr() — which means printing a queryset runs it, and that catches people who add a print inside a loop while debugging. Once evaluated, the results are cached on that queryset object, so iterating it a second time is free. But the cache lives on the object, so a rebuilt query is a fresh trip, and any chaining call returns a new object with an empty cache. Slicing is the useful special case: qs[10:20] stays lazy and becomes LIMIT and OFFSET, so pagination fetches ten rows rather than all of them — although a slice with a step, or a plain index, evaluates immediately.count() instead of len(), and when would that be the wrong choice?count() issues SELECT COUNT(*) and returns a number; len() loads every row into memory and then measures the list. So if the count is all I want, count() is enormously cheaper on a large table — same for exists(), which issues SELECT 1 ... LIMIT 1 rather than fetching everything to check truthiness. The reversal is when I am going to use the rows anyway: if qs.exists(): rows = list(qs) is two queries, because exists() does not populate the result cache, whereas rows = list(qs) followed by if rows: is one. So the rule is that count() and exists() are for when the answer is the whole point, and plain len()/truthiness are correct when the rows are about to be used — and applying the optimisation reflexively silently doubles queries on the happy path. One nuance worth knowing: if the queryset is already evaluated, count() reads the cache rather than issuing a second query, so the cost depends on the state of the object rather than on the method name alone.CaptureQueriesContext in a test, or debug-toolbar in development — because "database time" splits into two very different problems: too many queries, or one slow query. Too many usually means laziness is being triggered somewhere invisible: a queryset iterated twice because the view passed it to a template that loops in two places, a len() that should be count(), or a related-object access inside a loop, which is the N+1 problem and the subject of the next chapter. The fixes there are materialising with list() once, replacing counting operations, and select_related/prefetch_related. If it is one slow query, I look at what the ORM actually emitted with print(qs.query) and then at the plan with EXPLAIN, because the usual causes are a missing index, an unindexed Meta.ordering applying a sort to every fetch, or a filter that cannot use an index. Then I reduce what is transferred: values() or values_list() when the code never calls a model method, since that skips instantiating objects entirely, and only()/defer() for a wide table with a large text column — being careful that a deferred field touched later costs a query per instance. For anything that walks a large table I use .iterator(), because the default caches every row and that is how a report that worked at 50,000 rows gets the worker OOM-killed at five million. And whatever I fix, I add a test asserting the query count, because this class of regression is invisible in code review.Build the lazy queryset. _clone must produce a new object with an empty cache, query() must assemble the SQL in the right order, _fetch must run exactly once per object, slicing must stay lazy as LIMIT/OFFSET, and count()/exists() must avoid loading rows. The laziness checks are the ones that matter — building must issue zero queries, and a second iteration must issue none. The checklist reports each requirement as PASS, FAIL or ERR; run it before writing anything and watch the lines flip.
Click Run to execute the code.
Summary
- Building a queryset issues no query. It is a description — table, conditions, ordering — plus an empty cache, which is what makes composition across layers free.
- Evaluation is triggered by iteration,
list(),len(),bool(),repr()and indexing. Everything returning another queryset stays lazy. repr()evaluates, so printing a queryset — in a debugger, a log line, or a loop — issues queries that are not in the code you wrote.- The result cache lives on the object. Reuse it and later passes are free; rebuild the same query and you pay again.
- Chaining clones. The original is untouched, which makes passing querysets safe — and means every refinement starts with an empty cache.
filter()ANDs its keyword arguments.ORneedsQobjects, which are conditions as values and can be combined with&,|and~.- An unevaluated slice becomes
LIMIT/OFFSET. Indexing and stepped slices evaluate, and filtering after slicing is refused rather than silently wrong. count()andexists()beatlen()andbool()— unless you need the rows. Thenexists()first is two queries where materialising once is one.values()skips model instantiation and hands you dicts, losing methods and properties;only()/defer()keep instances but cost a query per instance on a deferred access.iterator()trades the cache for flat memory. Correct for large one-pass work, wrong for anything iterated twice.
In the next chapter, Relationships & Joins, we follow a foreign key into SQL: what select_related actually generates, why filter(a=1).filter(b=2) and filter(a=1, b=2) differ across a join, and how the ORM decides between a JOIN and a second query.
Laziness is the ORM's best idea and the source of its worst surprises, and the two are inseparable rather than a good feature with a flaw attached. What laziness buys is that a query can be assembled by code that does not know about itself — a base view, a mixin, a filter backend, a template tag — and still cost one round trip. What it costs is that the moment of execution is not written anywhere; it is implied by a later, ordinary-looking expression. Every deferred-execution system has this shape, from generators to promises to build graphs, and they all share the same failure: the code that causes the work is not the code that looks like work. The practical defence is not to distrust laziness but to make the invisible thing countable — which is why "assert this endpoint makes three queries" is one of the highest-value tests a Django project can have, and why almost nobody writes it until after the first incident.
Q&A · 0
Enrol to ask questions and join the discussion.
No questions yet — be the first to ask.