Juarez Lustosa
ProjectsWritingPT
All articles

A slow endpoint is not always an N+1

How to distinguish repeated Rails queries from an expensive PostgreSQL query plan, using a fictional example and a measurement-driven investigation.

3 min readJuarez Lustosapostgresql, rails, performance

An N+1 is a common explanation for a slow Rails list endpoint. Loading associations in advance can remove repeated queries, but that does not guarantee that the main query is efficient. These are different problems, and I investigate them separately.

The examples below use a fictional publishing application. The domain, table and column names, identifiers, and code are illustrative. They do not reproduce a company’s database schema, execution plans, configuration, or performance measurements.

The symptom

Imagine a list that loads posts and their authors. The request log shows repeated queries while the response is being serialized. After adding eager loading, the query count drops, but most of the request time is still spent on the query that selects the posts.

At that point, counting queries is no longer enough. I need to understand where the database is spending its time.

A fictional example

In this example, an application lists the latest posts written by authors from one publication:

SELECT posts.*
FROM posts
WHERE author_id IN (
  SELECT id FROM authors WHERE publication_id = 7
)
ORDER BY posts.id DESC
LIMIT 20;

All names and values in this example are invented. The question is whether the database can find the matching posts without examining too much unrelated data.

What to look for in the plan

An ordered B-tree index can help satisfy an ORDER BY without a separate sort. For a query with a filter and a LIMIT, the planner can consider different access paths: retrieving matching rows and sorting them, or reading an index in the required order and stopping once enough matches are found.

Reading in order can work well when matching rows appear early. If those rows are scarce in that part of the index, however, returning a short page may still require a lot of work. An index scan is not automatically a cheap query.

I use EXPLAIN (ANALYZE, BUFFERS) to compare estimated and actual row counts, execution time, loops, and buffer activity. The useful question is how much work the query performs to produce the requested result, not simply whether an index appears in the plan.

EXPLAIN ANALYZE executes the statement. I run it in a controlled environment with representative data and permission to perform the investigation.

The planner uses estimates derived from statistics. A large estimate-versus-actual gap is a reason to investigate data distribution, statistics, and query structure. It does not, by itself, prove that one particular rewrite is the solution.

An alternative to test

When the set of author ids is small, one option to compare is resolving it before querying the posts:

author_ids = Author.where(publication_id: 7).pluck(:id)
Post.where(author_id: author_ids).order(id: :desc).limit(20)

The resulting filter can be illustrated with these fictional values:

SELECT posts.*
FROM posts
WHERE author_id IN (11, 22, 33)
ORDER BY posts.id DESC
LIMIT 20;

This SQL illustrates the selected values; in the application, I let Active Record handle the query parameters instead of interpolating values into SQL.

Separating the queries changes the information available when the second query is planned. It is a hypothesis to measure, not a promise of a better plan. Parameter handling, indexes, statistics, and data distribution can all affect the result.

There are tradeoffs too: an extra database round trip, memory for the id list, and consistency between the two reads. For a large set of ids, keeping the operation in the database may be preferable. I compare alternatives before choosing.

How to tell the problems apart

  1. Check whether the number of association queries grows with the number of returned records. That is the pattern to investigate for an N+1.
  2. Identify which query dominates the request time. Reducing query count may leave the most expensive query unchanged.
  3. Read the execution plan with representative data. Compare work performed with rows returned, rather than focusing on an isolated node name.
  4. Check statistics, indexes, and query alternatives. A subquery is not inherently slow, and materializing ids is not a universal fix.
  5. Measure the whole request again. Include serialization, database round trips, and memory use when evaluating a change.

What I take from this

I separate association loading from the cost of the main query. I use logs to identify repeated work, execution plans to investigate database behavior, and measurements to decide whether a change helped. An N+1 can be real without being the main source of latency.

References