public inbox for [email protected]
help / color / mirror / Atom feed[RFC][PATCH] Order qual clauses by combined cost and selectivity
2+ messages / 2 participants
[nested] [flat]
* [RFC][PATCH] Order qual clauses by combined cost and selectivity
@ 2026-04-22 05:50 Staroverov Ilja <[email protected]>
2026-04-22 21:47 ` Re: [RFC][PATCH] Order qual clauses by combined cost and selectivity Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Staroverov Ilja @ 2026-04-22 05:50 UTC (permalink / raw)
To: [email protected] <[email protected]>
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
Attachments:
[application/octet-stream] 0001-Order-qual-clauses-by-combined-cost-and-selectivity.patch (49.6K, 2-0001-Order-qual-clauses-by-combined-cost-and-selectivity.patch)
download | inline diff:
From 542ec565d8f0449d5f853af58f34886166ebfb03 Mon Sep 17 00:00:00 2001
From: Staroverov Ilya <[email protected]>
Date: Wed, 22 Apr 2026 08:13:48 +0300
Subject: [PATCH] Order qual clauses by combined cost and selectivity
order_qual_clauses() currently ranks predicates by estimated per-tuple
cost alone. For conjunctive (AND) predicates evaluated with
short-circuit logic, this can be suboptimal: a slightly more expensive
clause that rejects most rows should be evaluated before a cheap clause
that passes most of them.
Rank clauses by cost / (1 - selectivity) instead, where selectivity is
the fraction of rows that satisfy the clause. This is a natural
heuristic that minimizes expected evaluation work when predicates are
independent and evaluated left-to-right.
Security-level constraints are still honored: clauses with a lower
security_level are never moved after clauses with a higher one, except
when the latter are leakproof. When computed ranks are equal, the
original clause order is preserved.
Add a dedicated regression test that exercises the new ordering:
equal-cost reordering by selectivity, cost-versus-selectivity trade-off,
preservation of security_level ordering, and stability for equal ranks.
Update existing expected outputs whose EXPLAIN Filter lines changed
due to the new ordering.
---
.../pg_plan_advice/expected/join_order.out | 2 +-
contrib/pg_plan_advice/expected/scan.out | 2 +-
.../postgres_fdw/expected/postgres_fdw.out | 6 +-
src/backend/optimizer/plan/createplan.c | 80 +++++--
src/test/regress/expected/inherit.out | 16 +-
src/test/regress/expected/join.out | 4 +-
src/test/regress/expected/partition_join.out | 48 ++--
src/test/regress/expected/partition_prune.out | 66 +++---
src/test/regress/expected/predicate_order.out | 222 ++++++++++++++++++
src/test/regress/expected/rowsecurity.out | 8 +-
src/test/regress/expected/select.out | 2 +-
src/test/regress/expected/select_distinct.out | 2 +-
src/test/regress/expected/updatable_views.out | 8 +-
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/predicate_order.sql | 193 +++++++++++++++
15 files changed, 563 insertions(+), 98 deletions(-)
create mode 100644 src/test/regress/expected/predicate_order.out
create mode 100644 src/test/regress/sql/predicate_order.sql
diff --git a/contrib/pg_plan_advice/expected/join_order.out b/contrib/pg_plan_advice/expected/join_order.out
index a5a9728e3fd..10af3ccf690 100644
--- a/contrib/pg_plan_advice/expected/join_order.out
+++ b/contrib/pg_plan_advice/expected/join_order.out
@@ -292,7 +292,7 @@ SELECT * FROM jo_fact f
--------------------------------------------------------------
Nested Loop
Disabled: true
- Join Filter: ((d1.id = f.dim1_id) AND (d2.id = f.dim2_id))
+ Join Filter: ((d2.id = f.dim2_id) AND (d1.id = f.dim1_id))
-> Nested Loop
-> Seq Scan on jo_dim1 d1
Filter: (val1 = 1)
diff --git a/contrib/pg_plan_advice/expected/scan.out b/contrib/pg_plan_advice/expected/scan.out
index f4036e4cbdd..62639a4f993 100644
--- a/contrib/pg_plan_advice/expected/scan.out
+++ b/contrib/pg_plan_advice/expected/scan.out
@@ -426,7 +426,7 @@ EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table
QUERY PLAN
-------------------------------------------------------------
Seq Scan on scan_table
- Filter: ((ctid > '(1,1)'::tid) AND (ctid < '(2,1)'::tid))
+ Filter: ((ctid < '(2,1)'::tid) AND (ctid > '(1,1)'::tid))
Supplied Plan Advice:
SEQ_SCAN(scan_table) /* matched */
Generated Plan Advice:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 10e87acabef..f96dba986b5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -380,7 +380,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 =
----------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c7 >= '1')) AND (("C 1" = 101)) AND ((c6 = '1'))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101)) AND ((c6 = '1')) AND ((c7 >= '1'))
(3 rows)
SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
@@ -2489,7 +2489,7 @@ SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT
Join Filter: (ft4.c1 = ft1.c1)
-> Foreign Scan on public.ft4
Output: ft4.c1, ft4.c2, ft4.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 10)) AND ((c1 <= 15))
+ Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 <= 15)) AND ((c1 >= 10))
-> Materialize
Output: ft1.c1, ft2.c1, (13)
-> Foreign Scan
@@ -3622,7 +3622,7 @@ select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6
Sort Key: ft2.c1 USING <^
-> Foreign Scan on public.ft2
Output: c1, c2
- Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6))
+ Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 = 6)) AND (("C 1" < 100))
(8 rows)
-- This should not be pushed either.
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index de6a183da79..5484fcfaa49 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -16,6 +16,8 @@
*/
#include "postgres.h"
+#include <float.h>
+
#include "access/sysattr.h"
#include "access/transam.h"
#include "catalog/pg_class.h"
@@ -5239,15 +5241,14 @@ get_switched_clauses(List *clauses, Relids outerrelids)
* different security levels in the list. Quals of lower security_level
* must go before quals of higher security_level, except that we can grant
* exceptions to move up quals that are leakproof. When security level
- * doesn't force the decision, we prefer to order clauses by estimated
- * execution cost, cheapest first.
+ * doesn't force the decision, we order clauses by a combined
+ * cost/selectivity rank so that cheap predicates that eliminate many rows
+ * are evaluated first.
*
- * Ideally the order should be driven by a combination of execution cost and
- * selectivity, but it's not immediately clear how to account for both,
- * and given the uncertainty of the estimates the reliability of the decisions
- * would be doubtful anyway. So we just order by security level then
- * estimated per-tuple cost, being careful not to change the order when
- * (as is often the case) the estimates are identical.
+ * For conjunctive predicates, we use the rank cost / (1 - selectivity),
+ * where selectivity is the fraction of rows that pass the clause.
+ * Clauses with a lower rank are checked first. We are careful not to
+ * change the order when the computed ranks are identical.
*
* Although this will work on either bare clauses or RestrictInfos, it's
* much faster to apply it to RestrictInfos, since it can re-use cost
@@ -5259,8 +5260,10 @@ get_switched_clauses(List *clauses, Relids outerrelids)
*
* Note: some callers pass lists that contain entries that will later be
* removed; this is the easiest way to let this routine see RestrictInfos
- * instead of bare clauses. This is another reason why trying to consider
- * selectivity in the ordering would likely do the wrong thing.
+ * instead of bare clauses. In such cases, selectivity estimates for
+ * clauses that are later removed can still affect the temporary ordering,
+ * but that is acceptable because this routine only determines executor
+ * qual evaluation order after planning decisions have already been made.
*/
static List *
order_qual_clauses(PlannerInfo *root, List *clauses)
@@ -5269,6 +5272,8 @@ order_qual_clauses(PlannerInfo *root, List *clauses)
{
Node *clause;
Cost cost;
+ Selectivity selectivity;
+ double rank;
Index security_level;
} QualItem;
int nitems = list_length(clauses);
@@ -5282,8 +5287,9 @@ order_qual_clauses(PlannerInfo *root, List *clauses)
return clauses;
/*
- * Collect the items and costs into an array. This is to avoid repeated
- * cost_qual_eval work if the inputs aren't RestrictInfos.
+ * Collect the items, costs, and selectivities into an array. This is
+ * to avoid repeated cost_qual_eval work if the inputs aren't
+ * RestrictInfos.
*/
items = (QualItem *) palloc(nitems * sizeof(QualItem));
i = 0;
@@ -5295,6 +5301,38 @@ order_qual_clauses(PlannerInfo *root, List *clauses)
cost_qual_eval_node(&qcost, clause, root);
items[i].clause = clause;
items[i].cost = qcost.per_tuple;
+
+ /*
+ * Compute selectivity for this clause. Selectivity is the
+ * fraction of rows that pass the predicate (lower = more
+ * selective = eliminates more rows).
+ */
+ items[i].selectivity = clause_selectivity(root,
+ clause,
+ 0,
+ JOIN_INNER,
+ NULL);
+
+ if (items[i].selectivity < 0.0)
+ items[i].selectivity = 0.0;
+ else if (items[i].selectivity > 1.0)
+ items[i].selectivity = 1.0;
+
+ /*
+ * For conjunctive predicates, rank is cost / (1 - selectivity),
+ * where selectivity is the fraction of rows that pass. Lower
+ * rank means that the clause is a better candidate to
+ * evaluate earlier.
+ *
+ * If selectivity is 1.0, the clause eliminates no rows, so
+ * assign the maximum rank and move it to the end within the
+ * same security level.
+ */
+ if (items[i].selectivity < 1.0)
+ items[i].rank = items[i].cost / (1.0 - items[i].selectivity);
+ else
+ items[i].rank = DBL_MAX;
+
if (IsA(clause, RestrictInfo))
{
RestrictInfo *rinfo = (RestrictInfo *) clause;
@@ -5334,10 +5372,22 @@ order_qual_clauses(PlannerInfo *root, List *clauses)
{
QualItem *olditem = &items[j - 1];
- if (newitem.security_level > olditem->security_level ||
- (newitem.security_level == olditem->security_level &&
- newitem.cost >= olditem->cost))
+ /*
+ * First, respect security levels: higher security_level
+ * must come after lower.
+ */
+ if (newitem.security_level > olditem->security_level)
break;
+ if (newitem.security_level < olditem->security_level)
+ {
+ items[j] = *olditem;
+ continue;
+ }
+
+ /* Same security level: order by cost-effectiveness rank. */
+ if (newitem.rank >= olditem->rank)
+ break;
+
items[j] = *olditem;
}
items[j] = newitem;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 3d8e8d8afd2..51641524c07 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -3197,11 +3197,11 @@ explain (costs off) select * from range_list_parted where a between 3 and 23 and
-----------------------------------------------------------------
Append
-> Seq Scan on part_1_10_ab range_list_parted_1
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+ Filter: ((b = 'ab'::bpchar) AND (a >= 3) AND (a <= 23))
-> Seq Scan on part_10_20_ab range_list_parted_2
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+ Filter: ((b = 'ab'::bpchar) AND (a >= 3) AND (a <= 23))
-> Seq Scan on part_21_30_ab range_list_parted_3
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+ Filter: ((b = 'ab'::bpchar) AND (a >= 3) AND (a <= 23))
(7 rows)
/* Should select no rows because range partition key cannot be null */
@@ -3345,7 +3345,7 @@ explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c >
QUERY PLAN
-----------------------------------------------------
Seq Scan on mcrparted4 mcrparted
- Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+ Filter: ((a = 20) AND (c > 10) AND (abs(b) = 10))
(2 rows)
explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
@@ -3353,13 +3353,13 @@ explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mc
---------------------------------------------
Append
-> Seq Scan on mcrparted3 mcrparted_1
- Filter: ((c > 20) AND (a = 20))
+ Filter: ((a = 20) AND (c > 20))
-> Seq Scan on mcrparted4 mcrparted_2
- Filter: ((c > 20) AND (a = 20))
+ Filter: ((a = 20) AND (c > 20))
-> Seq Scan on mcrparted5 mcrparted_3
- Filter: ((c > 20) AND (a = 20))
+ Filter: ((a = 20) AND (c > 20))
-> Seq Scan on mcrparted_def mcrparted_4
- Filter: ((c > 20) AND (a = 20))
+ Filter: ((a = 20) AND (c > 20))
(9 rows)
-- check that partitioned table Appends cope with being referenced in
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 78bf022f7b4..0db378be547 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2809,7 +2809,7 @@ select count(*) from
Aggregate
-> Merge Left Join
Merge Cond: (x.thousand = y.unique2)
- Join Filter: ((x.twothousand = y.hundred) AND (x.fivethous = y.unique2))
+ Join Filter: ((x.fivethous = y.unique2) AND (x.twothousand = y.hundred))
-> Sort
Sort Key: x.thousand, x.twothousand, x.fivethous
-> Seq Scan on tenk1 x
@@ -9787,7 +9787,7 @@ inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2;
Nested Loop
Output: j1.id1, j1.id2, j2.id1, j2.id2
Inner Unique: true
- Join Filter: ((j1.id1 = j2.id1) AND (j1.id2 = j2.id2))
+ Join Filter: ((j1.id2 = j2.id2) AND (j1.id1 = j2.id1))
-> Seq Scan on public.j2
Output: j2.id1, j2.id2
-> Seq Scan on public.j1
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 38643d41fd7..f8072f99e5d 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -285,7 +285,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a <
Filter: (b > 250)
-> Hash
-> Seq Scan on prt1_p2 t1
- Filter: ((a < 450) AND (b = 0))
+ Filter: ((b = 0) AND (a < 450))
(9 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -311,9 +311,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
-> Hash
-> Append
-> Seq Scan on prt1_p1 prt1_1
- Filter: ((a < 450) AND (b = 0))
+ Filter: ((b = 0) AND (a < 450))
-> Seq Scan on prt1_p2 prt1_2
- Filter: ((a < 450) AND (b = 0))
+ Filter: ((b = 0) AND (a < 450))
(15 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
@@ -1400,9 +1400,9 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
Sort Key: prt1.a
-> Append
-> Seq Scan on prt1_p1 prt1_1
- Filter: ((a < 450) AND (b = 0))
+ Filter: ((b = 0) AND (a < 450))
-> Seq Scan on prt1_p2 prt1_2
- Filter: ((a < 450) AND (b = 0))
+ Filter: ((b = 0) AND (a < 450))
-> Sort
Sort Key: prt2.b
-> Append
@@ -2536,7 +2536,7 @@ where not exists (select 1 from prtx2
Append
-> Nested Loop Anti Join
-> Seq Scan on prtx1_1
- Filter: ((a < 20) AND (c = 120))
+ Filter: ((c = 120) AND (a < 20))
-> Bitmap Heap Scan on prtx2_1
Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
Filter: (a = prtx1_1.a)
@@ -2547,7 +2547,7 @@ where not exists (select 1 from prtx2
Index Cond: (c = 123)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
- Filter: ((a < 20) AND (c = 120))
+ Filter: ((c = 120) AND (a < 20))
-> Bitmap Heap Scan on prtx2_2
Recheck Cond: ((b = prtx1_2.b) AND (c = 123))
Filter: (a = prtx1_2.a)
@@ -2577,7 +2577,7 @@ where not exists (select 1 from prtx2
Append
-> Nested Loop Anti Join
-> Seq Scan on prtx1_1
- Filter: ((a < 20) AND (c = 91))
+ Filter: ((c = 91) AND (a < 20))
-> Bitmap Heap Scan on prtx2_1
Recheck Cond: ((b = (prtx1_1.b + 1)) OR (c = 99))
Filter: (a = prtx1_1.a)
@@ -2588,7 +2588,7 @@ where not exists (select 1 from prtx2
Index Cond: (c = 99)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
- Filter: ((a < 20) AND (c = 91))
+ Filter: ((c = 91) AND (a < 20))
-> Bitmap Heap Scan on prtx2_2
Recheck Cond: ((b = (prtx1_2.b + 1)) OR (c = 99))
Filter: (a = prtx1_2.a)
@@ -3451,13 +3451,13 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a =
-> Seq Scan on prt2_adv_p1 t2_1
-> Hash
-> Seq Scan on prt1_adv_p1 t1_1
- Filter: ((a < 300) AND (b = 0))
+ Filter: ((b = 0) AND (a < 300))
-> Hash Join
Hash Cond: (t2_2.b = t1_2.a)
-> Seq Scan on prt2_adv_p2 t2_2
-> Hash
-> Seq Scan on prt1_adv_p2 t1_2
- Filter: ((a < 300) AND (b = 0))
+ Filter: ((b = 0) AND (a < 300))
(15 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -3490,13 +3490,13 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a =
-> Seq Scan on prt2_adv_p1 t2_1
-> Hash
-> Seq Scan on prt1_adv_p1 t1_1
- Filter: ((a >= 100) AND (a < 300) AND (b = 0))
+ Filter: ((b = 0) AND (a >= 100) AND (a < 300))
-> Hash Join
Hash Cond: (t2_2.b = t1_2.a)
-> Seq Scan on prt2_adv_p2 t2_2
-> Hash
-> Seq Scan on prt1_adv_p2 t1_2
- Filter: ((a >= 100) AND (a < 300) AND (b = 0))
+ Filter: ((b = 0) AND (a >= 100) AND (a < 300))
(15 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -4447,7 +4447,7 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-> Seq Scan on plt1_adv_p3 t1_3
Filter: (b < 10)
-> Nested Loop Left Join
- Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
+ Join Filter: ((t1_4.c = t2_4.c) AND (t1_4.a = t2_4.a))
-> Seq Scan on plt1_adv_extra t1_4
Filter: (b < 10)
-> Seq Scan on plt2_adv_extra t2_4
@@ -4553,9 +4553,9 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2
-> Seq Scan on plt1_adv_p3 t1_3
Filter: (b < 10)
-> Nested Loop Left Join
- Join Filter: ((t1_4.a = t3_4.a) AND (t1_4.c = t3_4.c))
+ Join Filter: ((t1_4.c = t3_4.c) AND (t1_4.a = t3_4.a))
-> Nested Loop Left Join
- Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
+ Join Filter: ((t1_4.c = t2_4.c) AND (t1_4.a = t2_4.a))
-> Seq Scan on plt1_adv_extra t1_4
Filter: (b < 10)
-> Seq Scan on plt2_adv_extra t2_4
@@ -5022,7 +5022,7 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
-> Seq Scan on beta_neg_p2 t2_2
-> Hash
-> Seq Scan on alpha_neg_p2 t1_2
- Filter: ((b >= 125) AND (b < 225))
+ Filter: ((b < 225) AND (b >= 125))
-> Hash Join
Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
-> Append
@@ -5032,11 +5032,11 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
-> Hash
-> Append
-> Seq Scan on alpha_pos_p1 t1_4
- Filter: ((b >= 125) AND (b < 225))
+ Filter: ((b < 225) AND (b >= 125))
-> Seq Scan on alpha_pos_p2 t1_5
- Filter: ((b >= 125) AND (b < 225))
+ Filter: ((b < 225) AND (b >= 125))
-> Seq Scan on alpha_pos_p3 t1_6
- Filter: ((b >= 125) AND (b < 225))
+ Filter: ((b < 225) AND (b >= 125))
(29 rows)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b) WHERE t1.b >= 125 AND t1.b < 225 ORDER BY t1.a, t1.b;
@@ -5105,13 +5105,13 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.c = t2
-> Seq Scan on beta_neg_p2 t2_3
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Nested Loop
- Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
+ Join Filter: ((t1_4.c = t2_4.c) AND (t1_4.a = t2_4.a))
-> Seq Scan on alpha_pos_p2 t1_4
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
-> Seq Scan on beta_pos_p2 t2_4
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Nested Loop
- Join Filter: ((t1_5.a = t2_5.a) AND (t1_5.c = t2_5.c))
+ Join Filter: ((t1_5.c = t2_5.c) AND (t1_5.a = t2_5.a))
-> Seq Scan on alpha_pos_p3 t1_5
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
-> Seq Scan on beta_pos_p3 t2_5
@@ -5161,13 +5161,13 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
-> Seq Scan on beta_neg_p2 t2_2
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Nested Loop
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
+ Join Filter: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c) AND (t1_3.a = t2_3.a))
-> Seq Scan on alpha_pos_p2 t1_3
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
-> Seq Scan on beta_pos_p2 t2_3
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Nested Loop
- Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.b = t2_4.b) AND (t1_4.c = t2_4.c))
+ Join Filter: ((t1_4.b = t2_4.b) AND (t1_4.c = t2_4.c) AND (t1_4.a = t2_4.a))
-> Seq Scan on alpha_pos_p3 t1_4
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
-> Seq Scan on beta_pos_p3 t2_4
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 849049f9c51..a85f69f601d 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -364,21 +364,21 @@ explain (costs off) select * from rlp where a > 15 and b = 'ab';
---------------------------------------------------------
Append
-> Seq Scan on rlp3abcd rlp_1
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
-> Seq Scan on rlp4_1 rlp_2
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
-> Seq Scan on rlp4_2 rlp_3
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
-> Seq Scan on rlp4_default rlp_4
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
-> Seq Scan on rlp5_1 rlp_5
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
-> Seq Scan on rlp5_default rlp_6
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
-> Seq Scan on rlp_default_30 rlp_7
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
-> Seq Scan on rlp_default_default rlp_8
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: (((b)::text = 'ab'::text) AND (a > 15))
(17 rows)
explain (costs off) select * from rlp where a = 16;
@@ -406,7 +406,7 @@ explain (costs off) select * from rlp where a = 16 and b < 'ab';
QUERY PLAN
---------------------------------------------------
Seq Scan on rlp3_default rlp
- Filter: (((b)::text < 'ab'::text) AND (a = 16))
+ Filter: ((a = 16) AND ((b)::text < 'ab'::text))
(2 rows)
explain (costs off) select * from rlp where a = 16 and b <= 'ab';
@@ -414,9 +414,9 @@ explain (costs off) select * from rlp where a = 16 and b <= 'ab';
----------------------------------------------------------
Append
-> Seq Scan on rlp3abcd rlp_1
- Filter: (((b)::text <= 'ab'::text) AND (a = 16))
+ Filter: ((a = 16) AND ((b)::text <= 'ab'::text))
-> Seq Scan on rlp3_default rlp_2
- Filter: (((b)::text <= 'ab'::text) AND (a = 16))
+ Filter: ((a = 16) AND ((b)::text <= 'ab'::text))
(5 rows)
explain (costs off) select * from rlp where a = 16 and b is null;
@@ -636,7 +636,7 @@ explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
QUERY PLAN
----------------------------------
Seq Scan on rlp_default_10 rlp
- Filter: ((a > 1) AND (a = 10))
+ Filter: ((a = 10) AND (a > 1))
(2 rows)
explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
@@ -741,9 +741,9 @@ explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8;
--------------------------------------------------------
Append
-> Seq Scan on mc3p0 mc3p_1
- Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
+ Filter: ((a = 1) AND (c < 8) AND (abs(b) = 1))
-> Seq Scan on mc3p1 mc3p_2
- Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
+ Filter: ((a = 1) AND (c < 8) AND (abs(b) = 1))
(5 rows)
explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
@@ -991,7 +991,7 @@ explain (costs off) select * from mc2p where a = 2 and b < 1;
QUERY PLAN
---------------------------------
Seq Scan on mc2p3 mc2p
- Filter: ((b < 1) AND (a = 2))
+ Filter: ((a = 2) AND (b < 1))
(2 rows)
explain (costs off) select * from mc2p where a > 1;
@@ -1014,7 +1014,7 @@ explain (costs off) select * from mc2p where a = 1 and b > 1;
QUERY PLAN
---------------------------------
Seq Scan on mc2p2 mc2p
- Filter: ((b > 1) AND (a = 1))
+ Filter: ((a = 1) AND (b > 1))
(2 rows)
-- all partitions but the default one should be pruned
@@ -1809,9 +1809,9 @@ explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' a
------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on rlp3efgh rlp_1
- Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
+ Filter: ((b IS NOT NULL) AND (a = 15) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text))
-> Seq Scan on rlp3_default rlp_2
- Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
+ Filter: ((b IS NOT NULL) AND (a = 15) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text))
(5 rows)
--
@@ -2026,13 +2026,13 @@ explain (costs off) select * from hp where a < 1 and b = 'xxx';
-------------------------------------------------
Append
-> Seq Scan on hp0 hp_1
- Filter: ((a < 1) AND (b = 'xxx'::text))
+ Filter: ((b = 'xxx'::text) AND (a < 1))
-> Seq Scan on hp1 hp_2
- Filter: ((a < 1) AND (b = 'xxx'::text))
+ Filter: ((b = 'xxx'::text) AND (a < 1))
-> Seq Scan on hp2 hp_3
- Filter: ((a < 1) AND (b = 'xxx'::text))
+ Filter: ((b = 'xxx'::text) AND (a < 1))
-> Seq Scan on hp3 hp_4
- Filter: ((a < 1) AND (b = 'xxx'::text))
+ Filter: ((b = 'xxx'::text) AND (a < 1))
(9 rows)
explain (costs off) select * from hp where a <> 1 and b = 'yyy';
@@ -2040,13 +2040,13 @@ explain (costs off) select * from hp where a <> 1 and b = 'yyy';
--------------------------------------------------
Append
-> Seq Scan on hp0 hp_1
- Filter: ((a <> 1) AND (b = 'yyy'::text))
+ Filter: ((b = 'yyy'::text) AND (a <> 1))
-> Seq Scan on hp1 hp_2
- Filter: ((a <> 1) AND (b = 'yyy'::text))
+ Filter: ((b = 'yyy'::text) AND (a <> 1))
-> Seq Scan on hp2 hp_3
- Filter: ((a <> 1) AND (b = 'yyy'::text))
+ Filter: ((b = 'yyy'::text) AND (a <> 1))
-> Seq Scan on hp3 hp_4
- Filter: ((a <> 1) AND (b = 'yyy'::text))
+ Filter: ((b = 'yyy'::text) AND (a <> 1))
(9 rows)
explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
@@ -4113,7 +4113,7 @@ select * from listp where a = (select 2) and b <> 10;
QUERY PLAN
--------------------------------------------------------
Seq Scan on listp1 listp (actual rows=0.00 loops=1)
- Filter: ((b <> 10) AND (a = (InitPlan expr_1).col1))
+ Filter: ((a = (InitPlan expr_1).col1) AND (b <> 10))
InitPlan expr_1
-> Result (never executed)
(4 rows)
@@ -4275,7 +4275,7 @@ explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a';
QUERY PLAN
--------------------------------------------------
Seq Scan on rp_prefix_test1_p1 rp_prefix_test1
- Filter: ((a <= 1) AND ((b)::text = 'a'::text))
+ Filter: (((b)::text = 'a'::text) AND (a <= 1))
(2 rows)
create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c);
@@ -4287,7 +4287,7 @@ explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >
QUERY PLAN
------------------------------------------------
Seq Scan on rp_prefix_test2_p1 rp_prefix_test2
- Filter: ((a <= 1) AND (c >= 0) AND (b = 1))
+ Filter: ((b = 1) AND (a <= 1) AND (c >= 0))
(2 rows)
create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d);
@@ -4309,7 +4309,7 @@ explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
- Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2))
+ Filter: ((b = 2) AND (c = 2) AND (a >= 1) AND (b >= 1) AND (d >= 0))
(2 rows)
drop table rp_prefix_test1;
@@ -4586,7 +4586,7 @@ explain (verbose, costs off) execute update_part_abc_view (1, 'd');
Subplans Removed: 1
-> Seq Scan on public.part_abc_1
Output: $2, part_abc_1.tableoid, part_abc_1.ctid
- Filter: ((part_abc_1.b <> 'a'::text) AND (part_abc_1.a = $1))
+ Filter: ((part_abc_1.a = $1) AND (part_abc_1.b <> 'a'::text))
(8 rows)
execute update_part_abc_view (1, 'd');
@@ -4605,7 +4605,7 @@ explain (verbose, costs off) execute update_part_abc_view (2, 'a');
Subplans Removed: 1
-> Seq Scan on public.part_abc_2
Output: $2, part_abc_2.tableoid, part_abc_2.ctid
- Filter: ((part_abc_2.b <> 'a'::text) AND (part_abc_2.a = $1))
+ Filter: ((part_abc_2.a = $1) AND (part_abc_2.b <> 'a'::text))
(8 rows)
execute update_part_abc_view (2, 'a');
@@ -4641,7 +4641,7 @@ when matched then delete returning pt.a;
-> Append
Subplans Removed: 1
-> Seq Scan on part_abc_1
- Filter: ((b <> 'a'::text) AND (a = stable_one()))
+ Filter: ((a = stable_one()) AND (b <> 'a'::text))
-> Materialize
-> Seq Scan on part_abc_1 pt1
Filter: (a = stable_one())
diff --git a/src/test/regress/expected/predicate_order.out b/src/test/regress/expected/predicate_order.out
new file mode 100644
index 00000000000..3b97f005137
--- /dev/null
+++ b/src/test/regress/expected/predicate_order.out
@@ -0,0 +1,222 @@
+--
+-- Predicate ordering by selectivity
+--
+-- Test 1. Check reordering of equal-cost clauses by selectivity.
+-- Both clauses use the built-in "=" operator and have the
+-- same per-tuple cost. The clause on column "a" is more
+-- selective and should appear first in the Filter.
+--
+CREATE TABLE pred_order (
+ a int,
+ b int
+);
+INSERT INTO pred_order
+SELECT i, i % 2
+FROM generate_series(1, 1000) AS g(i);
+ANALYZE pred_order;
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE b = 0 AND a = 2;
+ QUERY PLAN
+---------------------------------
+ Seq Scan on pred_order
+ Filter: ((a = 2) AND (b = 0))
+(2 rows)
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE b = 0 AND a = 2;
+ count
+-------
+ 1
+(1 row)
+
+--
+-- Test setup. Use a SET clause to prevent SQL-function inlining.
+-- This keeps the operator node and its declared cost
+-- visible to the planner.
+--
+CREATE FUNCTION pred_order_eq_slow(int, int)
+RETURNS boolean
+LANGUAGE plpgsql
+IMMUTABLE
+PARALLEL SAFE
+COST 1.5
+AS $$
+BEGIN
+ RETURN $1 = $2;
+END;
+$$;
+CREATE OPERATOR #=# (
+ LEFTARG = int4,
+ RIGHTARG = int4,
+ PROCEDURE = pred_order_eq_slow,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel
+);
+--
+-- Test 2. Check that a more selective clause can move before a
+-- cheaper clause when its cost/selectivity rank is better.
+-- Expected result: the predicate on column a is evaluated
+-- before the predicate on column b. The old cost-only
+-- ordering would keep the cheaper predicate first.
+--
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE b = 0 AND a #=# 2;
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on pred_order
+ Filter: ((a #=# 2) AND (b = 0))
+(2 rows)
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE b = 0 AND a #=# 2;
+ count
+-------
+ 1
+(1 row)
+
+-- Test setup.
+CREATE FUNCTION pred_order_eq_very_slow(int, int)
+RETURNS boolean
+LANGUAGE plpgsql
+IMMUTABLE
+PARALLEL SAFE
+COST 10
+AS $$
+BEGIN
+ RETURN $1 = $2;
+END;
+$$;
+CREATE OPERATOR #==# (
+ LEFTARG = int4,
+ RIGHTARG = int4,
+ PROCEDURE = pred_order_eq_very_slow,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel
+);
+--
+-- Test 3. Check that a much more expensive clause stays after a
+-- cheaper clause when selectivity does not compensate for
+-- the extra cost.
+-- Expected result: the predicate on column b is evaluated
+-- before the predicate on column a.
+--
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE b = 0 AND a #==# 2;
+ QUERY PLAN
+------------------------------------
+ Seq Scan on pred_order
+ Filter: ((b = 0) AND (a #==# 2))
+(2 rows)
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE b = 0 AND a #==# 2;
+ count
+-------
+ 1
+(1 row)
+
+--
+-- Test 4. Check that row-level security predicates are never
+-- reordered past user-supplied predicates even when
+-- the user predicate has a better rank.
+-- Expected result: the RLS predicate (b = 0) is evaluated
+-- before the user predicate (a #=# 2) because the latter
+-- is not leakproof and has a higher security_level.
+--
+ALTER TABLE pred_order ENABLE ROW LEVEL SECURITY;
+ALTER TABLE pred_order FORCE ROW LEVEL SECURITY;
+CREATE POLICY pred_order_policy ON pred_order
+ USING (b = 0);
+CREATE ROLE pred_order_rls_user;
+GRANT SELECT ON pred_order TO pred_order_rls_user;
+SET ROLE pred_order_rls_user;
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE a #=# 2;
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on pred_order
+ Filter: ((b = 0) AND (a #=# 2))
+(2 rows)
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE a #=# 2;
+ count
+-------
+ 1
+(1 row)
+
+RESET ROLE;
+DROP POLICY pred_order_policy ON pred_order;
+REVOKE ALL ON pred_order FROM pred_order_rls_user;
+DROP ROLE pred_order_rls_user;
+ALTER TABLE pred_order DISABLE ROW LEVEL SECURITY;
+ALTER TABLE pred_order NO FORCE ROW LEVEL SECURITY;
+--
+-- Test 5. Check that clauses with identical ranks preserve their
+-- original order.
+-- Expected result: both columns have the same selectivity
+-- and cost, so the filter keeps the original clause order
+-- (c = 5) AND (d = 5).
+--
+CREATE TABLE pred_order_eq (
+ c int,
+ d int
+);
+INSERT INTO pred_order_eq
+SELECT i % 10, i % 10
+FROM generate_series(1, 1000) AS g(i);
+ANALYZE pred_order_eq;
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order_eq
+WHERE c = 5 AND d = 5;
+ QUERY PLAN
+---------------------------------
+ Seq Scan on pred_order_eq
+ Filter: ((c = 5) AND (d = 5))
+(2 rows)
+
+SELECT COUNT(*)
+FROM pred_order_eq
+WHERE c = 5 AND d = 5;
+ count
+-------
+ 100
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order_eq
+WHERE d = 5 AND c = 5;
+ QUERY PLAN
+---------------------------------
+ Seq Scan on pred_order_eq
+ Filter: ((d = 5) AND (c = 5))
+(2 rows)
+
+SELECT COUNT(*)
+FROM pred_order_eq
+WHERE d = 5 AND c = 5;
+ count
+-------
+ 100
+(1 row)
+
+DROP TABLE pred_order_eq;
+DROP OPERATOR #==# (int4, int4);
+DROP FUNCTION pred_order_eq_very_slow(int, int);
+DROP OPERATOR #=# (int4, int4);
+DROP FUNCTION pred_order_eq_slow(int, int);
+DROP TABLE pred_order;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..bf63d4bfd69 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -636,7 +636,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on document
- Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle))
+ Filter: ((cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND (cid <> 44) AND (cid <> 44) AND f_leak(dtitle))
InitPlan expr_1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
@@ -653,7 +653,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
-> Seq Scan on category
-> Hash
-> Seq Scan on document
- Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle))
+ Filter: ((cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND (cid <> 44) AND (cid <> 44) AND f_leak(dtitle))
(9 rows)
-- 44 would technically fail for both p2r and p1r, but we should get an error
@@ -2264,7 +2264,7 @@ EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
-----------------------------------------------------------------------
Update on b1
-> Seq Scan on b1
- Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0) AND f_leak(b))
+ Filter: ((a = 4) AND (a > 0) AND ((a % 2) = 0) AND f_leak(b))
(3 rows)
UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
@@ -2274,7 +2274,7 @@ EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
-----------------------------------------------------------------------
Delete on b1
-> Seq Scan on b1
- Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0) AND f_leak(b))
+ Filter: ((a = 6) AND (a > 0) AND ((a % 2) = 0) AND f_leak(b))
(3 rows)
DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index 34f040beecc..0cdd1a37b26 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -834,7 +834,7 @@ select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
QUERY PLAN
-------------------------------------------------------
Seq Scan on onek2
- Filter: ((stringu1 < 'C'::name) AND (unique2 = 11))
+ Filter: ((unique2 = 11) AND (stringu1 < 'C'::name))
(2 rows)
select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out
index 379ba0bc9fa..2744da6a139 100644
--- a/src/test/regress/expected/select_distinct.out
+++ b/src/test/regress/expected/select_distinct.out
@@ -322,7 +322,7 @@ SELECT DISTINCT four FROM tenk1 WHERE four = 0 AND two <> 0;
---------------------------------------------
Limit
-> Seq Scan on tenk1
- Filter: ((two <> 0) AND (four = 0))
+ Filter: ((four = 0) AND (two <> 0))
(3 rows)
-- Ensure no rows are returned
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 8852160718f..24ed0dffb85 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -964,7 +964,7 @@ EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (a = 2)
-> Subquery Scan on rw_view1
- Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
+ Filter: ((rw_view1.a = 2) AND (rw_view1.a < 10))
-> Bitmap Heap Scan on base_tbl base_tbl_1
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
@@ -979,7 +979,7 @@ EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (a = 2)
-> Subquery Scan on rw_view1
- Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
+ Filter: ((rw_view1.a = 2) AND (rw_view1.a < 10))
-> Bitmap Heap Scan on base_tbl base_tbl_1
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
@@ -1255,7 +1255,7 @@ EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
----------------------------------------------------------
Update on rw_view1 rw_view1_1
-> Subquery Scan on rw_view1
- Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
+ Filter: ((rw_view1.a = 2) AND (rw_view1.a < 10))
-> Bitmap Heap Scan on base_tbl
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
@@ -1267,7 +1267,7 @@ EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
----------------------------------------------------------
Delete on rw_view1 rw_view1_1
-> Subquery Scan on rw_view1
- Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
+ Filter: ((rw_view1.a = 2) AND (rw_view1.a < 10))
-> Bitmap Heap Scan on base_tbl
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 5d4f910155e..9c847809730 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -123,7 +123,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
# The stats test resets stats, so nothing else needing stats access can be in
# this group.
# ----------
-test: partition_merge partition_split partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain memoize stats predicate numa eager_aggregate graph_table_rls planner_est
+test: partition_merge partition_split partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain memoize stats predicate predicate_order numa eager_aggregate graph_table_rls planner_est
# ----------
# Another group of parallel tests (compression)
diff --git a/src/test/regress/sql/predicate_order.sql b/src/test/regress/sql/predicate_order.sql
new file mode 100644
index 00000000000..556a86227b7
--- /dev/null
+++ b/src/test/regress/sql/predicate_order.sql
@@ -0,0 +1,193 @@
+--
+-- Predicate ordering by selectivity
+--
+-- Test 1. Check reordering of equal-cost clauses by selectivity.
+-- Both clauses use the built-in "=" operator and have the
+-- same per-tuple cost. The clause on column "a" is more
+-- selective and should appear first in the Filter.
+--
+
+CREATE TABLE pred_order (
+ a int,
+ b int
+);
+
+INSERT INTO pred_order
+SELECT i, i % 2
+FROM generate_series(1, 1000) AS g(i);
+
+ANALYZE pred_order;
+
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE b = 0 AND a = 2;
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE b = 0 AND a = 2;
+
+--
+-- Test setup. Use a SET clause to prevent SQL-function inlining.
+-- This keeps the operator node and its declared cost
+-- visible to the planner.
+--
+
+CREATE FUNCTION pred_order_eq_slow(int, int)
+RETURNS boolean
+LANGUAGE plpgsql
+IMMUTABLE
+PARALLEL SAFE
+COST 1.5
+AS $$
+BEGIN
+ RETURN $1 = $2;
+END;
+$$;
+
+CREATE OPERATOR #=# (
+ LEFTARG = int4,
+ RIGHTARG = int4,
+ PROCEDURE = pred_order_eq_slow,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel
+);
+
+--
+-- Test 2. Check that a more selective clause can move before a
+-- cheaper clause when its cost/selectivity rank is better.
+-- Expected result: the predicate on column a is evaluated
+-- before the predicate on column b. The old cost-only
+-- ordering would keep the cheaper predicate first.
+--
+
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE b = 0 AND a #=# 2;
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE b = 0 AND a #=# 2;
+
+-- Test setup.
+
+CREATE FUNCTION pred_order_eq_very_slow(int, int)
+RETURNS boolean
+LANGUAGE plpgsql
+IMMUTABLE
+PARALLEL SAFE
+COST 10
+AS $$
+BEGIN
+ RETURN $1 = $2;
+END;
+$$;
+
+CREATE OPERATOR #==# (
+ LEFTARG = int4,
+ RIGHTARG = int4,
+ PROCEDURE = pred_order_eq_very_slow,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel
+);
+
+--
+-- Test 3. Check that a much more expensive clause stays after a
+-- cheaper clause when selectivity does not compensate for
+-- the extra cost.
+-- Expected result: the predicate on column b is evaluated
+-- before the predicate on column a.
+--
+
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE b = 0 AND a #==# 2;
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE b = 0 AND a #==# 2;
+
+--
+-- Test 4. Check that row-level security predicates are never
+-- reordered past user-supplied predicates even when
+-- the user predicate has a better rank.
+-- Expected result: the RLS predicate (b = 0) is evaluated
+-- before the user predicate (a #=# 2) because the latter
+-- is not leakproof and has a higher security_level.
+--
+
+ALTER TABLE pred_order ENABLE ROW LEVEL SECURITY;
+ALTER TABLE pred_order FORCE ROW LEVEL SECURITY;
+
+CREATE POLICY pred_order_policy ON pred_order
+ USING (b = 0);
+
+CREATE ROLE pred_order_rls_user;
+GRANT SELECT ON pred_order TO pred_order_rls_user;
+
+SET ROLE pred_order_rls_user;
+
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order
+WHERE a #=# 2;
+
+SELECT COUNT(*)
+FROM pred_order
+WHERE a #=# 2;
+
+RESET ROLE;
+
+DROP POLICY pred_order_policy ON pred_order;
+REVOKE ALL ON pred_order FROM pred_order_rls_user;
+DROP ROLE pred_order_rls_user;
+
+ALTER TABLE pred_order DISABLE ROW LEVEL SECURITY;
+ALTER TABLE pred_order NO FORCE ROW LEVEL SECURITY;
+
+--
+-- Test 5. Check that clauses with identical ranks preserve their
+-- original order.
+-- Expected result: both columns have the same selectivity
+-- and cost, so the filter keeps the original clause order
+-- (c = 5) AND (d = 5).
+--
+
+CREATE TABLE pred_order_eq (
+ c int,
+ d int
+);
+
+INSERT INTO pred_order_eq
+SELECT i % 10, i % 10
+FROM generate_series(1, 1000) AS g(i);
+
+ANALYZE pred_order_eq;
+
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order_eq
+WHERE c = 5 AND d = 5;
+
+SELECT COUNT(*)
+FROM pred_order_eq
+WHERE c = 5 AND d = 5;
+
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM pred_order_eq
+WHERE d = 5 AND c = 5;
+
+SELECT COUNT(*)
+FROM pred_order_eq
+WHERE d = 5 AND c = 5;
+
+DROP TABLE pred_order_eq;
+
+DROP OPERATOR #==# (int4, int4);
+DROP FUNCTION pred_order_eq_very_slow(int, int);
+DROP OPERATOR #=# (int4, int4);
+DROP FUNCTION pred_order_eq_slow(int, int);
+DROP TABLE pred_order;
--
2.43.0
[application/octet-stream] predicate_order_bench.sql (2.6K, 3-predicate_order_bench.sql)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [RFC][PATCH] Order qual clauses by combined cost and selectivity
2026-04-22 05:50 [RFC][PATCH] Order qual clauses by combined cost and selectivity Staroverov Ilja <[email protected]>
@ 2026-04-22 21:47 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2026-04-22 21:47 UTC (permalink / raw)
To: Staroverov Ilja <[email protected]>; +Cc: [email protected] <[email protected]>
Staroverov Ilja <[email protected]> writes:
> The attached patch changes the ranking heuristic to use
> cost / (1 - selectivity)
> where selectivity is the fraction of rows that pass the clause.
This (or some close relative) has been proposed before, but we
have been hesitant to do it because our cost metrics for qual
clauses are pretty nearly completely bogus: practically all
the built-in functions are assigned cost 1, even though in
reality they have a wide range of runtimes. Selectivity isn't
enormously reliable either. We could easily be taking a qual
order that the user has chosen carefully and stirring it around
more or less at random.
I'm suspicious of the particular form of this expression, too,
because selectivities close to 1 will produce very substantial
effects on the estimate even though there may not be that much
difference in practice, and the selectivity difference may be
mostly sampling error in the first place. I think you need
a formula that's not very sensitive to small differences, but
this will fail that test.
We had a similar discussion about two years ago concerning a
patch that (IIRC) tried to order sort columns according to
the estimated cost of the comparison functions. That got
reverted for a few reasons, but one of the big ones was that
the cost comparisons were largely garbage-in-garbage-out.
I think that a prerequisite for any work in this area is to
try to assign more realistic procost estimates to at least
a substantial fraction of the built-in pg_proc entries.
That's going to be tedious and probably contentious, but
it's hard to believe we can make much progress without
better cost data.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-22 21:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-22 05:50 [RFC][PATCH] Order qual clauses by combined cost and selectivity Staroverov Ilja <[email protected]>
2026-04-22 21:47 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox