Skillversitas

Query Performance

A query that is correct but slow is a bug at scale, and the performance of a data layer is the sum of a handful of recurring problems: the N+1 that issues a query per row, the missing index that turns a filter into a scan, the over-selected row that fetches ten columns for one, and the unpaginated list that materializes the table. The good news is that these are a closed set of patterns with a closed set of fixes, and the diagnostic is the one tool the whole tutorial built toward - .explain() and the generated SQL, which show exactly what the query does and where it is slow. This chapter covers the diagnosis, the four recurring problems and their fixes, and the profiling habit that keeps a fast data layer fast as the app grows.

Diagnosis: explain and the SQL

from peewee import *

q = (Article.select()
         .join(Author)
         .where(Author.name == "Ada")
         .order_by(Article.views.desc()))

# the SQL - what will actually run:
print(q)

# the EXPLAIN - how the database will run it:
# the access paths, the indexes used, the row estimates
for row in q.explain().execute():
    print(row)

# on PostgreSQL the richer EXPLAIN ANALYZE (via the raw form):
from peewee import Sql
plan = db.execute_sql("EXPLAIN ANALYZE " + str(q)).fetchall()
for line in plan:
    print(line)

# the read: a Seq Scan on a large table is the missing index;
# a Nested Loop fanning out per row is the N+1 shape;
# the row estimate vs the actual is the stale-statistics signal

The diagnosis is two layers. The SQL (print(q)) shows the statement - the joins, the filter, the projection - and is where a wrong shape is visible (a CROSS JOIN where an INNER JOIN was meant, a filter on a column that is not indexed). The explain() shows the plan - the access paths the database chose, the indexes it used or skipped, the estimated rows - and it is where the cost is: a Seq Scan (a full table scan) on a large table is the missing-index signature, a per-row fan-out is the N+1 signature in the plan, and a wide gap between the estimated and the actual rows is the stale-statistics case that needs an ANALYZE. On PostgreSQL the EXPLAIN ANALYZE (run via the raw execute_sql) adds the actual timings and row counts, which is the definitive "where did the time go" answer. The habit is the one the whole tutorial built: the query is inspectable, so the slow query is explainable, and the fix is a reaction to what the plan shows rather than a guess.

The N+1: Join or Prefetch

from peewee import *

# the bug: one query for the list, one per row for the author
rows = Article.select().where(Article.published == True).execute()
for a in rows:
    label = a.author.name        # a query, every iteration

# fix 1 - prefetch: the related rows in a second query, served
# from memory in the loop (the joins chapter's tool):
rows = Article.select().prefetch(Article.author).execute()
for a in rows:
    label = a.author.name        # in-memory, no per-row query

# fix 2 - join: when the query FILTERS or ORDERS on the author,
# the join brings the column into the one statement:
rows = (Article.select()
            .join(Author)
            .where(Author.is_active == True)
            .execute())

# the decision (the standing rule):
#   the loop only DISPLAYS the relation  -> prefetch
#   the query FILTERS/ORDERS on it       -> join

The N+1 is the most common and the most expensive recurring problem: the list query plus one related query per row, so a 100-row list is 101 queries instead of 2. The two fixes are the joins chapter's tools, chosen by the rule. When the loop only displays the relation (the author's name in the listing), prefetch loads all the related rows in a second query and serves the loop from memory - 2 queries total. When the query filters or orders on the related data (only the active authors' articles, ranked by the author's rating), the join brings the related columns into the one statement, because the filter needs them in the SQL. The rule is the decision, and the explain() is the check - the plan for the N+1 shows the per-row fan-out, and the plan for the fixed form shows the single scan plus the one related lookup. The profiling habit that catches it early is the query count per request: a request that should be two queries issuing a hundred is the N+1 announcing itself before the latency does.

The Missing Index: The Filter and the Join

from peewee import *
from playhouse.migrate import SqliteMigrator, migrate

# the query that is slow: a filter on a column with no index
q = Article.select().where(Article.author_id == 7)
# the plan: a Seq Scan - every row read, because there is no
# index on author_id to seek

# the fix: the index on the filtered column (the models chapter
# declared it; the migration adds it to the existing table):
migrator = SqliteMigrator(db)
migrate(migrator.add_index("article", ["author_id"]))

