diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 129fb447099..b2b457b614c 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -140,6 +140,33 @@ JumbleQuery(Query *query) return jstate; } +JumbleState * +JumbleExpr(Expr *expr, uint64 *queryId) +{ + JumbleState *jstate = NULL; + + Assert(queryId != NULL); + + jstate = (JumbleState *) palloc(sizeof(JumbleState)); + + /* Set up workspace for query jumbling */ + jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE); + jstate->jumble_len = 0; + jstate->clocations_buf_size = 32; + jstate->clocations = (LocationLen *) + palloc(jstate->clocations_buf_size * sizeof(LocationLen)); + jstate->clocations_count = 0; + jstate->highest_extern_param_id = 0; + + /* Compute query ID */ + _jumbleNode(jstate, (Node *) expr); + *queryId = DatumGetUInt64(hash_any_extended(jstate->jumble, + jstate->jumble_len, + 0)); + + return jstate; +} + /* * Enables query identifier computation. * diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 00cd7358ebb..0216d61b801 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -16,12 +16,15 @@ #include "postgres.h" #include "catalog/pg_aggregate.h" +#include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "commands/dbcommands.h" +#include "common/hashfn.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" +#include "nodes/queryjumble.h" #include "optimizer/optimizer.h" #include "parser/analyze.h" #include "parser/parse_agg.h" @@ -41,6 +44,9 @@ #include "utils/lsyscache.h" #include "utils/timestamp.h" #include "utils/xml.h" +#include "utils/syscache.h" + +bool enable_or_transformation = true; /* GUC parameters */ bool Transform_null_equals = false; @@ -110,6 +116,415 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname, static Node *make_nulltest_from_distinct(ParseState *pstate, A_Expr *distincta, Node *arg); +typedef struct OrClauseGroupKey +{ + NodeTag type; + + Expr *expr; /* Pointer to the expression tree which has been a source for + the hashkey value */ + Oid opno; + Oid exprtype; +} OrClauseGroupKey; + +typedef struct OrClauseGroupEntry +{ + OrClauseGroupKey key; + + List *consts; + List *exprs; + FunctionCallInfo fcinfo; +} OrClauseGroupEntry; + +/* + * Hash function to find candidate clauses. + */ +static uint32 +orclause_hash(const void *data, Size keysize) +{ + OrClauseGroupKey *key = (OrClauseGroupKey *) data; + uint64 exprHash; + + Assert(keysize == sizeof(OrClauseGroupKey)); + Assert(IsA(data, Invalid)); + + (void) JumbleExpr(key->expr, &exprHash); + + return hash_combine((uint32) exprHash, + hash_combine((uint32) key->opno, + (uint32) key->exprtype)); +} + +static void * +orclause_keycopy(void *dest, const void *src, Size keysize) +{ + OrClauseGroupKey *src_key = (OrClauseGroupKey *) src; + OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest; + + Assert(sizeof(OrClauseGroupKey) == keysize); + Assert(IsA(src, Invalid)); + + dst_key->type = T_Invalid; + dst_key->expr = src_key->expr; + dst_key->opno = src_key->opno; + dst_key->exprtype = src_key->exprtype; + return dst_key; +} + +/* + * Dynahash match function to use in or_group_htab + */ +static int +orclause_match(const void *data1, const void *data2, Size keysize) +{ + OrClauseGroupKey *key1 = (OrClauseGroupKey *) data1; + OrClauseGroupKey *key2 = (OrClauseGroupKey *) data2; + + Assert(sizeof(OrClauseGroupKey) == keysize); + Assert(IsA(key1, Invalid)); + Assert(IsA(key2, Invalid)); + + if (key1->opno == key2->opno && + key1->exprtype == key2->exprtype && + equal(key1->expr, key2->expr)) + return 0; + + return 1; +} + +static FunctionCallInfo locfcinfo = NULL; + +static int +saop_const_comparator(const ListCell *a, const ListCell *b) +{ + Node *leftop = (Node *) lfirst(a); + Node *rightop = (Node *) lfirst(b); + + if (IsA(leftop, RelabelType)) + leftop = (Node *) ((RelabelType *) leftop)->arg; + + if (IsA(rightop, RelabelType)) + rightop = (Node *) ((RelabelType *) rightop)->arg; + + Assert(IsA(leftop, Const) && IsA(rightop, Const)); + + locfcinfo->args[0].value = ((Const *) leftop)->constvalue; + locfcinfo->args[0].isnull = false; + locfcinfo->args[1].value = ((Const *) rightop)->constvalue; + locfcinfo->args[1].isnull = false; + return DatumGetInt32(FunctionCallInvoke(locfcinfo)); +} + +static List * +saop_delete_duplicates(ParseState *pstate, OrClauseGroupEntry *entry, + Oid scalar_type) +{ + ListCell *lc; + List *result; + + locfcinfo = entry->fcinfo; + list_sort(entry->consts, saop_const_comparator); + + result = list_make1(linitial(entry->consts)); + for_each_from(lc, entry->consts, 1) + { + Node *node = (Node *) lfirst(lc); + + if (equal(llast(result), node)) + continue; + + node = coerce_to_common_type(pstate, node, scalar_type, "IN"); + result = lappend(result, node); + } + + return result; +} + +/* + * TransformOrExprToANY - + * Discover the args of an OR expression and try to group similar OR + * expressions to an ANY operation. + * Transformation must be already done on input args list before the call. + * Transformation groups two-sided equality operations. One side of such an + * operation must be plain constant or constant expression. The other side of + * the clause must be a variable expression without volatile functions. + * The grouping technique is based on an equivalence of variable sides of the + * expression: using queryId and equal() routine, it groups constant sides of + * similar clauses into an array. After the grouping procedure, each couple + * ('variable expression' and 'constant array') form a new SAOP operation, + * which is added to the args list of the returning expression. + * + * NOTE: function returns OR BoolExpr if more than one clause are detected in + * the final args list, or ScalarArrayOpExpr if all args were grouped into + * the single SAOP expression. + */ +static Node * +TransformOrExprToANY(ParseState *pstate, List *args, int location) +{ + List *or_list = NIL; + List *groups = NIL; + List *unconvinient_ors = NIL; + ListCell *lc; + HASHCTL info; + HTAB *or_group_htab = NULL; + int len_ors = list_length(args); + OrClauseGroupEntry *entry = NULL; + + Assert(enable_or_transformation && len_ors > 1); + + MemSet(&info, 0, sizeof(info)); + info.keysize = sizeof(OrClauseGroupKey); + info.entrysize = sizeof(OrClauseGroupEntry); + info.hash = orclause_hash; + info.keycopy = orclause_keycopy; + info.match = orclause_match; + or_group_htab = hash_create("OR Groups", + len_ors, + &info, + HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY); + + foreach(lc, args) + { + Node *orqual = lfirst(lc); + Node *const_expr; + Node *nconst_expr; + OrClauseGroupKey hashkey; + bool found; + Oid opno; + Oid exprtype; + Node *leftop, *rightop; + TypeCacheEntry *typentry; + + if (!IsA(orqual, OpExpr)) + { + unconvinient_ors = lappend(unconvinient_ors, orqual); + continue; + } + + opno = ((OpExpr *) orqual)->opno; + if (get_op_rettype(opno) != BOOLOID) + { + /* Only operator returning boolean suits OR -> ANY transformation */ + unconvinient_ors = lappend(unconvinient_ors, orqual); + continue; + } + + /* + * Detect the constant side of the clause. Recall non-constant + * expression can be made not only with Vars, but also with Params, + * which is not bonded with any relation. Thus, we detect the const + * side - if another side is constant too, the orqual couldn't be + * an OpExpr. + * Get pointers to constant and expression sides of the qual. + */ + leftop = get_leftop(orqual); + if (IsA(leftop, RelabelType)) + leftop = (Node *) ((RelabelType *) leftop)->arg; + rightop = get_rightop(orqual); + if (IsA(rightop, RelabelType)) + rightop = (Node *) ((RelabelType *) rightop)->arg; + + if (IsA(leftop, Const)) + { + opno = get_commutator(opno); + + if (!OidIsValid(opno)) + { + /* commutator doesn't exist, we can't reverse the order */ + unconvinient_ors = lappend(unconvinient_ors, orqual); + continue; + } + + nconst_expr = get_rightop(orqual); + const_expr = get_leftop(orqual); + } + else if (IsA(rightop, Const)) + { + const_expr = get_rightop(orqual); + nconst_expr = get_leftop(orqual); + } + else + { + unconvinient_ors = lappend(unconvinient_ors, orqual); + continue; + } + + /* + * Transformation only works with both side type is not + * { array | composite | domain | record }. + * Also, forbid it for volatile expressions. + */ + exprtype = exprType(nconst_expr); + if (type_is_rowtype(exprType(const_expr)) || + type_is_rowtype(exprtype) || + contain_volatile_functions((Node *) nconst_expr)) + { + unconvinient_ors = lappend(unconvinient_ors, orqual); + continue; + } + + typentry = lookup_type_cache(exprtype, TYPECACHE_CMP_PROC_FINFO); + + /* + * Within this transformation we remove duplicates. To avoid + * quadratic behavior we need to sort clauses after making a list of + * elements. So, need sort operator to implement that. + */ + if (!OidIsValid(typentry->cmp_proc_finfo.fn_oid)) + { + unconvinient_ors = lappend(unconvinient_ors, orqual); + continue; + } + + /* + * At this point we definitely have a transformable clause. + * Classify it and add into specific group of clauses, or create new + * group. + */ + hashkey.type = T_Invalid; + hashkey.expr = (Expr *) nconst_expr; + hashkey.opno = opno; + hashkey.exprtype = exprtype; + entry = hash_search(or_group_htab, &hashkey, HASH_ENTER, &found); + + if (unlikely(found)) + { + entry->consts = lappend(entry->consts, const_expr); + entry->exprs = lappend(entry->exprs, orqual); + } + else + { + entry->consts = list_make1(const_expr); + entry->exprs = list_make1(orqual); + + /* Prepare funcall for sort comparator */ + entry->fcinfo = + (FunctionCallInfo) palloc(SizeForFunctionCallInfo(2)); + InitFunctionCallInfoData(*(entry->fcinfo), + &typentry->cmp_proc_finfo, 2, + typentry->typcollation, NULL, NULL); + + /* + * Add the entry to the list. It is needed exclusively to manage the + * problem with the order of transformed clauses in explain. + * Hash value can depend on the platform and version. Hence, + * sequental scan of the hash table would prone to change the order + * of clauses in lists and, as a result, break regression tests + * accidentially. + */ + groups = lappend(groups, entry); + } + } + + /* Let's convert each group of clauses to an IN operation. */ + + /* + * Go through the list of groups and convert each, where number of + * consts more than 1. trivial groups move to OR-list again + */ + foreach (lc, groups) + { + Oid scalar_type; + Oid array_type; + + if (!IsA(lfirst(lc), Invalid)) + { + unconvinient_ors = lappend(unconvinient_ors, lfirst(lc)); + continue; + } + + entry = (OrClauseGroupEntry *) lfirst(lc); + + Assert(list_length(entry->consts) > 0); + Assert(list_length(entry->exprs) == list_length(entry->consts)); + + if (list_length(entry->consts) == 1) + { + /* + * Only one element returns origin expression into the BoolExpr args + * list unchanged. + */ + list_free(entry->consts); + unconvinient_ors = list_concat(unconvinient_ors, entry->exprs); + continue; + } + + /* + * Do the transformation. + */ + + scalar_type = entry->key.exprtype; + array_type = OidIsValid(scalar_type) ? get_array_type(scalar_type) : + InvalidOid; + + if (OidIsValid(array_type)) + { + /* + * OK: coerce all the right-hand non-Var inputs to the common + * type and build an ArrayExpr for them. + */ + List *aexprs = NIL; + ArrayExpr *newa = NULL; + ScalarArrayOpExpr *saopexpr = NULL; + HeapTuple opertup; + Form_pg_operator operform; + List *namelist = NIL; + + aexprs = saop_delete_duplicates(pstate, entry, scalar_type); + + newa = makeNode(ArrayExpr); + /* array_collid will be set by parse_collate.c */ + newa->element_typeid = scalar_type; + newa->array_typeid = array_type; + newa->multidims = false; + newa->elements = aexprs; + newa->location = -1; + + opertup = SearchSysCache1(OPEROID, + ObjectIdGetDatum(entry->key.opno)); + if (!HeapTupleIsValid(opertup)) + elog(ERROR, "cache lookup failed for operator %u", + entry->key.opno); + + operform = (Form_pg_operator) GETSTRUCT(opertup); + if (!OperatorIsVisible(entry->key.opno)) + namelist = lappend(namelist, makeString(get_namespace_name(operform->oprnamespace))); + + namelist = lappend(namelist, makeString(pstrdup(NameStr(operform->oprname)))); + ReleaseSysCache(opertup); + + saopexpr = + (ScalarArrayOpExpr *) + make_scalar_array_op(pstate, + namelist, + true, + (Node *) entry->key.expr, + (Node *) newa, + -1); + + or_list = lappend(or_list, makeBoolExpr(AND_EXPR, list_make2(makeBoolExpr(OR_EXPR, entry->exprs, location), saopexpr), -1)); + } + else + { + /* + * If the const node (right side of operator expression) 's type + * don't have “true” array type, then we cannnot do the transformation. + * We simply concatenate the expression node. + * + */ + list_free(entry->consts); + unconvinient_ors = list_concat(unconvinient_ors, entry->exprs); + } + } + if(unconvinient_ors != NIL) + or_list = lappend(or_list, list_length(unconvinient_ors) > 1 ? makeBoolExpr(OR_EXPR, unconvinient_ors, location) : linitial(unconvinient_ors)); + hash_destroy(or_group_htab); + list_free(groups); + + /* One more trick: assemble correct clause */ + return (Node *) ((list_length(or_list) > 1) ? + makeBoolExpr(OR_EXPR, or_list, location) : + linitial(or_list)); +} /* * transformExpr - @@ -1432,6 +1847,11 @@ transformBoolExpr(ParseState *pstate, BoolExpr *a) args = lappend(args, arg); } + /* Make an attempt to group similar OR clauses into ANY operation */ + if (enable_or_transformation && a->boolop == OR_EXPR && + list_length(args) >0) + return TransformOrExprToANY(pstate, args, a->location); + return (Node *) makeBoolExpr(a->boolop, args, a->location); } diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 46c258be282..f5da097fcdf 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -1007,6 +1007,17 @@ struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"enable_or_transformation", PGC_USERSET, QUERY_TUNING_OTHER, + gettext_noop("Transform a sequence of OR clauses to an array expression."), + gettext_noop("The planner will replace expression like 'x=c1 OR x=c2 ..'" + "to the expression 'x = ANY(ARRAY[c1,c2,..])'"), + GUC_EXPLAIN + }, + &enable_or_transformation, + true, + NULL, NULL, NULL + }, { /* * Not for general use --- used by SET SESSION AUTHORIZATION and SET diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index f1c55c8067f..a9ae048af52 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -65,6 +65,7 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); +extern JumbleState *JumbleExpr(Expr *expr, uint64 *queryId); extern void EnableQueryId(void); extern PGDLLIMPORT bool query_id_enabled; diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h index 7b63c5cf718..35ab5775019 100644 --- a/src/include/optimizer/optimizer.h +++ b/src/include/optimizer/optimizer.h @@ -50,6 +50,7 @@ struct PlannedStmt; struct ParamListInfoData; struct HeapTupleData; +extern PGDLLIMPORT bool enable_or_transformation; /* in path/clausesel.c: */ diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 6b16c3a6769..ccf280001a0 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4210,10 +4210,10 @@ explain (costs off) select * from tenk1 a join tenk1 b on (a.unique1 = 1 and b.unique1 = 2) or ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ Nested Loop - Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4))) + Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) -> Bitmap Heap Scan on tenk1 b Recheck Cond: ((unique1 = 2) OR (hundred = 4)) -> BitmapOr @@ -4223,16 +4223,64 @@ select * from tenk1 a join tenk1 b on Index Cond: (hundred = 4) -> Materialize -> Bitmap Heap Scan on tenk1 a - Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7)) + Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[]))) -> BitmapOr -> Bitmap Index Scan on tenk1_unique1 Index Cond: (unique1 = 1) -> Bitmap Index Scan on tenk1_unique2 - Index Cond: (unique2 = 3) + Index Cond: (unique2 = ANY ('{3,7}'::integer[])) +(17 rows) + +SET enable_or_transformation = on; +explain (costs off) +select * from tenk1 a join tenk1 b on + (a.unique1 = 1 and b.unique1 = 2) or + ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ + Nested Loop + Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) + -> Bitmap Heap Scan on tenk1 b + Recheck Cond: ((unique1 = 2) OR (hundred = 4)) + -> BitmapOr + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = 2) + -> Bitmap Index Scan on tenk1_hundred + Index Cond: (hundred = 4) + -> Materialize + -> Bitmap Heap Scan on tenk1 a + Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[]))) + -> BitmapOr + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = 1) -> Bitmap Index Scan on tenk1_unique2 - Index Cond: (unique2 = 7) -(19 rows) + Index Cond: (unique2 = ANY ('{3,7}'::integer[])) +(17 rows) + +explain (costs off) +select * from tenk1 a join tenk1 b on + (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or + ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------- + Nested Loop + Join Filter: ((a.unique1 < 20) OR (a.unique1 = 3) OR ((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) + -> Seq Scan on tenk1 b + -> Materialize + -> Bitmap Heap Scan on tenk1 a + Recheck Cond: ((unique1 < 20) OR (unique1 = 3) OR (unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[]))) + -> BitmapOr + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 < 20) + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = 3) + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = 1) + -> Bitmap Index Scan on tenk1_unique2 + Index Cond: (unique2 = ANY ('{3,7}'::integer[])) +(15 rows) +RESET enable_or_transformation; -- -- test placement of movable quals in a parameterized join tree -- diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7ca98397aec..9c2a4d75619 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -82,25 +82,47 @@ explain (costs off) select * from lp where a is null; (2 rows) explain (costs off) select * from lp where a = 'a' or a = 'c'; - QUERY PLAN ----------------------------------------------------------- + QUERY PLAN +----------------------------------------------- Append -> Seq Scan on lp_ad lp_1 - Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) + Filter: (a = ANY ('{a,c}'::bpchar[])) -> Seq Scan on lp_bc lp_2 - Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) + Filter: (a = ANY ('{a,c}'::bpchar[])) (5 rows) explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Append -> Seq Scan on lp_ad lp_1 - Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) + Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[]))) -> Seq Scan on lp_bc lp_2 - Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) + Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[]))) (5 rows) +SET enable_or_transformation = on; +explain (costs off) select * from lp where a = 'a' or a = 'c'; + QUERY PLAN +----------------------------------------------- + Append + -> Seq Scan on lp_ad lp_1 + Filter: (a = ANY ('{a,c}'::bpchar[])) + -> Seq Scan on lp_bc lp_2 + Filter: (a = ANY ('{a,c}'::bpchar[])) +(5 rows) + +explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); + QUERY PLAN +--------------------------------------------------------------------- + Append + -> Seq Scan on lp_ad lp_1 + Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[]))) + -> Seq Scan on lp_bc lp_2 + Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[]))) +(5 rows) + +RESET enable_or_transformation; explain (costs off) select * from lp where a <> 'g'; QUERY PLAN ------------------------------------ @@ -515,10 +537,10 @@ explain (costs off) select * from rlp where a <= 31; (27 rows) explain (costs off) select * from rlp where a = 1 or a = 7; - QUERY PLAN --------------------------------- + QUERY PLAN +------------------------------------------ Seq Scan on rlp2 rlp - Filter: ((a = 1) OR (a = 7)) + Filter: (a = ANY ('{1,7}'::integer[])) (2 rows) explain (costs off) select * from rlp where a = 1 or b = 'ab'; @@ -596,13 +618,13 @@ explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25); -- where clause contradicts sub-partition's constraint explain (costs off) select * from rlp where a = 20 or a = 40; - QUERY PLAN ----------------------------------------- + QUERY PLAN +-------------------------------------------------- Append -> Seq Scan on rlp4_1 rlp_1 - Filter: ((a = 20) OR (a = 40)) + Filter: (a = ANY ('{20,40}'::integer[])) -> Seq Scan on rlp5_default rlp_2 - Filter: ((a = 20) OR (a = 40)) + Filter: (a = ANY ('{20,40}'::integer[])) (5 rows) explain (costs off) select * from rlp3 where a = 20; /* empty */ @@ -671,6 +693,163 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) (11 rows) +SET enable_or_transformation = on; +explain (costs off) select * from rlp where a = 1 or a = 7; + QUERY PLAN +------------------------------------------ + Seq Scan on rlp2 rlp + Filter: (a = ANY ('{1,7}'::integer[])) +(2 rows) + +explain (costs off) select * from rlp where a = 1 or b = 'ab'; + QUERY PLAN +------------------------------------------------------- + Append + -> Seq Scan on rlp1 rlp_1 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp2 rlp_2 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp3abcd rlp_3 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_1 rlp_4 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_2 rlp_5 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp4_default rlp_6 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp5_1 rlp_7 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp5_default rlp_8 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_10 rlp_9 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_30 rlp_10 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_null rlp_11 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) + -> Seq Scan on rlp_default_default rlp_12 + Filter: ((a = 1) OR ((b)::text = 'ab'::text)) +(25 rows) + +explain (costs off) select * from rlp where a > 20 and a < 27; + QUERY PLAN +----------------------------------------- + Append + -> Seq Scan on rlp4_1 rlp_1 + Filter: ((a > 20) AND (a < 27)) + -> Seq Scan on rlp4_2 rlp_2 + Filter: ((a > 20) AND (a < 27)) +(5 rows) + +explain (costs off) select * from rlp where a = 29; + QUERY PLAN +------------------------------ + Seq Scan on rlp4_default rlp + Filter: (a = 29) +(2 rows) + +explain (costs off) select * from rlp where a >= 29; + QUERY PLAN +--------------------------------------------- + Append + -> Seq Scan on rlp4_default rlp_1 + Filter: (a >= 29) + -> Seq Scan on rlp5_1 rlp_2 + Filter: (a >= 29) + -> Seq Scan on rlp5_default rlp_3 + Filter: (a >= 29) + -> Seq Scan on rlp_default_30 rlp_4 + Filter: (a >= 29) + -> Seq Scan on rlp_default_default rlp_5 + Filter: (a >= 29) +(11 rows) + +explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25); + QUERY PLAN +------------------------------------------------------ + Append + -> Seq Scan on rlp1 rlp_1 + Filter: ((a < 1) OR ((a > 20) AND (a < 25))) + -> Seq Scan on rlp4_1 rlp_2 + Filter: ((a < 1) OR ((a > 20) AND (a < 25))) +(5 rows) + +explain (costs off) select * from rlp where a = 20 or a = 40; + QUERY PLAN +-------------------------------------------------- + Append + -> Seq Scan on rlp4_1 rlp_1 + Filter: (a = ANY ('{20,40}'::integer[])) + -> Seq Scan on rlp5_default rlp_2 + Filter: (a = ANY ('{20,40}'::integer[])) +(5 rows) + +explain (costs off) select * from rlp3 where a = 20; /* empty */ + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +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)) +(2 rows) + +explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */ + QUERY PLAN +---------------------------------------------- + Append + -> Seq Scan on rlp3abcd rlp_1 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp3efgh rlp_2 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp3nullxy rlp_3 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp3_default rlp_4 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp4_1 rlp_5 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp4_2 rlp_6 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp4_default rlp_7 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp5_1 rlp_8 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp5_default rlp_9 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp_default_30 rlp_10 + Filter: ((a > 1) AND (a >= 15)) + -> Seq Scan on rlp_default_default rlp_11 + Filter: ((a > 1) AND (a >= 15)) +(23 rows) + +explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); + QUERY PLAN +------------------------------------------------------------------- + Append + -> Seq Scan on rlp2 rlp_1 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3abcd rlp_2 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3efgh rlp_3 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3nullxy rlp_4 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) + -> Seq Scan on rlp3_default rlp_5 + Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15))) +(11 rows) + +RESET enable_or_transformation; -- multi-column keys create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); create table mc3p_default partition of mc3p default; @@ -2072,10 +2251,10 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde'; explain (costs off) select * from hp where a = 1 and b = 'abcde' and (c = 2 or c = 3); - QUERY PLAN ----------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- Seq Scan on hp2 hp - Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3))) + Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text)) (2 rows) drop table hp2; diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 8c4da955084..7678744181c 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -1322,19 +1322,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1'''); estimated | actual -----------+-------- - 99 | 100 + 100 | 100 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')'); estimated | actual -----------+-------- - 99 | 100 + 100 | 100 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')'); estimated | actual -----------+-------- - 197 | 200 + 200 | 200 (1 row) -- OR clauses referencing different attributes are incompatible @@ -1664,19 +1664,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1'''); estimated | actual -----------+-------- - 99 | 100 + 100 | 100 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')'); estimated | actual -----------+-------- - 99 | 100 + 100 | 100 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')'); estimated | actual -----------+-------- - 197 | 200 + 200 | 200 (1 row) -- OR clauses referencing different attributes diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out index dbfd0c13d46..6123351a9a8 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -147,6 +147,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_memoize | on enable_mergejoin | on enable_nestloop | on + enable_or_transformation | on enable_parallel_append | on enable_parallel_hash | on enable_partition_pruning | on diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index e296891cab8..057c6d2bda1 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -738,6 +738,41 @@ SELECT count(*) FROM tenk1 SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); +SET enable_or_transformation = on; +EXPLAIN (COSTS OFF) +SELECT * FROM tenk1 + WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); +SELECT * FROM tenk1 + WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 + WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); +SELECT count(*) FROM tenk1 + WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 + WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR 42 > thousand); + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 + WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41; +SELECT count(*) FROM tenk1 + WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 + WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; +SELECT count(*) FROM tenk1 + WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 + WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); +SELECT count(*) FROM tenk1 + WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); +RESET enable_or_transformation; + -- -- Check behavior with duplicate index column contents -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 8bfe3b7ba67..c77b5c50f01 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1408,6 +1408,16 @@ explain (costs off) select * from tenk1 a join tenk1 b on (a.unique1 = 1 and b.unique1 = 2) or ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); +SET enable_or_transformation = on; +explain (costs off) +select * from tenk1 a join tenk1 b on + (a.unique1 = 1 and b.unique1 = 2) or + ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); +explain (costs off) +select * from tenk1 a join tenk1 b on + (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or + ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); +RESET enable_or_transformation; -- -- test placement of movable quals in a parameterized join tree diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index a09b27d820c..9717c8c835c 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -21,6 +21,12 @@ explain (costs off) select * from lp where a is not null; explain (costs off) select * from lp where a is null; explain (costs off) select * from lp where a = 'a' or a = 'c'; explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); + +SET enable_or_transformation = on; +explain (costs off) select * from lp where a = 'a' or a = 'c'; +explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); +RESET enable_or_transformation; + explain (costs off) select * from lp where a <> 'g'; explain (costs off) select * from lp where a <> 'a' and a <> 'd'; explain (costs off) select * from lp where a not in ('a', 'd'); @@ -99,6 +105,22 @@ explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, i explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); + +SET enable_or_transformation = on; +explain (costs off) select * from rlp where a = 1 or a = 7; +explain (costs off) select * from rlp where a = 1 or b = 'ab'; +explain (costs off) select * from rlp where a > 20 and a < 27; +explain (costs off) select * from rlp where a = 29; +explain (costs off) select * from rlp where a >= 29; +explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25); +explain (costs off) select * from rlp where a = 20 or a = 40; +explain (costs off) select * from rlp3 where a = 20; /* empty */ +explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */ +explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */ +explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ +explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); +RESET enable_or_transformation; + -- multi-column keys create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); create table mc3p_default partition of mc3p default; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..0499bedb9eb 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -22,6 +22,12 @@ EXPLAIN (COSTS OFF) SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid; SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid; +SET enable_or_transformation = on; +EXPLAIN (COSTS OFF) +SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid; +SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid; +RESET enable_or_transformation; + -- ctid = ScalarArrayOp - implemented as tidscan EXPLAIN (COSTS OFF) SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 61ad417cde6..94450939331 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1714,6 +1714,8 @@ NumericVar OM_uint32 OP OSAPerGroupState +OrClauseGroupEntry +OrClauseGroupKey OSAPerQueryState OSInfo OSSLCipher