Hi,
I'd like to propose a change to qual clause ordering in the executor.
Currently, order_qual_clauses() ranks predicates mainly by estimated
per-tuple cost. For conjunctive predicates under short-circuit
evaluation, this can be suboptimal: a slightly more expensive clause
that rejects many rows may be better to evaluate earlier, because it
can avoid many later evaluations.
The attached patch changes the ranking heuristic to use
cost / (1 - selectivity)
where selectivity is the fraction of rows that pass the clause.
For ANDed predicates evaluated left-to-right with short-circuit
semantics, this is a natural heuristic: (1 - selectivity) is the
probability that a clause rejects a row, so lower cost per rejected
row should generally be preferred.
The patch preserves the existing constraints:
- security_level ordering is still honored;
- leakproof-related behavior is unchanged;
- original clause order is preserved when ranks are equal.
It also adds a regression test covering:
1. reordering of equal-cost clauses by selectivity,
2. a case where better selectivity outweighs slightly higher cost,
3. a case where high cost still dominates despite better selectivity,
4. preservation of security_level ordering with row-level security,
5. stability when computed ranks are identical.
I also updated regression outputs whose EXPLAIN Filter or Join Filter
clause order changed due to the new ordering.
I also attached a small synthetic SQL benchmark script that I used to
illustrate one case where the new ordering reduces executor work for
user-defined operators. I'm including it only as an illustration, not
as a claim of universal improvement.
I searched the archives but could not find prior discussion of this
specific approach. Has incorporating selectivity into qual ordering
been considered before? If so, I would appreciate pointers to any
earlier discussion or reasons it was not pursued.
Patch attached.
Thanks,
Ilya Staroverov
