Eating humble pie

Harper’s Bazaar Serbia was moving from an existing custom solution to WordPress. I had rebuilt the site so that it looked identical to the old version from the outside. There was no obvious sign the underlying system had been replaced. The editors had the controls they needed.

I had won. Then it went into production.

The homepage became painfully slow. Category pages still loaded quickly, so “WordPress is slow” was not much of an explanation. Something about the homepage workload was different.

The perfect five posts

The homepage was made up of several editorial sections. There was a Featured post at the top, a Latest section and a series of category sections further down the page. Posts could also be explicitly excluded from the homepage through a custom field in the editor.

Then there was duplication to prevent.

If a post had already appeared in Latest, it generally should not appear again in one of the category sections. The same applied to the Featured post, unless an editor had intentionally selected it.

The category sections had their own management in the WordPress admin. A section might contain five posts. An editor could choose some of them manually, while the remaining positions would be filled automatically with the latest suitable posts from that category.

Those automatically selected posts still had to obey everything that had happened above them. The query was no longer “Give me the latest five posts from this category.” It was closer to “Give me the latest five posts from this category, except the ones explicitly excluded from the homepage, the Featured post, anything already used in Latest, anything manually selected elsewhere and anything already consumed by one of the previous sections.”

WordPress already had an API for that kind of exclusion, so I used post__not_in. As the homepage was assembled, the list of post IDs that should no longer be returned grew, and each subsequent query received that exclusion list.

At the time, it made perfect sense. The requirement said those posts should not be returned, the query API let me express exactly that, and the results were correct.

Looking below WP_Query

When the performance problem appeared, I did not immediately know where it was coming from.

My first instinct was the usual answer to performance trouble. Cache something. Instead, I started reading.

I went through WordPress performance discussions, documentation and blog posts, and post__not_in kept appearing as something worth being suspicious of.

Suspicious was not enough.

Until then, I had mostly thought about WP_Query through its documented interface. I knew which arguments produced which results. Now I needed to understand what those arguments actually made WordPress do. That meant SQL. A simple WP_Query is not the query the database sees. It is an abstraction that WordPress translates into SQL.

A category condition can involve WordPress's taxonomy tables. Metadata lives separately from the post itself. Sorting and limiting add their own constraints. post__not_in becomes an exclusion condition over the post IDs.

None of those things is inherently bad. A JOIN is not automatically expensive. NOT IN is not automatically a performance problem. The problem was the workload we had created.

The homepage made several separate queries during a single request. Those queries included taxonomy work and ordering, while each successive section carried an increasingly large, changing exclusion state. Each query still had to find the exact number of valid posts after applying those conditions.

LIMIT 5 only meant that five rows came back. It did not mean the database only needed to consider five rows to find them.

Stop asking for the perfect result

The fix was not to make the same query slightly cheaper. It was to ask for a different result.

The database did not need to know everything that had happened while the homepage was being assembled, and it did not need to return the perfect final five posts. It only needed to return enough good candidates.

So I changed the approach.

At the beginning of the homepage request, I queried the metadata for posts explicitly excluded from the homepage once. That query returned only post IDs, which were added to a shared tracker. The Featured post was added when it should not be reused, and as the homepage was built, every post that was actually consumed was added to the same tracker.

Every section used the same shared tracker, which stored only post IDs. It did not need complete WordPress post objects. Its job was simply to know whether a post had already been excluded or used.

For each category section, I first narrowed the tracker’s IDs to those that were actually relevant to that category. That told me how many candidates the query needed to return. The query fetched only IDs and deliberately returned more than the section needed, enough to cover the five available positions, posts the editor might already have selected manually, and the relevant posts the tracker could reject.

PHP handled the final selection. It removed already used, excluded and manually selected IDs, filled the remaining positions with the latest suitable posts, and only then fetched the final post objects needed for rendering.

The separate section queries remained. What changed was the work I was asking them to do. The database produced a bounded candidate set, while the request-specific editorial state was resolved in PHP.

After the changes, the homepage returned to the kind of load time we expected.

The abstraction was not the problem

I recently found something that made me smile.

WordPress VIP now explicitly documents post__not_in as something to be careful with in performance-sensitive queries, and recommends a familiar alternative. Request some additional posts and perform the exclusion in PHP.

At the time, I had found that approach by digging through documentation, articles and Stack Overflow. I do not mention that because it proves I had discovered some uniquely clever solution. I had not. The more interesting part was what had changed between the first implementation and the second.

In the first, I knew how to use WP_Query. In the second, I had started to understand what WP_Query was doing.

When the site first worked, I felt like I had figured WordPress out. In one sense, I had. I could make the platform do what the project required.

What production exposed was the part I had not learned yet. Getting the right result through an abstraction is not the same as understanding the work required to produce it.

That was the useful part of eating humble pie. I had not been wrong to feel proud of what I had built. I had mistaken making it work for understanding the whole problem.