A developer on Twitter asked an honest question: is SQL's `FILTER` clause just syntax sugar for a `CASE` expression, or is there a real performance difference? According to blog.jooq.org, the jOOQ team ran a controlled benchmark on PostgreSQL 15 against the well‑known Sakila sample database, and the answer is a quiet but consistent win for `FILTER`.
If you write reporting queries that bucket aggregates by category, this is the kind of detail that adds up. Here is what the benchmark showed, why it matters, and how to get the same shape on databases that do not support `FILTER` natively.
What happened
- `FILTER (WHERE …)` is standard SQL for filtering values before aggregation.
- Native support includes PostgreSQL, H2, HSQLDB, SQLite, Firebird, CockroachDB, and YugabyteDB.
- Five runs of 500 iterations each showed `FILTER` consistently faster than `CASE`.
- The reported gap was around 8% on the test machine.
- JOOQ transparently rewrites `FILTER` into `CASE` when targeting dialects without native support.
What the benchmark actually measured
The setup is straightforward and worth copying. Each variant sums film lengths per actor across the join of `film_actor` and `film`, with one expression bucketing on `rating = 'R'` and another on `rating = 'PG'`. The only difference between the two queries is the syntax used to express that bucket. The benchmark looped each query 500 times in five separate runs.
The published numbers tell a tidy story. Across all five runs, the `FILTER` form completed in the high‑0.77‑second range, while the `CASE` form consistently landed in the mid‑0.83‑second range. The author pegs the gap at roughly 8% on the test machine, with the caveat that absolute numbers will shift with hardware and dataset size. The direction is what matters: one syntax is measurably cheaper than the other here.
A second pass added a redundant `rating IN ('R', 'PG')` predicate on the join to test whether the optimiser could lift the filter. It shaved time off both forms, and the gap between them stayed similar. In other words, `FILTER` did not just win because it gave the planner more information; it won because the executor was less work.
Why this matters
For reporting queries that run nightly, 8% is a rounding error. For dashboards that aggregate millions of rows on every page load, 8% is real money in compute and user‑perceived latency. The bigger lesson is that the `FILTER` clause is not just shorter to read; it is closer to how the optimiser thinks about the operation.
For our readers, the practical takeaway is: if you are on PostgreSQL, H2, HSQLDB, SQLite, Firebird, CockroachDB, or YugabyteDB, write `FILTER` first. It is more readable, more declarative, and now provably a touch faster. If you are on Oracle, MySQL, or SQL Server, where `FILTER` is not standard, you can keep the `CASE` form, or use jOOQ as a translation layer so your source code stays portable.
Say you are building a sales dashboard
Imagine a dashboard that summarises orders by region and product line, all from a single base table. You write `SUM(amount) FILTER (WHERE region = 'EU')` and the equivalent for `NA`, `APAC`, and `LATAM` in the same query, grouped by product line. One scan of the base table, four filtered aggregates, no repeated `CASE` boilerplate.
On PostgreSQL, you ship that and watch the EXPLAIN plan. The optimiser treats each `FILTER` as a separate aggregate node, which is what you want. On MySQL, the same query gets rewritten to `SUM(CASE WHEN region = 'EU' THEN amount END)` and the planner still does a reasonable job, just without the small speedup. Either way, the SQL reads cleanly.
What to watch next
Bottom line: prefer `FILTER` where it is native, and benchmark your own workload before assuming the gap transfers. Real‑world tables are messier than Sakila, and a handful of milliseconds saved on a toy dataset is not a guaranteed win in production. Watch for PostgreSQL release notes; optimiser improvements land in every major version, and today's edge may grow or shrink in the next minor release.
Key takeaways
- `FILTER` is faster than `CASE` for filtered aggregates on PostgreSQL 15, by about 8% in the published benchmark.
- Use `FILTER` natively on supported dialects; use `CASE` (or a translation layer) on Oracle, MySQL, and SQL Server.
- Always benchmark against your own data, not just sample databases.
- A redundant join predicate can speed up both forms; placement matters.
Frequently asked questions
Does `FILTER` change query results compared to `CASE`?
No. The jOOQ article confirms they are semantically identical, and a database that lacks `FILTER` can transparently rewrite one to the other.
Why does `FILTER` run faster on PostgreSQL?
The likely reason is a leaner executor path for the native form. The author notes the optimiser has less bookkeeping to do, which lines up with the consistent timing.
Should I migrate all my old `CASE` aggregations?
Only if you control the codebase and can verify behaviour with tests. The performance gain is small; the readability gain is the bigger reason to migrate.
What about MySQL?
MySQL does not support `FILTER` natively, so the `CASE` form is your only option there. The performance story for MySQL is a separate question worth its own benchmark.