Query Performance Anti-Patterns

Systematic Query Profiling covers forming a hypothesis before changing anything. These three patterns are common enough, and cheap enough to check, that they are worth ruling out — or confirming — as that first hypothesis before a longer investigation.

N+1 queries#

Fetching a list, then issuing one additional query per item to get related data, instead of one query (or one batch) that gets everything at once:

1 query:  SELECT * FROM orders WHERE customer_id = 42N queries: for each order, SELECT * FROM order_items WHERE order_id = ?

For 500 orders, this is 501 round trips instead of one or two. Distributed Systems Fundamentals covers why a network call is not "a slower function call" — it has real, fixed per-call latency on top of the actual work, and N+1 pays that fixed cost N extra times over. The fix is almost always a single query with a JOIN, or a single WHERE order_id IN (...) batched across every ID gathered from the first query, instead of one call per row — SQLAlchemy's own documentation on relationship loading covers this exact trade-off for anyone working through an ORM, where N+1 is especially easy to introduce without noticing.

Implicit type conversion defeating an index#

Comparing a column to a value of a different type can force an implicit type conversion on every row's value before comparing — the same function-wrapped-column problem Index Theory covers, showing up a third time in this exact shape:

-- order_id is stored as a string columnWHERE order_id = 4021        -- numeric literal: forces a conversion                              -- on every row, disabling the index
WHERE order_id = '4021'      -- matches the column's actual type:                              -- the index stays usable

This is easy to introduce by accident — an application framework that passes a number where the schema expects a string, or a schema migration that changed a column's type without updating every query written against it.

SELECT * and unbounded result sets#

Predicate and Projection Pushdown already covers SELECT * defeating projection pushdown in a file-based system; the same waste applies inside a database query, fetching columns nothing downstream actually uses. The larger version of the same mistake is fetching an unbounded result set — no LIMIT, no pagination — pulling potentially millions of rows into application memory to filter or aggregate there, work the database itself could have done far more efficiently before ever sending the data across the network:

-- Pulls every row across the network, aggregates in application codeSELECT * FROM orders;
-- The database aggregates before sending anything backSELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

The general principle behind both halves of this pattern: push computation to where the data already is, rather than moving the data to where the computation happens to be written.

Common mistakes#

Applying every pattern in this article without measuring first#

These are common causes, not guaranteed ones — Systematic Query Profiling still applies: confirm one of these is actually present in the specific slow query before spending time rewriting it.

Premature optimization applied to a query that isn't actually slow#

"Premature optimization is the root of all evil," in Donald Knuth's famous phrasing, applies here directly — rewriting a query nobody has measured as slow, against a pattern from this list, spends real effort for a benefit that may not exist. Profile first; this article is a list of places to look once a query is confirmed to need attention, not a checklist to apply everywhere by default.

Fixing the anti-pattern without confirming the fix helped#

Rewriting SELECT * to name explicit columns, for instance, is low-risk — but the discipline from Systematic Query Profiling still applies: measure before and after, under the same conditions, rather than assuming a textbook fix automatically produced a measurable improvement in this specific case.

Further reading#

• Distributed Systems Fundamentals — why a network round trip is never free, the root cause behind why N+1 is expensive.
• Index Theory — the full explanation of why a function or type conversion wrapped around a column disables an index.
• Predicate and Projection Pushdown — the file-format-level version of SELECT *'s cost.
• Database index and B-tree — what an implicit type conversion actually disables.

See also#