skip to content

In Django, when should you call exists() or count() on a QuerySet instead of using bool() or len()?

level: middleimportance: should knowfreq 55%

answer

  1. do you need the rows?
  2. LIMIT 1 and COUNT(*)
  3. both consult the cache first
  4. exists then loop costs two

basics

~20 s

Use exists() and count() when you need only a yes/no or a number: they send LIMIT 1 and COUNT(*) queries without loading rows. Use bool() and len() when the rows will be used anyway, since they fill the result cache.

solid answer

~40 s

`bool(qs)` and `len(qs)` evaluate the whole QuerySet, fetch every row into the result cache and then test or count the list. `qs.exists()` sends a query with ordering cleared, `LIMIT 1` and a constant column, and `qs.count()` sends `SELECT COUNT(*)`, so neither transfers rows. The choice depends on what happens next: if the view only shows a badge or branches on emptiness, use `exists()` or `count()`; if it will loop over the rows anyway, `if qs:` and `len(qs)` load them once and later reads are free, while `exists()` plus a loop costs two queries. Both methods check the cache first: on an already evaluated QuerySet, `count()` returns the cached length and `exists()` tests the cached list without a query.

code

python · 13 lines
python
orders = Order.objects.filter(status='open')

# Only a banner: one tiny query, no rows loaded
show_banner = orders.exists()          # SELECT 1 AS a ... LIMIT 1

# Only a badge: one COUNT(*)
badge = orders.count()

# Rows needed anyway: one SELECT total
if orders:                             # full SELECT, fills the cache
    for order in orders:               # cache
        print(order.pk)
    print(len(orders), orders.count()) # both from the cache

go deeper

for a junior

Remember that count() and exists() ask the database for a number or a yes/no, while len() and if qs load every row.

for a middle

Explain the SQL each call sends, and that count() and exists() read the result cache when the QuerySet is already evaluated.

for a senior

Choose per page whether to reuse one evaluated QuerySet or issue narrow queries, and spot exists-then-loop and len-for-count patterns in review.

for a principal

Weigh consistency against cost: numbers and lists from one evaluation always agree, while separate narrow queries are cheaper but can disagree under concurrent writes.

## Four ways to ask about a QuerySet's size Django offers two families of size checks: - **Python protocol operations**, `bool(qs)` (including `if qs:`) and `len(qs)`. These evaluate the QuerySet: they run the full SELECT, build every model instance, store them in the result cache, then test or measure the list. - **QuerySet methods**, `qs.exists()` and `qs.count()`. These ask the database a narrower question and return a bool or an int, not rows. ## What SQL each one sends | Call | SQL on an unevaluated QuerySet | Rows transferred | Fills cache? | On an evaluated QuerySet | |---|---|---|---|---| | `bool(qs)` | full SELECT | all | yes | reads the cache | | `len(qs)` | full SELECT | all | yes | reads the cache | | `qs.exists()` | `SELECT 1 ... LIMIT 1`, ordering cleared | at most one | no | reads the cache | | `qs.count()` | `SELECT COUNT(*) ...` | none | no | returns the cached length | The last column matters: `count()` and `exists()` are not "always a query". The source checks the result cache first, so calling them after the QuerySet has been iterated costs nothing. ## Choosing by what happens next 1. **Only a yes/no is needed**, such as showing a "you have open orders" banner: `exists()`. It stops at the first match and moves one tiny row. 2. **Only a number is needed**, such as a badge count: `count()`. Counting in the database avoids building thousands of model instances. 3. **The rows will be used anyway**, such as a list rendered below the banner: evaluate once with `if orders:` or by iterating, then use `len(orders)`. A separate `exists()` or `count()` first would be an extra round trip. 4. **A single object's membership**: `qs.contains(obj)` does `filter(pk=obj.pk).exists()` on an unevaluated QuerySet and a Python `in` on an evaluated one. Django's own documentation spells out case 3: if a QuerySet has not been evaluated but will be, `exists()` does more total work (one query for the check, another for the results) than `bool()`. ## Common mistakes - **`len(Order.objects.all())` for a count.** This loads the entire table into memory to produce one integer. Use `count()`. - **`if qs.count() > 0:`** Counting every match to learn whether there is at least one; `exists()` can stop at the first. - **`if qs.exists():` followed by `for x in qs:`** Two queries where one would do. - **Assuming `count()` is cached across QuerySets.** It only reuses the cache of the same object; `Order.objects.filter(...).count()` written twice runs two `COUNT(*)` queries. - **Templates.** `{{ orders|length }}` calls `len()`, so it evaluates the QuerySet; `{{ orders.count }}` calls `count()`. After a `{% for %}` over the same object, both are free. ## How big the difference is The gap grows with the size of the result, not the size of the table: - For a QuerySet matching a handful of rows, `bool(qs)` and `qs.exists()` cost about the same; the docs say `exists()` is faster, but not by a large degree, so the gain needs a large QuerySet. - For a QuerySet matching thousands of rows, `bool()` and `len()` transfer them all and build a model instance for each, which costs both database time and Python memory; `exists()` and `count()` return one small value. - `count()` still has to count every match in the database, so on huge filtered sets it is not free either; when an approximate figure or a cached number is acceptable, avoid counting on every request. So the choice is less about micro-optimising and more about not loading rows that nobody reads. ## Numbers that can disagree A count taken now and rows fetched a moment later come from two separate queries, and another request can insert or delete rows in between. If the page shows "12 orders" above a list, deriving both from one evaluated QuerySet guarantees they match; two queries only guarantee each was correct when it ran. Under autocommit, which is Django's default, each query sees the database as of its own execution. ## Rules of thumb - Need rows: evaluate once, then use `bool()` and `len()` freely. - Need only a fact about the rows: `exists()` or `count()`. - Unsure: measure with `assertNumQueries` rather than guessing.

  • Why might exists() still be slow on a large table?
    `exists()` stops at the first matching row, but finding it still depends on the WHERE clause: filtering on an unindexed column can force a scan before the first match appears. It avoids transferring rows, not the search. The fix is an index that serves the filter, not a different QuerySet method.
  • What does count() return on a QuerySet sliced with [:10]?
    At most 10. Django counts over the limited query, wrapping it as a subquery, so the result respects the slice. If you need both the total and a page of rows, call `count()` on the unsliced QuerySet and slice separately.

saying these in an interview costs you the question

  • len(qs) runs SELECT COUNT(*) under the hood
  • exists() always hits the database even after iteration
  • if qs.exists(): then looping over qs costs one query
  • count() is shared between identical QuerySets in a request
  • bool(qs) fetches only the first row