The Filter in the Wrong Place: 76x Faster by Moving One WHERE Clause

I was reviewing a PR that added a GET /accounts endpoint to a payments API. Each account has many transactions, and the endpoint returns each account's most recent transaction so the list can show last activity at a glance. It supports a partial name filter and paginates. The shape was sensible. The query looked reasonable until I saw where the name filter landed.

The ranking was a textbook window function. For each account, rank that account's transactions newest first, and keep rank 1. In Ecto it built up as a pipeline of subqueries:

defp ranked_transaction_query do
  from t in Transaction,
    select: %{
      account_id: t.account_id,
      amount: t.amount,
      currency: t.currency,
      inserted_at: t.inserted_at,
      rank:
        over(row_number(),
          partition_by: t.account_id,
          order_by: [desc: t.inserted_at, desc: t.id]
        )
    }
end

defp best_transaction_query(ranked) do
  from t in subquery(ranked), where: t.rank == 1
end

Then it joined accounts onto that best-transaction subquery, ordered by name, and only then, on the outer query, applied the name filter:

defp maybe_filter_by_name(query, name) do
  sanitized = "%" <> QueryUtils.sanitize_like(name) <> "%"
  where(query, [a, ...], ilike(a.name, ^sanitized))
end

Read top to bottom, it tells a clean story. Rank the transactions, take the newest per account, join the accounts, filter by name. Each step is correct in isolation. The results were right. The problem is the order those steps actually run in, and what the window function is forced to do because of it.

How a window function actually evaluates

Does Postgres push an outer ILIKE filter down into a window subquery so it only ranks matching accounts?

Not in this case. Postgres can push an outer filter into a subquery that contains a window function, but only when the filter is on the partition key itself. Dropping a whole partition cannot change the ranking of the rows that survive, so that push is safe. The name filter is on accounts.name, which is not the partition key, and is not even a column the ranking subquery outputs. There is nothing for the planner to push down.

So the window has to work from the unfiltered transaction input. row_number() OVER (PARTITION BY account_id ORDER BY ...) is defined relative to each whole account partition, so a filter that removes arbitrary rows from inside a partition would change the ranking. Postgres has executor optimizations and does not have to literally materialize and number every row, but the planner still cannot use accounts.name to shrink the input to this transactions-only window subquery.

The name filter sat on the outer query, after the ranking subquery had already produced its output. By the time ILIKE ran, the window node had still been planned over the whole transactions table. The filter was not pruning the expensive input. It was discarding finished work. Searching for one account still paid the full cost of processing all ~40k transaction rows through that ranking plan, then kept the handful that matched.

That was the inversion. The cheapest, most selective predicate in the query, a name match that usually returns a few rows, was the last thing to run.

What the numbers said

The endpoint logged the generated SQL. The unfiltered version shows the window running over the bare transactions table with no scoping at all:

SELECT a0."id", a0."name", t1."amount", t1."currency", t1."inserted_at"
FROM "accounts" AS a0
INNER JOIN (
  SELECT tt0."account_id", tt0."amount", tt0."currency", tt0."inserted_at", tt0."rank"
  FROM (
    SELECT ttt0."account_id", ttt0."amount", ttt0."currency", ttt0."inserted_at",
           row_number() OVER (
             PARTITION BY ttt0."account_id"
             ORDER BY ttt0."inserted_at" DESC, ttt0."id" DESC
           ) AS "rank"
    FROM "transactions" AS ttt0
  ) AS tt0
  WHERE (tt0."rank" = 1)
) AS t1 ON t1."account_id" = a0."id"
ORDER BY a0."name", a0."id"
LIMIT $1 OFFSET $2

The benchmark ran against 20k accounts and 40k transactions. The unfiltered listing came in around 66ms. With the filter applied the old way, ranking all 40k rows then matching the name, the filtered query measured about 61ms. Almost no savings, because the expensive part ran regardless of how few accounts matched. The filter was decoration on top of a full-table sort.

Moving the filter into the subquery

The fix is to make the name match happen first, so the window only ever sees transactions that belong to matching accounts. Resolve the matching account ids up front, then scope the ranking subquery to those ids:

defp matching_account_ids(name) do
  sanitized = "%" <> QueryUtils.sanitize_like(name) <> "%"
  from a in Account, where: ilike(a.name, ^sanitized), select: a.id
end

defp ranked_transaction_query(account_ids_query) do
  from t in Transaction,
    where: t.account_id in subquery(account_ids_query),
    select: %{
      account_id: t.account_id,
      amount: t.amount,
      currency: t.currency,
      inserted_at: t.inserted_at,
      rank:
        over(row_number(),
          partition_by: t.account_id,
          order_by: [desc: t.inserted_at, desc: t.id]
        )
    }
end

Same window, same partition, same ordering. The only change is that the input to the window is now a few accounts' transactions instead of all of them. The WHERE account_id IN (...) runs at the same query level as the window, so it applies before the window is computed. Because it filters whole accounts in or out, every surviving account still gets its correct rank 1. The PARTITION BY account_id work shrinks from 40k rows to whatever the name matched. A pg_trgm GIN index on accounts.name can make resolving those ids cheap, so the window starts from a tiny set.

The filtered query dropped from about 61ms to roughly 0.9ms. On the full benchmark the name-filter path sat at 0.86ms median against the 66ms unfiltered listing, about 76x faster. The query went from ranking the whole table then looking for Acme, to finding Acme then ranking only Acme's transactions.

Why the unfiltered case is still inherent

The unfiltered listing is the honest baseline for this query shape, and it stays at ~66ms no matter what. When there is no name to filter on, there is nothing in this plan to scope the window down to. The window subquery has to consider the full transaction set to produce each account's most recent one, and offset pagination makes the outer query produce the ranked account rows before it can skip to a page in the middle. Walking to page 500 measured about 76ms versus 66ms for page 1, the extra ~10ms being Postgres skipping the ~9,980 rows ahead of the offset. That penalty grows linearly with page depth, and it is the price of offset pagination over this full-table window plan. Cursor-based pagination, a LATERAL ... LIMIT 1 lookup, DISTINCT ON with the right index, or maintained latest-transaction state could attack the unfiltered path, but that is a separate problem. Moving the filter does not help the unfiltered path, and it was never supposed to.

The escaping aside

One more thing I checked while I was in there, because interpolating user input into an ILIKE pattern is its own small trap. The filter wraps the search term in %...%, but the term itself can contain LIKE metacharacters. Someone searching for 50% or a_b would otherwise have the % and _ interpreted as wildcards, quietly matching far more than they asked for. The helper escapes them before interpolation:

def sanitize_like(value) do
  value
  |> String.replace("\\", "\\\\")
  |> String.replace("%", "\\%")
  |> String.replace("_", "\\_")
end

Order matters here. Escape the backslash first, then the wildcards, otherwise you double-escape the backslashes you just added. With PostgreSQL's default LIKE escape behavior, % matches a literal percent sign and _ matches a literal underscore. If you want that contract to be visible at the SQL boundary, add an explicit ESCAPE '\' too. The wrapping %...% the query adds itself stays meaningful, and only the user's own metacharacters get neutralized.

The takeaway from this review: a window-function subquery can block useful predicate pushdown unless the predicate safely removes whole partitions. Anything placed after the ranking can only discard rows the window plan already paid to produce. A predicate that filters on the partition key can be pushed down for free, but anything more selective, like a name match, has to live before the window, inside the subquery, or it buys you nothing. Correct results are not the same as correct order of operations, and with window functions the order is where the cost hides.