From 542ec565d8f0449d5f853af58f34886166ebfb03 Mon Sep 17 00:00:00 2001 From: Staroverov Ilya 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 + #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