# the composite index for the composite filter:
# the query filters on (author, published) - the index is on the
# PAIR, in the order the filter uses them, not two separate ones:
migrate(migrator.add_index("article", ["author_id", "published"],
                           unique=False))

# the rule: the index matches the WHERE (and the JOIN's ON),
# leading with the most selective column, and the ORDER BY
# column when the query sorts - the index the query can use
# is the one its filter and sort describe

The missing index is the second recurring problem, and its signature is the Seq Scan in the plan on a filter that should be a seek. The fix is the index on the filtered column - declared in the model's Meta.indexes for a new table, added by the migration's add_index for an existing one. The composite filter gets the composite index, on the pair in the order the filter uses them, because a single-column index on author_id does not serve a filter on (author_id, published) the way the pair does. The leading column is the most selective (the one that narrows most), and the ORDER BY column is included when the query sorts, because the index can then supply the rows pre-sorted and the sort disappears. The index is not free - it is written on every insert and update, and too many indexes slow the writes - so the rule is the balance: index the columns the queries filter, join, and sort on, and the writes pay for it, which is the trade the workload makes explicit.

Over-Selection and the Unpaginated List


from peewee import *

# over-selection: fetching the columns the caller does not use
# the listing needs the title and the views - not the 4KB body:
rows = (Article.select(Article.id, Article.title, Article.views)
               .where(Article.published == True)
               .execute())
# the projection is the fix - fewer columns per row, less to
# transfer and to hold, and the statement says what it is for

# the unpaginated list: materializing the whole table
all_rows = Article.select().execute()        # 2M rows, in memory

# the fix: the page, with keyset pagination for the large table
page = (Article.select(Article.id, Article.title)
            .where(Article.id > last_id)
            .order_by(Article.id)
            .limit(50)
            .execute())

# and the COUNT when the caller needs the total (the cheap
# form on the indexed key, not a COUNT(*) over a joined query):
total = Article.select().where(Article.published == True).count()

The last two problems are the projection and the pagination. The over-selection is the query fetching the columns the caller does not use - the full body of every article for a listing that shows the title - and the fix is the field projection (select(id, title, views)), which is both the performance (less transferred and held) and the documentation (the statement names what it is for). The unpaginated list is the select() with no limit materializing the table in memory, and the fix is the page - the limit for the bounded case, the keyset pagination (id > last_id) for the large table the offset form re-scans. The count() the listing needs for the "page 3 of 40" is the aggregate over the filter, kept on the indexed key and out of the joined query, because a COUNT over a join is the count paying the join's cost. The four problems - the N+1, the missing index, the over-selection, the unpaginated list - are the closed set, and each has the fix the plan reveals: prefetch or join, the index, the projection, the page.

The Profiling Habit

  • Count the queries per request. A request issuing one query per row is the N+1, visible in the count before it is visible in the latency - the log line that saves the explain session.
  • Explain the slow query, read the plan. The Seq Scan is the missing index, the fan-out is the N+1, the estimate/actual gap is the stale statistics - the plan names the problem, and the fix is a reaction to it.
  • Index the filter, the join, and the sort - leading with the selective column - and remember the write cost, so the index set is the workload's, not a habit's.
  • Project the columns the caller uses and page the list that is not bounded - the two fixes that cost nothing and remove the most data at once.
  • Keep the transaction short - the performance chapter and the transactions chapter meet here, because a long transaction holds the locks and the pool slot the next query waits on, which is the latency that is not in any single query.

Summary

The performance of a data layer is a closed set of recurring problems with a closed set of fixes, and the diagnosis is the tool the whole tutorial built - the SQL (print(q)) for the shape and the explain() for the plan, where the Seq Scan is the missing index, the per-row fan-out is the N+1, and the estimate/actual gap is the stale statistics. The N+1 is fixed by prefetch (the loop displays the relation) or the join (the query filters or sorts on it); the missing index is the index on the filtered, joined, and sorted columns, leading with the selective one, added by the migration to the existing table; the over-selection is the field projection; and the unpaginated list is the page, keyset-paged for the large table. The habit that keeps it fast is the query count per request (the N+1's early signal) and the explain of the slow query (the plan that names the fix), with the transaction kept short so the locks and the pool slot are not the hidden latency. The query is inspectable, the plan is readable, and the fix is a reaction to what they show - which is the control that a fast data layer keeps as the app grows.