Re: Proposal: QUALIFY clause - Mailing list pgsql-hackers

On Mon Jun 29, 2026 at 11:41 AM -03, Matheus Alcantara wrote:
> The big win of the rewrite approach is that the planner already
> optimizes window functions sitting inside a subquery, so QUALIFY gets
> those optimizations (window run conditions and pushing quals down below
> the WindowAgg) for free, with no new planner code. The plans come out
> identical to the equivalent hand-written subquery.
>
> The main downside is view deparse: because the stored query is the
> rewritten subquery, pg_get_viewdef() of a view defined with QUALIFY
> shows the subquery form rather than the QUALIFY keyword.
>
> An alternative:
>
> Another approach is to teach the planner about QUALIFY directly and
> apply the same window optimizations to its quals. I'm attaching a second
> patch (v2-0001-Add-QUALIFY-clause.patch.nocfbot) that does this, but
> only for the window-function case. Since QUALIFY is a general
> post-SELECT filter, a condition that doesn't reference a window function
> would, in this approach, need a gating Result node carrying the QUALIFY
> filter on top of the plan tree; that part is not implemented in this
> patch. This approach keeps QUALIFY first-class (clean view deparse, no
> synthetic subquery), at the cost of duplicating the run-condition and
> qual-pushdown logic that today lives at the subquery boundary.
>

When I wrote this I hadn't looked closely at ExecResult and I assumed a
Result node could carry a filter qual and just skip non-matching rows,
but that turns out not to be the case. ExecResult only checks
resconstantqual once, for the LIMIT 0-style case; it never loops
re-fetching from its outer plan until a per-row qual passes, the way
ExecScan does for every Scan node, or the way Agg/WindowAgg do by hand
for HAVING and window run conditions.

I looked at how DuckDB implements QUALIFY and it has a physical Filter
operator, also used for WHERE, and QUALIFY just puts one on top of the
window operator.

Postgres doesn't have an equivalent generic node. We filter rows
everywhere, but always as a capability built into a specific node type.
There's no node whose sole job is "filter an arbitrary already-computed
row stream," which is what QUALIFY needs for the case where its
condition doesn't reference a window function at all. So I'm thinking
about two options: teach ExecResult a real per-row filter loop, or
introduce a dedicated node for this purpose.

Either way, the cases where we'd actually need this node are narrower
than "QUALIFY without a window function." A QUALIFY condition that
doesn't reference a window function, in a query that has no window
functions at all, can be pushed into WHERE - mirroring the existing
optimization that moves a HAVING clause with no aggregates into WHERE.
And when window functions are present, a condition is still safe to push
below them as long as it only references columns that are in every
window clause's PARTITION BY list, the same rule that already handle
pushing quals below a WindowAgg. So the generic node is only strictly
needed when a QUALIFY condition references a window function's result in
a way that can't become a run condition, or references plain columns
that aren't common to every partition i.e., the cases that don't
reduce to either WHERE-pushdown or the WindowAgg qual. I'm just
wondering, though, whether either implementation option would be useful
beyond QUALIFY, I'm not sure it's worth building either just for this
one clause.

> So there are two patches attached:
>
> - v2-0001-Add-QUALIFY-clause.patch: the query-rewrite approach, complete (window and
>   non-window QUALIFY), with docs and tests.
>
> - v2-0001-Add-QUALIFY-clause.patch.nocfbot: the planner-based approach,
>   window-functions only. Note that there is some test cases failing due
>   to WIP state.
>

CFBot was not happy with v2 due to some compiler warnings. Attaching v3
with fixes, not major changes compared with v2.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com