public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v5 1/7] Row pattern recognition patch for raw parser. 13+ messages / 4 participants [nested] [flat]
* [PATCH v5 1/7] Row pattern recognition patch for raw parser. @ 2023-09-02 06:32 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Tatsuo Ishii @ 2023-09-02 06:32 UTC (permalink / raw) --- src/backend/parser/gram.y | 216 +++++++++++++++++++++++++++++--- src/include/nodes/parsenodes.h | 56 +++++++++ src/include/parser/kwlist.h | 8 ++ src/include/parser/parse_node.h | 1 + 4 files changed, 267 insertions(+), 14 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 7d2032885e..70409cdc9a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -251,6 +251,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DefElem *defelt; SortBy *sortby; WindowDef *windef; + RPCommonSyntax *rpcom; + RPSubsetItem *rpsubset; JoinExpr *jexpr; IndexElem *ielem; StatsElem *selem; @@ -453,8 +455,12 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); TriggerTransitions TriggerReferencing vacuum_relation_list opt_vacuum_relation_list drop_option_list pub_obj_list - -%type <node> opt_routine_body + row_pattern_measure_list row_pattern_definition_list + opt_row_pattern_subset_clause + row_pattern_subset_list row_pattern_subset_rhs + row_pattern +%type <rpsubset> row_pattern_subset_item +%type <node> opt_routine_body row_pattern_term %type <groupclause> group_clause %type <list> group_by_list %type <node> group_by_item empty_grouping_set rollup_clause cube_clause @@ -551,6 +557,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <range> relation_expr_opt_alias %type <node> tablesample_clause opt_repeatable_clause %type <target> target_el set_target insert_column_item + row_pattern_measure_item row_pattern_definition %type <str> generic_option_name %type <node> generic_option_arg @@ -633,6 +640,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> window_clause window_definition_list opt_partition_clause %type <windef> window_definition over_clause window_specification opt_frame_clause frame_extent frame_bound +%type <rpcom> opt_row_pattern_common_syntax opt_row_pattern_skip_to +%type <boolean> opt_row_pattern_initial_or_seek +%type <list> opt_row_pattern_measures %type <ival> opt_window_exclusion_clause %type <str> opt_existing_window_name %type <boolean> opt_if_not_exists @@ -659,7 +669,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_object_constructor_null_clause_opt json_array_constructor_null_clause_opt - /* * Non-keyword token types. These are hard-wired into the "flex" lexer. * They must be listed first so that their numeric codes do not depend on @@ -702,7 +711,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS - DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC + DEFERRABLE DEFERRED DEFINE DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP @@ -718,7 +727,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); HANDLER HAVING HEADER_P HOLD HOUR_P IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE - INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P + INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIAL INITIALLY INLINE_P INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION @@ -731,7 +740,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED - MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE METHOD + MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MEASURES MERGE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO NONE @@ -743,8 +752,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD - PLACING PLANS POLICY + PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PAST + PATTERN_P PERMUTE PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -755,12 +764,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE - SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT + SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SEEK SELECT SEQUENCE SEQUENCES + SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P - SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER + SUBSCRIPTION SUBSET SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM @@ -853,6 +863,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); */ %nonassoc UNBOUNDED /* ideally would have same precedence as IDENT */ %nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP +%nonassoc MEASURES AFTER INITIAL SEEK PATTERN_P %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' @@ -15857,7 +15868,8 @@ over_clause: OVER window_specification ; window_specification: '(' opt_existing_window_name opt_partition_clause - opt_sort_clause opt_frame_clause ')' + opt_sort_clause opt_row_pattern_measures opt_frame_clause + opt_row_pattern_common_syntax ')' { WindowDef *n = makeNode(WindowDef); @@ -15865,10 +15877,12 @@ window_specification: '(' opt_existing_window_name opt_partition_clause n->refname = $2; n->partitionClause = $3; n->orderClause = $4; + n->rowPatternMeasures = $5; /* copy relevant fields of opt_frame_clause */ - n->frameOptions = $5->frameOptions; - n->startOffset = $5->startOffset; - n->endOffset = $5->endOffset; + n->frameOptions = $6->frameOptions; + n->startOffset = $6->startOffset; + n->endOffset = $6->endOffset; + n->rpCommonSyntax = $7; n->location = @1; $$ = n; } @@ -15892,6 +15906,31 @@ opt_partition_clause: PARTITION BY expr_list { $$ = $3; } | /*EMPTY*/ { $$ = NIL; } ; +/* + * ROW PATTERN_P MEASURES + */ +opt_row_pattern_measures: MEASURES row_pattern_measure_list { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_measure_list: + row_pattern_measure_item + { $$ = list_make1($1); } + | row_pattern_measure_list ',' row_pattern_measure_item + { $$ = lappend($1, $3); } + ; + +row_pattern_measure_item: + a_expr AS ColLabel + { + $$ = makeNode(ResTarget); + $$->name = $3; + $$->indirection = NIL; + $$->val = (Node *) $1; + $$->location = @1; + } + ; + /* * For frame clauses, we return a WindowDef, but only some fields are used: * frameOptions, startOffset, and endOffset. @@ -16051,6 +16090,139 @@ opt_window_exclusion_clause: | /*EMPTY*/ { $$ = 0; } ; +opt_row_pattern_common_syntax: +opt_row_pattern_skip_to opt_row_pattern_initial_or_seek + PATTERN_P '(' row_pattern ')' + opt_row_pattern_subset_clause + DEFINE row_pattern_definition_list + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = $1->rpSkipTo; + n->rpSkipVariable = $1->rpSkipVariable; + n->initial = $2; + n->rpPatterns = $5; + n->rpSubsetClause = $7; + n->rpDefs = $9; + $$ = n; + } + | /*EMPTY*/ { $$ = NULL; } + ; + +opt_row_pattern_skip_to: + AFTER MATCH SKIP TO NEXT ROW + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_NEXT_ROW; + n->rpSkipVariable = NULL; + $$ = n; + } + | AFTER MATCH SKIP PAST LAST_P ROW + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_PAST_LAST_ROW; + n->rpSkipVariable = NULL; + $$ = n; + } +/* + | AFTER MATCH SKIP TO FIRST_P ColId %prec FIRST_P + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_FIRST_VARIABLE; + n->rpSkipVariable = $6; + $$ = n; + } + | AFTER MATCH SKIP TO LAST_P ColId %prec LAST_P + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_LAST_VARIABLE; + n->rpSkipVariable = $6; + $$ = n; + } + * Shift/reduce + | AFTER MATCH SKIP TO ColId + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_VARIABLE; + n->rpSkipVariable = $5; + $$ = n; + } +*/ + | /*EMPTY*/ + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + /* temporary set default to ST_NEXT_ROW */ + n->rpSkipTo = ST_PAST_LAST_ROW; + n->rpSkipVariable = NULL; + $$ = n; + } + ; + +opt_row_pattern_initial_or_seek: + INITIAL { $$ = true; } + | SEEK + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("SEEK is not supported"), + errhint("Use INITIAL."), + parser_errposition(@1))); + } + | /*EMPTY*/ { $$ = true; } + ; + +row_pattern: + row_pattern_term { $$ = list_make1($1); } + | row_pattern row_pattern_term { $$ = lappend($1, $2); } + ; + +row_pattern_term: + ColId { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "", (Node *)makeString($1), NULL, @1); } + | ColId '*' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "*", (Node *)makeString($1), NULL, @1); } + | ColId '+' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "+", (Node *)makeString($1), NULL, @1); } + | ColId '?' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "?", (Node *)makeString($1), NULL, @1); } + ; + +opt_row_pattern_subset_clause: + SUBSET row_pattern_subset_list { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_subset_list: + row_pattern_subset_item { $$ = list_make1($1); } + | row_pattern_subset_list ',' row_pattern_subset_item { $$ = lappend($1, $3); } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_subset_item: ColId '=' '(' row_pattern_subset_rhs ')' + { + RPSubsetItem *n = makeNode(RPSubsetItem); + n->name = $1; + n->rhsVariable = $4; + $$ = n; + } + ; + +row_pattern_subset_rhs: + ColId { $$ = list_make1(makeStringConst($1, @1)); } + | row_pattern_subset_rhs ',' ColId { $$ = lappend($1, makeStringConst($3, @1)); } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_definition_list: + row_pattern_definition { $$ = list_make1($1); } + | row_pattern_definition_list ',' row_pattern_definition { $$ = lappend($1, $3); } + ; + +row_pattern_definition: + ColId AS a_expr + { + $$ = makeNode(ResTarget); + $$->name = $1; + $$->indirection = NIL; + $$->val = (Node *) $3; + $$->location = @1; + } + ; /* * Supporting nonterminals for expressions. @@ -17146,6 +17318,7 @@ unreserved_keyword: | INDEXES | INHERIT | INHERITS + | INITIAL | INLINE_P | INPUT_P | INSENSITIVE @@ -17173,6 +17346,7 @@ unreserved_keyword: | MATCHED | MATERIALIZED | MAXVALUE + | MEASURES | MERGE | METHOD | MINUTE_P @@ -17215,6 +17389,9 @@ unreserved_keyword: | PARTITION | PASSING | PASSWORD + | PAST + | PATTERN_P + | PERMUTE | PLANS | POLICY | PRECEDING @@ -17265,6 +17442,7 @@ unreserved_keyword: | SEARCH | SECOND_P | SECURITY + | SEEK | SEQUENCE | SEQUENCES | SERIALIZABLE @@ -17290,6 +17468,7 @@ unreserved_keyword: | STRICT_P | STRIP_P | SUBSCRIPTION + | SUBSET | SUPPORT | SYSID | SYSTEM_P @@ -17477,6 +17656,7 @@ reserved_keyword: | CURRENT_USER | DEFAULT | DEFERRABLE + | DEFINE | DESC | DISTINCT | DO @@ -17639,6 +17819,7 @@ bare_label_keyword: | DEFAULTS | DEFERRABLE | DEFERRED + | DEFINE | DEFINER | DELETE_P | DELIMITER @@ -17714,6 +17895,7 @@ bare_label_keyword: | INDEXES | INHERIT | INHERITS + | INITIAL | INITIALLY | INLINE_P | INNER_P @@ -17763,6 +17945,7 @@ bare_label_keyword: | MATCHED | MATERIALIZED | MAXVALUE + | MEASURES | MERGE | METHOD | MINVALUE @@ -17816,6 +17999,9 @@ bare_label_keyword: | PARTITION | PASSING | PASSWORD + | PAST + | PATTERN_P + | PERMUTE | PLACING | PLANS | POLICY @@ -17872,6 +18058,7 @@ bare_label_keyword: | SCROLL | SEARCH | SECURITY + | SEEK | SELECT | SEQUENCE | SEQUENCES @@ -17903,6 +18090,7 @@ bare_label_keyword: | STRICT_P | STRIP_P | SUBSCRIPTION + | SUBSET | SUBSTRING | SUPPORT | SYMMETRIC diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index fef4c714b8..4cc1fb417b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -547,6 +547,44 @@ typedef struct SortBy int location; /* operator location, or -1 if none/unknown */ } SortBy; +/* + * AFTER MATCH row pattern skip to types in row pattern common syntax + */ +typedef enum RPSkipTo +{ + ST_NONE, /* AFTER MATCH omitted */ + ST_NEXT_ROW, /* SKIP TO NEXT ROW */ + ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */ + ST_FIRST_VARIABLE, /* SKIP TO FIRST variable name */ + ST_LAST_VARIABLE, /* SKIP TO LAST variable name */ + ST_VARIABLE /* SKIP TO variable name */ +} RPSkipTo; + +/* + * Row Pattern SUBSET clause item + */ +typedef struct RPSubsetItem +{ + NodeTag type; + char *name; /* Row Pattern SUBSET clause variable name */ + List *rhsVariable; /* Row Pattern SUBSET rhs variables (list of char *string) */ +} RPSubsetItem; + +/* + * RowPatternCommonSyntax - raw representation of row pattern common syntax + * + */ +typedef struct RPCommonSyntax +{ + NodeTag type; + RPSkipTo rpSkipTo; /* Row Pattern AFTER MATCH SKIP type */ + char *rpSkipVariable; /* Row Pattern Skip To variable name, if any */ + bool initial; /* true if <row pattern initial or seek> is initial */ + List *rpPatterns; /* PATTERN variables (list of A_Expr) */ + List *rpSubsetClause; /* row pattern subset clause (list of RPSubsetItem), if any */ + List *rpDefs; /* row pattern definitions clause (list of ResTarget) */ +} RPCommonSyntax; + /* * WindowDef - raw representation of WINDOW and OVER clauses * @@ -562,6 +600,8 @@ typedef struct WindowDef char *refname; /* referenced window name, if any */ List *partitionClause; /* PARTITION BY expression list */ List *orderClause; /* ORDER BY (list of SortBy) */ + List *rowPatternMeasures; /* row pattern measures (list of ResTarget) */ + RPCommonSyntax *rpCommonSyntax; /* row pattern common syntax */ int frameOptions; /* frame_clause options, see below */ Node *startOffset; /* expression for starting bound, if any */ Node *endOffset; /* expression for ending bound, if any */ @@ -1483,6 +1523,11 @@ typedef struct GroupingSet * the orderClause might or might not be copied (see copiedOrder); the framing * options are never copied, per spec. * + * "defineClause" is Row Pattern Recognition DEFINE clause (list of + * TargetEntry). TargetEntry.resname represents row pattern definition + * variable name. "patternVariable" and "patternRegexp" represents PATTERN + * clause. + * * The information relevant for the query jumbling is the partition clause * type and its bounds. */ @@ -1514,6 +1559,17 @@ typedef struct WindowClause Index winref; /* ID referenced by window functions */ /* did we copy orderClause from refname? */ bool copiedOrder pg_node_attr(query_jumble_ignore); + /* Row Pattern AFTER MACH SKIP clause */ + RPSkipTo rpSkipTo; /* Row Pattern Skip To type */ + bool initial; /* true if <row pattern initial or seek> is initial */ + /* Row Pattern DEFINE clause (list of TargetEntry) */ + List *defineClause; + /* Row Pattern DEFINE variable initial names (list of String) */ + List *defineInitial; + /* Row Pattern PATTERN variable name (list of String) */ + List *patternVariable; + /* Row Pattern PATTERN regular expression quantifier ('+' or ''. list of String) */ + List *patternRegexp; } WindowClause; /* diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 5984dcfa4b..2804333b53 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -128,6 +128,7 @@ PG_KEYWORD("default", DEFAULT, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("defaults", DEFAULTS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("deferrable", DEFERRABLE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("deferred", DEFERRED, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("define", DEFINE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("definer", DEFINER, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("delete", DELETE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("delimiter", DELIMITER, UNRESERVED_KEYWORD, BARE_LABEL) @@ -212,6 +213,7 @@ PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("indexes", INDEXES, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inherit", INHERIT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inherits", INHERITS, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("initial", INITIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("initially", INITIALLY, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inline", INLINE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inner", INNER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) @@ -265,6 +267,7 @@ PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("measures", MEASURES, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD, AS_LABEL) @@ -326,6 +329,9 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("past", PAST, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("pattern", PATTERN_P, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("permute", PERMUTE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) @@ -385,6 +391,7 @@ PG_KEYWORD("scroll", SCROLL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("search", SEARCH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("second", SECOND_P, UNRESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("seek", SEEK, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("select", SELECT, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD, BARE_LABEL) @@ -416,6 +423,7 @@ PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("subset", SUBSET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("substring", SUBSTRING, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("support", SUPPORT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index f589112d5e..6640090910 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -51,6 +51,7 @@ typedef enum ParseExprKind EXPR_KIND_WINDOW_FRAME_RANGE, /* window frame clause with RANGE */ EXPR_KIND_WINDOW_FRAME_ROWS, /* window frame clause with ROWS */ EXPR_KIND_WINDOW_FRAME_GROUPS, /* window frame clause with GROUPS */ + EXPR_KIND_RPR_DEFINE, /* DEFINE */ EXPR_KIND_SELECT_TARGET, /* SELECT target list item */ EXPR_KIND_INSERT_TARGET, /* INSERT target list item */ EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ -- 2.25.1 ----Next_Part(Sat_Sep__2_15_52_35_2023_273)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v5-0002-Row-pattern-recognition-patch-parse-analysis.patch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-24 15:28 Robert Haas <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Robert Haas @ 2024-06-24 15:28 UTC (permalink / raw) To: Alena Rybakina <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On Fri, Jun 21, 2024 at 6:52 PM Alena Rybakina <[email protected]> wrote: > It's hard to tell, but I think it might be one of the good places to apply transformation. Let me describe a brief conclusion on the two approaches. This explanation is somewhat difficult for me to follow. For example: > In the first approach, we definitely did not process the extra "OR" expressions in the first approach, since they were packaged as an Array. It could lead to the fact that less planning time would be spent on the optimizer. I don't know what the "first approach" refers to, or what processing the extra "OR" expressions means, or what it would mean to package OR expressions as an array. If you made them into an SAOP then you'd have an array *instead of* OR expressions, not OR expressions "packaged as an array" but even then, they'd still be processed somewhere, unless the patch was just wrong. I think you should try writing this summary again and see if you can make it a lot clearer and more precise. I'm suspicious based that we should actually be postponing the transformation even further. If, for example, the transformation is advantageous for index scans and disadvantageous for bitmap scans, or the other way around, then this approach can't help much: it either does the transformation and all scan types are affected, or it doesn't do it and no scan types are affected. But if you decided for each scan whether to transform the quals, then you could handle that. Against that, there might be increased planning cost. But, perhaps that could be avoided somehow. > What exactly is the strategy around OR-clauses with type differences? > If I'm reading the code correctly, the first loop requires an exact > opno match, which presumably implies that the constant-type elements > are of the same type. But then why does the second loop need to use > coerce_to_common_type? > > It needs to transform all similar constants to one type, because some constants of "OR" expressions can belong others, like the numeric and int types. Due to the fact that array structure demands that all types must be belonged to one type, so for this reason we applied this procedure. The alternative that should be considered is not combining things if the types don't match. If we're going to combine such things, we need to be absolutely certain that type conversion cannot fail. > I do not think this is acceptable. We should find a way to get the > right operator into the ScalarArrayOpExpr without translating the OID > back into a name and then back into an OID. > > I don’t really understand the reason why it’s better not to do this. Can you explain please? One reason is that it is extra work to convert things to a name and then back to an OID. It's got to be slower than using the OID you already have. The other reason is that it's error-prone. If somehow the second lookup doesn't produce the same OID as the first lookup, bad things will happen, possibly including security vulnerabilities. I see you've taken steps to avoid that, like nailing down the schema, and that's good, but it's not a good enough reason to do it like this. If we don't have a function that can construct the node we need with the OID rather than the name as an argument, we should invent one, not do this sort of thing. > Also, why is the array built with eval_const_expressions instead of > something like makeArrayResult? There should be no need for general > expression evaluation here if we are just dealing with constants. > > I'm not ready to answer this question right now, I need time to study the use of the makeArrayResult function in the optimizer. OK. An important consideration here is that eval_const_expressions() is prone to *fail* because it can call user-defined functions. We really don't want this optimization to cause planner failure (or queries to error out at any other stage, either). We also don't want to end up with any security problems, which is another possible danger when we call a function that can execute arbitrary code. It's better to keep it simple and only do things that we know are simple and safe, like assembling a bunch of datums that we already have into an array. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-24 16:08 Peter Geoghegan <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Peter Geoghegan @ 2024-06-24 16:08 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On Mon, Jun 24, 2024 at 11:28 AM Robert Haas <[email protected]> wrote: > > It needs to transform all similar constants to one type, because some constants of "OR" expressions can belong others, like the numeric and int types. Due to the fact that array structure demands that all types must be belonged to one type, so for this reason we applied this procedure. > > The alternative that should be considered is not combining things if > the types don't match. If we're going to combine such things, we need > to be absolutely certain that type conversion cannot fail. But what about cases like this: SELECT * FROM mytable WHERE columna = 1_000_000_000 or columna = 5_000_000_000; -- columna is int4 This is using two types, of course. 1_000_000_000 is int4, while 5_000_000_000 is bigint. If the transformation suddenly failed to work when a constant above INT_MAX was used for the first time, then I'd say that that's pretty surprising. That's what happens currently if you write the same query as "WHERE columna = any('{1_000_000_000,5_000_000_000}')", due to the way the coercion works. That seems less surprising to me, because the user is required to construct their own array, and users expect arrays to always have one element type. It would probably be okay to make the optimization not combine things/not apply when the user gratuitously mixes different syntaxes. For example, if a numeric constant was used, rather than an integer constant. Maybe it would be practical to do something with the B-Tree operator class for each of the types involved in the optimization. You could probably find a way for a SAOP to work against a "heterogeneously-typed array" while still getting B-Tree index scans -- provided the types all came from the same operator family. I'm assuming that the index has an index column whose input opclass was a member of that same family. That would necessitate changing the general definition of SAOP, and adding new code to nbtree that worked with that. But that seems doable. I was already thinking about doing something like this, to support index scans for "IS NOT DISTINCT FROM", or on constructs like "columna = 5 OR columna IS NULL". That is more or less a SAOP with two values, except that one of the values in the value NULL. I've already implemented "nbtree SAOPs where one of the elements is a NULL" for skip scan, which could be generalized to support these other cases. Admittedly I'm glossing over a lot of important details here. Does it just work for the default opclass for the type, or can we expect it to work with a non-default opclass when that's the salient opclass (the one used by our index)? I don't know what you'd do about stuff like that. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-24 17:29 Robert Haas <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Robert Haas @ 2024-06-24 17:29 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On Mon, Jun 24, 2024 at 12:09 PM Peter Geoghegan <[email protected]> wrote: > But what about cases like this: > > SELECT * FROM mytable WHERE columna = 1_000_000_000 or columna = > 5_000_000_000; -- columna is int4 > > This is using two types, of course. 1_000_000_000 is int4, while > 5_000_000_000 is bigint. If the transformation suddenly failed to work > when a constant above INT_MAX was used for the first time, then I'd > say that that's pretty surprising. That's what happens currently if > you write the same query as "WHERE columna = > any('{1_000_000_000,5_000_000_000}')", due to the way the coercion > works. That seems less surprising to me, because the user is required > to construct their own array, and users expect arrays to always have > one element type. I am not against handling this kind of case if we can do it, but it's more important that the patch doesn't cause gratuitous failures than that it handles more cases. > Maybe it would be practical to do something with the B-Tree operator > class for each of the types involved in the optimization. You could > probably find a way for a SAOP to work against a > "heterogeneously-typed array" while still getting B-Tree index scans > -- provided the types all came from the same operator family. I'm > assuming that the index has an index column whose input opclass was a > member of that same family. That would necessitate changing the > general definition of SAOP, and adding new code to nbtree that worked > with that. But that seems doable. I agree that something based on operator families might be viable. Why would that require changing the definition of an SAOP? > Admittedly I'm glossing over a lot of important details here. Does it > just work for the default opclass for the type, or can we expect it to > work with a non-default opclass when that's the salient opclass (the > one used by our index)? I don't know what you'd do about stuff like > that. It seems to me that it just depends on the opclasses in the query. If the user says WHERE column op1 const1 AND column op2 const2 ...then if op1 and op2 are in the same operator family and if we can convert one of const1 and const2 to the type of the other without risk of failure, then we can rewrite this as an SAOP with whichever of the two operators pertains to the target type, e.g. column1 op1 ANY[const1,converted_const2] I don't think the default opclass matters here, or the index properties either. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-24 17:46 Peter Geoghegan <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 2 replies; 13+ messages in thread From: Peter Geoghegan @ 2024-06-24 17:46 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On Mon, Jun 24, 2024 at 1:29 PM Robert Haas <[email protected]> wrote: > I am not against handling this kind of case if we can do it, but it's > more important that the patch doesn't cause gratuitous failures than > that it handles more cases. I agree, with the proviso that "avoid gratuitous failures" should include cases where a query that got the optimization suddenly fails to get the optimization, due only to some very innocuous looking change. Such as a change from using a constant 1_000_000_000 to a constant 5_000_000_000 in the query text. That is a POLA violation. > > Maybe it would be practical to do something with the B-Tree operator > > class for each of the types involved in the optimization. You could > > probably find a way for a SAOP to work against a > > "heterogeneously-typed array" while still getting B-Tree index scans > > -- provided the types all came from the same operator family. I'm > > assuming that the index has an index column whose input opclass was a > > member of that same family. That would necessitate changing the > > general definition of SAOP, and adding new code to nbtree that worked > > with that. But that seems doable. > > I agree that something based on operator families might be viable. Why > would that require changing the definition of an SAOP? Maybe it doesn't. My point was only that the B-Tree code doesn't necessarily need to use just one rhs type for the same column input opclass. The definition of SOAP works (or could work) in basically the same way, provided the "OR condition" were provably disjunct. We could for example mix different operators for the same nbtree scan key (with some work in nbtutils.c), just as we could support "where mycol =5 OR mycol IS NULL" with much effort. BTW, did you know MySQL has long supported the latter? It has a <=> operator, which is basically a non-standard spelling of IS NOT DISTINCT FROM. Importantly, it is indexable, whereas right now Postgres doesn't support indexing IS NOT DISTINCT FROM. If you're interested in working on this problem within the scope of this patch, or some follow-up patch, I can take care of the nbtree side of things. > > Admittedly I'm glossing over a lot of important details here. Does it > > just work for the default opclass for the type, or can we expect it to > > work with a non-default opclass when that's the salient opclass (the > > one used by our index)? I don't know what you'd do about stuff like > > that. > > It seems to me that it just depends on the opclasses in the query. If > the user says > > WHERE column op1 const1 AND column op2 const2 > > ...then if op1 and op2 are in the same operator family and if we can > convert one of const1 and const2 to the type of the other without risk > of failure, then we can rewrite this as an SAOP with whichever of the > two operators pertains to the target type, e.g. > > column1 op1 ANY[const1,converted_const2] > > I don't think the default opclass matters here, or the index properties either. Okay, good. The docs do say "Another requirement for a multiple-data-type family is that any implicit or binary-coercion casts that are defined between data types included in the operator family must not change the associated sort ordering" [1]. There must be precedent for this sort of thing. Probably for merge joins. [1] https://www.postgresql.org/docs/devel/btree.html#BTREE-BEHAVIOR -- Peter Geoghegan ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-24 17:54 Peter Geoghegan <[email protected]> parent: Peter Geoghegan <[email protected]> 1 sibling, 0 replies; 13+ messages in thread From: Peter Geoghegan @ 2024-06-24 17:54 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On Mon, Jun 24, 2024 at 1:46 PM Peter Geoghegan <[email protected]> wrote: > BTW, did you know MySQL has long supported the latter? It has a <=> > operator, which is basically a non-standard spelling of IS NOT > DISTINCT FROM. Importantly, it is indexable, whereas right now > Postgres doesn't support indexing IS NOT DISTINCT FROM. If you're > interested in working on this problem within the scope of this patch, > or some follow-up patch, I can take care of the nbtree side of things. To be clear, I meant that we could easily support "where mycol = 5 OR mycol IS NULL" and have nbtree handle that efficiently, by making it a SAOP internally. Separately, we could also make IS NOT DISTINCT FROM indexable, though that probably wouldn't need any work in nbtree. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-24 18:28 Robert Haas <[email protected]> parent: Peter Geoghegan <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Robert Haas @ 2024-06-24 18:28 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On Mon, Jun 24, 2024 at 1:47 PM Peter Geoghegan <[email protected]> wrote: > I agree, with the proviso that "avoid gratuitous failures" should > include cases where a query that got the optimization suddenly fails > to get the optimization, due only to some very innocuous looking > change. Such as a change from using a constant 1_000_000_000 to a > constant 5_000_000_000 in the query text. That is a POLA violation. Nope, I don't agree with that at all. If you imagine that we can either have the optimization apply to one of those cases on the other, or on the other hand we can have some cases that outright fail, I think it's entirely clear that the former is better. > Maybe it doesn't. My point was only that the B-Tree code doesn't > necessarily need to use just one rhs type for the same column input > opclass. The definition of SOAP works (or could work) in basically the > same way, provided the "OR condition" were provably disjunct. We could > for example mix different operators for the same nbtree scan key (with > some work in nbtutils.c), just as we could support "where mycol =5 OR > mycol IS NULL" with much effort. > > BTW, did you know MySQL has long supported the latter? It has a <=> > operator, which is basically a non-standard spelling of IS NOT > DISTINCT FROM. Importantly, it is indexable, whereas right now > Postgres doesn't support indexing IS NOT DISTINCT FROM. If you're > interested in working on this problem within the scope of this patch, > or some follow-up patch, I can take care of the nbtree side of things. I was assuming this patch shouldn't be changing the way indexes work at all, just making use of the facilities that we have today. More could be done, but that might make it harder to get anything committed. Before we get too deep into arguing about hypotheticals, I don't think there's any problem here that we can't solve with the infrastructure we already have. For instance, consider this: robert.haas=# explain select * from foo where a in (1, 1000000000000000); QUERY PLAN ----------------------------------------------------------- Seq Scan on foo1 foo (cost=0.00..25.88 rows=13 width=36) Filter: (a = ANY ('{1,1000000000000000}'::bigint[])) (2 rows) I don't know exactly what's happening here, but it seems very similar to what we need to have happen for this patch to work. pg_typeof(1) is integer, and pg_typeof(1000000000000000) is bigint, and we're able to figure out that it's OK to put both of those in an array of a single type and without having any type conversion failures. If you replace 1000000000000000 with 2, then the array ends up being of type integer[] rather than type bigint[], so. clearly the system is able to reason its way through these kinds of scenarios already. It's even possible, in my mind at least, that the patch is already doing exactly the right things here. Even if it isn't, the problem doesn't seem to be fundamental, because if this example can work (and it does) then what the patch is trying to do should be workable, too. We just have to make sure we're plugging all the pieces properly together, and that we have comments adequately explain what is happening and test cases that verify it. My feeling is that the patch doesn't meet that standard today, but I think that just means it needs some more work. I'm not arguing we have to throw the whole thing out, or invent a lot of new infrastructure, or anything like that. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-24 19:02 Peter Geoghegan <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Peter Geoghegan @ 2024-06-24 19:02 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On Mon, Jun 24, 2024 at 2:28 PM Robert Haas <[email protected]> wrote: > On Mon, Jun 24, 2024 at 1:47 PM Peter Geoghegan <[email protected]> wrote: > > I agree, with the proviso that "avoid gratuitous failures" should > > include cases where a query that got the optimization suddenly fails > > to get the optimization, due only to some very innocuous looking > > change. Such as a change from using a constant 1_000_000_000 to a > > constant 5_000_000_000 in the query text. That is a POLA violation. > > Nope, I don't agree with that at all. If you imagine that we can > either have the optimization apply to one of those cases on the other, > or on the other hand we can have some cases that outright fail, I > think it's entirely clear that the former is better. I'm just saying that not having the optimization apply to a query very similar to one where it does apply is a POLA violation. That's another kind of failure, for all practical purposes. Weird performance cliffs like that are bad. It's very easy to imagine code that generates a query text, that at some point randomly and mysteriously gets a sequential scan. Or a much less efficient index scan. > I was assuming this patch shouldn't be changing the way indexes work > at all, just making use of the facilities that we have today. More > could be done, but that might make it harder to get anything > committed. I was just pointing out that there is currently no good way to make nbtree efficiently execute a qual "WHERE a = 5 OR a IS NULL", which is almost entirely (though not quite entirely) due to a lack of any way of expressing that idea through SQL, in a way that'll get pushed down to the index scan node. You can write "WHERE a = any('{5,NULL')", of course, but that doesn't treat NULL as just another array element to match against via an IS NULL qual (due to NULL semantics). Yeah, this is nonessential. But it's quite a nice optimization, and seems entirely doable within the framework of the patch. It would be a natural follow-up. All that I'd need on the nbtree side is to get an input scan key that directly embodies "WHERE mycol = 5 OR mycol IS NULL". That would probably just be a scan key with sk_flags "SK_SEARCHARRAY | SK_SEARCHNULL", that was otherwise identical to the current SK_SEARCHARRAY scan keys. Adopting the nbtree array index scan code to work with this would be straightforward. SK_SEARCHNULL scan keys basically already work like regular equality scan keys at execution time, so all that this optimization requires on the nbtree side is teaching _bt_advance_array_keys to treat NULL as a distinct array condition (evaluated as IS NULL, not as = NULL). > It's even possible, in my mind at least, that the patch is already > doing exactly the right things here. Even if it isn't, the problem > doesn't seem to be fundamental, because if this example can work (and > it does) then what the patch is trying to do should be workable, too. > We just have to make sure we're plugging all the pieces properly > together, and that we have comments adequately explain what is > happening and test cases that verify it. My feeling is that the patch > doesn't meet that standard today, but I think that just means it needs > some more work. I'm not arguing we have to throw the whole thing out, > or invent a lot of new infrastructure, or anything like that. I feel like my point about the potential for POLA violations is pretty much just common sense. I'm not particular about the exact mechanism by which we avoid it; only that we avoid it. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-25 23:13 Alena Rybakina <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Alena Rybakina @ 2024-06-25 23:13 UTC (permalink / raw) To: Robert Haas <[email protected]>; Peter Geoghegan <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On 24.06.2024 18:28, Robert Haas wrote: > On Fri, Jun 21, 2024 at 6:52 PM Alena Rybakina > <[email protected]> wrote: >> It's hard to tell, but I think it might be one of the good places to apply transformation. Let me describe a brief conclusion on the two approaches. > This explanation is somewhat difficult for me to follow. For example: > >> In the first approach, we definitely did not process the extra "OR" expressions in the first approach, since they were packaged as an Array. It could lead to the fact that less planning time would be spent on the optimizer. > I don't know what the "first approach" refers to, or what processing > the extra "OR" expressions means, or what it would mean to package OR > expressions as an array. If you made them into an SAOP then you'd have > an array*instead of* OR expressions, not OR expressions "packaged as > an array" but even then, they'd still be processed somewhere, unless > the patch was just wrong. > > I think you should try writing this summary again and see if you can > make it a lot clearer and more precise. > > I'm suspicious based that we should actually be postponing the > transformation even further. If, for example, the transformation is > advantageous for index scans and disadvantageous for bitmap scans, or > the other way around, then this approach can't help much: it either > does the transformation and all scan types are affected, or it doesn't > do it and no scan types are affected. But if you decided for each scan > whether to transform the quals, then you could handle that. Against > that, there might be increased planning cost. But, perhaps that could > be avoided somehow. Sorry, you are right and I'll try to explain more precisely. The firstapproach isthefirstpartof the patch,wherewemade "Or" expressions into an SAOPatan earlystageof plangeneration[0],the secondonewasthe one proposedby A.Korotkov[1]. So, when we made "OR" expressions into an SAOPat the post-parsing stage of the plan generation [0], we definitely did not process the redundantexpressions"OR" expressions there (for example,duplicates), since they were transformed to SAOP expression. Furthermore, the list of OR expressions can be significantly reduced, since constants belonging to the same predicate will already be converted into an SAOP expression. I assume this may reduce planning time, as I know several places in the optimizer where these lists of "OR" expressions are scanned several times. Also the selectivity for SAOP expressions is estimated better, which could lead to the generation of a more optimal plan, but, to be honest, this is just an observation from changes in regression tests and, in general, how the process of calculating the selectivity of a complex expression works. And I think it needs further consideration. 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 Speaking of the main disadvantages, we do not give the optimizer the opportunity to generate a plan using BitmapScan, which can lead to the generation of a suboptimal plan, but in the current approach the same thing happens [2]. And you mentioned about it before: On 24.06.2024 18:28, Robert Haas wrote: > I'm suspicious based that we should actually be postponing the > transformation even further. If, for example, the transformation is > advantageous for index scans and disadvantageous for bitmap scans, or > the other way around, then this approach can't help much: it either > does the transformation and all scan types are affected, or it doesn't > do it and no scan types are affected. But if you decided for each scan > whether to transform the quals, then you could handle that. Against > that, there might be increased planning cost. But, perhaps that could > be avoided somehow. Andrei mentioned the problem, which might be caused by the transformation on the later stage of optimization is updating references to expressions in RestrictInfo [3] lists, because they can be used in different parts during the formation of the query plan. As the practice of Self-join removal [4] has shown, this can be expensive, but feasible. By applying the transformation at the analysis stage [0], because no links were created, so we did not encounter such problems, so this approach was more suitable than the others. If some things were not clear enough, let me know. [0] https://www.postgresql.org/message-id/attachment/156971/v21-0001-Transform-OR-clauses-to-ANY-express... [1] https://www.postgresql.org/message-id/CAPpHfduah1PLzajBJFDmp7%2BMZuaWYpie2p%2BGsV0r03fcGghQ-g%40mail... [2] https://www.postgresql.org/message-id/7d5aed92-d4cc-4b76-8ae0-051d182c9eec%40postgrespro.ru [3] https://www.postgresql.org/message-id/6850c306-4e9d-40b7-8096-1f3c7d29cd9e%40postgrespro.ru [4] https://commitfest.postgresql.org/48/5043/ On 24.06.2024 18:28, Robert Haas wrote: > The alternative that should be considered is not combining things if > the types don't match. If we're going to combine such things, we need > to be absolutely certain that type conversion cannot fail. Peter,Robert,thanksforthe detaileddiscussion,I realizedthathereyou needto lookcarefullyatthe patch. In general, it comes out, I need to pay attention and highlight the cases where POLA violation occurs On 24.06.2024 18:28, Robert Haas wrote: > One reason is that it is extra work to convert things to a name and > then back to an OID. It's got to be slower than using the OID you > already have. > > The other reason is that it's error-prone. If somehow the second > lookup doesn't produce the same OID as the first lookup, bad things > will happen, possibly including security vulnerabilities. I see you've > taken steps to avoid that, like nailing down the schema, and that's > good, but it's not a good enough reason to do it like this. If we > don't have a function that can construct the node we need with the OID > rather than the name as an argument, we should invent one, not do this > sort of thing. I understood. I'll try to fix it. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-26 20:19 Robert Haas <[email protected]> parent: Alena Rybakina <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Robert Haas @ 2024-06-26 20:19 UTC (permalink / raw) To: Alena Rybakina <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> I think maybe replying to multiple emails with a single email is something you'd be better off doing less often. On Tue, Jun 25, 2024 at 7:14 PM Alena Rybakina <[email protected]> wrote: > Sorry, you are right and I'll try to explain more precisely. The first approach is the first part of the patch, where we made "Or" expressions into an SAOP at an early stage of plan generation [0], the second one was the one proposed by A.Korotkov [1]. [0] isn't doing anything "at an early stage of plan generation". It's changing something in *the parser*. The parser and planner are VERY different stages of query parsing, and it's really important to keep them separate mentally and in discussions. We should not be changing anything about the query in the parser, because that will, as Alexander also pointed out, change what gets stored if the user does something like CREATE VIEW whatever AS SELECT ...; and we should, for the most part, be storing the query as the user entered it, not some transformed version of it. Further, at the parser stage, we do not know the cost of anything, so we can only transform things when the transformed version is always - or practically always - going to be cheaper than the untransformed version. > On 24.06.2024 18:28, Robert Haas wrote: > Andrei mentioned the problem, which might be caused by the transformation on the later stage of optimization is updating references to expressions in RestrictInfo [3] lists, because they can be used in different parts during the formation of the query plan. As the practice of Self-join removal [4] has shown, this can be expensive, but feasible. By applying the transformation at the analysis stage [0], because no links were created, so we did not encounter such problems, so this approach was more suitable than the others. The link you provided for [3] doesn't show me exactly what code you're talking about, but I can see why mutating a RestrictInfo after creating it could be problematic. However, I'm not proposing that, and I don't think it's a good idea. Instead of mutating an existing data structure after it's been created, we want to get each data structure correct at the moment that it is created. What that means is that at each stage of processing, whenever we create a new in-memory data structure, we have an opportunity to make changes along the way. For instance, let's say we have a RestrictInfo and we are creating a Path, perhaps via create_index_path(). One argument to that function is a list of indexclauses. The indexclauses are derived from the RestrictInfo list associated with the RelOptInfo. We take some subset of those quals that are deemed to be indexable and we reorder them and maybe change a few things and we build this new list of indexclauses that is then passed to create_index_path(). The RelOptInfo's list of RestrictInfos is not changed -- only the new list of clauses derived from it is being built up here, without any mutation of the original structure. This is the kind of thing that this patch can and probably should do. Join removal is quite awkward, as you rightly point out, because we end up modifying existing data structures after they've been created, and that requires us to run around and fix up a bunch of stuff, and that can have bugs. Whenever possible, we don't want to do it that way. Instead, we want to pick points in the processing when we're anyway constructing some new structure and use that as an opportunity to do transformations when building the new structure that incorporate optimizations that make sense. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-27 19:56 Alena Rybakina <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Alena Rybakina @ 2024-06-27 19:56 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On 26.06.2024 23:19, Robert Haas wrote: > I think maybe replying to multiple emails with a single email is > something you'd be better off doing less often. Ok, I won't do this in the future. After thinkingit over,I realizedthatit turnedout to be somekindof messinthe end. > On Tue, Jun 25, 2024 at 7:14 PM Alena Rybakina > <[email protected]> wrote: >> Sorry, you are right and I'll try to explain more precisely. The first approach is the first part of the patch, where we made "Or" expressions into an SAOP at an early stage of plan generation [0], the second one was the one proposed by A.Korotkov [1]. > [0] isn't doing anything "at an early stage of plan generation". It's > changing something in *the parser*. The parser and planner are VERY > different stages of query parsing, and it's really important to keep > them separate mentally and in discussions. Thanks for the detailed explanation, I'm always glad to learn new things for myself) To be honest, I had an intuitive feeling that the transformation was called in the analyzer stage, but I wasn't sure about it, so I tried to summarize it. As for the fact that in general all this can be divided into two large stages, parsing and planner is a little new to me. I have reread the documentation [0] andI foundinformationaboutitthere. Beforethat, Iwas guidedbyinformationfromthe CarnegieMellonUniversitylecture andthe BruceMamjian report[1],whichwas wrongonmypart. By the way,it turnsout that the queryrewritingstagereferstoan independentstage,whichis locatedbetweenthe parserstageandtheplanner/optimizer. I found it from the documentation [2]. [0] https://www.postgresql.org/docs/current/planner-optimizer.html [1] https://momjian.us/main/writings/pgsql/optimizer.pdf [2] https://www.postgresql.org/docs/16/rule-system.html > We should not be changing > anything about the query in the parser, because that will, as > Alexander also pointed out, change what gets stored if the user does > something like CREATE VIEW whatever AS SELECT ...; and we should, for > the most part, be storing the query as the user entered it, not some > transformed version of it. Further, at the parser stage, we do not > know the cost of anything, so we can only transform things when the > transformed version is always - or practically always - going to be > cheaper than the untransformed version. Thank you, now it has become clear to me why it is so important to leave the transformation at the planner stage. >> On 24.06.2024 18:28, Robert Haas wrote: >> Andrei mentioned the problem, which might be caused by the transformation on the later stage of optimization is updating references to expressions in RestrictInfo [3] lists, because they can be used in different parts during the formation of the query plan. As the practice of Self-join removal [4] has shown, this can be expensive, but feasible. By applying the transformation at the analysis stage [0], because no links were created, so we did not encounter such problems, so this approach was more suitable than the others. > The link you provided for [3] doesn't show me exactly what code you're > talking about, but I can see why mutating a RestrictInfo after > creating it could be problematic. However, I'm not proposing that, and > I don't think it's a good idea. Instead of mutating an existing data > structure after it's been created, we want to get each data structure > correct at the moment that it is created. What that means is that at > each stage of processing, whenever we create a new in-memory data > structure, we have an opportunity to make changes along the way. > > For instance, let's say we have a RestrictInfo and we are creating a > Path, perhaps via create_index_path(). One argument to that function > is a list of indexclauses. The indexclauses are derived from the > RestrictInfo list associated with the RelOptInfo. We take some subset > of those quals that are deemed to be indexable and we reorder them and > maybe change a few things and we build this new list of indexclauses > that is then passed to create_index_path(). The RelOptInfo's list of > RestrictInfos is not changed -- only the new list of clauses derived > from it is being built up here, without any mutation of the original > structure. > > This is the kind of thing that this patch can and probably should do. > Join removal is quite awkward, as you rightly point out, because we > end up modifying existing data structures after they've been created, > and that requires us to run around and fix up a bunch of stuff, and > that can have bugs. Whenever possible, we don't want to do it that > way. Instead, we want to pick points in the processing when we're > anyway constructing some new structure and use that as an opportunity > to do transformations when building the new structure that incorporate > optimizations that make sense. Thanks for the idea! I hadn't thought in this direction before, but it really might just work and solve all our original problems. By the way, I saw that the optimizer is smart enough to eliminate duplicates. Below I have conducted a couple of examples where he decides for himself which expression is more profitable for him to leave. Wejustneedto addthistransformation,andthe optimizerwillchoosethe appropriateone) alena@postgres=# explain select * from x where (a = 1 or a = 2) and a in (1,2); QUERY PLAN -------------------------------------------------------------------- Index Only Scan using a_idx on x (cost=0.28..8.61 rows=1 width=4) Index Cond: (a = ANY ('{1,2}'::integer[])) (2 rows) alena@postgres=# explain select * from x where a < 3 and (a = 1 or a = 2) and a = ANY(ARRAY[1,2]); QUERY PLAN -------------------------------------------------------------------- Index Only Scan using a_idx on x (cost=0.28..8.60 rows=1 width=4) Index Cond: ((a < 3) AND (a = ANY ('{1,2}'::integer[]))) (2 rows) ItworksforKorotkov's casetoo,asIseeit: alena@postgres=# create table test as (select (random()*10)::int x, (random()*1000) y from generate_series(1,1000000) i); create index test_x_1_y on test (y) where x = 1; create index test_x_2_y on test (y) where x = 2; vacuum analyze test; SELECT 1000000 CREATE INDEX CREATE INDEX VACUUM alena@postgres=# explain select * from test where (x = 1 or x = 2) and y = 100 and x in (1,2); QUERY PLAN -------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test (cost=8.60..12.62 rows=1 width=12) Recheck Cond: (((y = '100'::double precision) AND (x = 1)) OR ((y = '100'::double precision) AND (x = 2))) -> BitmapOr (cost=8.60..8.60 rows=1 width=0) -> Bitmap Index Scan on test_x_1_y (cost=0.00..4.30 rows=1 width=0) Index Cond: (y = '100'::double precision) -> Bitmap Index Scan on test_x_2_y (cost=0.00..4.30 rows=1 width=0) Index Cond: (y = '100'::double precision) (7 rows) I noticed that the distribute_quals_to_rels function launches at the stage when it is necessary to generate RestrictInfo lists for relation - it might be a suitable place for applying transformation. So, instead of completely replacing the list, we should form a complex BoolExpr structure with the "AND" operator, which should contain two expressions, where one of them is BoolExpr with the "OR" operator and the second is ScalarArrayOpExpr. Tobe honest,I've alreadystartedwritingcodetodothis,butI'm facedwitha misunderstandingof howto correctlycreatea conditionfor"OR"expressionsthatare notsubjectto transformation.For example,the expressions b=1in the query below: alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN ---------------------------------------------------------------------------------- Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows) I see that two expressions have remained unchanged and it only works for "AND" binary operations. But I think it might be worth applying this together, where does the optimizer generate indexes (build_paths_for_OR function)? -- Regards, Alena Rybakina Postgres Professional:http://www.postgrespro.com The Russian Postgres Company ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-06-27 20:06 Alena Rybakina <[email protected]> parent: Alena Rybakina <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Alena Rybakina @ 2024-06-27 20:06 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; pgsql-hackers; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> Tobe honest,I've alreadystartedwritingcodetodothis,butI'm facedwitha misunderstandingof howto correctlycreatea conditionfor"OR"expressionsthatare notsubjectto transformation. > > For example,the expressions b=1in the query below: > > alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = > ANY(ARRAY[5,4])) or (b=1); QUERY PLAN > ---------------------------------------------------------------------------------- > Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR > (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows) > > I see that two expressions have remained unchanged and it only works > for "AND" binary operations. > > But I think it might be worth applying this together, where does the > optimizer generate indexes (build_paths_for_OR function)? > Sorry, it works) I needed to create one more index for b column. Just in case, I gave an example of a complete case, otherwise it might not be entirely clear: alena@postgres=# create table x (a int, b int); CREATE TABLE alena@postgres=# create index a_idx on x(a); insert into x select id,id from generate_series(1, 5000) as id; CREATE INDEX INSERT 0 5000 alena@postgres=# analyze; ANALYZE alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN ---------------------------------------------------------------------------------- Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows) alena@postgres=# create index b_idx on x(b); CREATE INDEX alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN -------------------------------------------------------------------------- Bitmap Heap Scan on x (cost=12.87..21.68 rows=1 width=8) Recheck Cond: ((a = ANY ('{5,4}'::integer[])) OR (b = 1)) -> BitmapOr (cost=12.87..12.87 rows=3 width=0) -> Bitmap Index Scan on a_idx (cost=0.00..8.58 rows=2 width=0) Index Cond: (a = ANY ('{5,4}'::integer[])) -> Bitmap Index Scan on b_idx (cost=0.00..4.29 rows=1 width=0) Index Cond: (b = 1) (7 rows) -- Regards, Alena Rybakina Postgres Professional:http://www.postgrespro.com The Russian Postgres Company ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: POC, WIP: OR-clause support for indexes @ 2024-07-10 23:30 Alena Rybakina <[email protected]> parent: Alena Rybakina <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Alena Rybakina @ 2024-07-10 23:30 UTC (permalink / raw) To: pgsql-hackers; +Cc: Robert Haas <[email protected]>; Peter Geoghegan <[email protected]>; Alexander Korotkov <[email protected]>; jian he <[email protected]>; Marcos Pegoraro <[email protected]>; [email protected]; Andrei Lepikhov <[email protected]>; Tomas Vondra <[email protected]>; Peter Eisentraut <[email protected]>; Ranier Vilela <[email protected]> On 27.06.2024 23:06, Alena Rybakina wrote: > Tobe honest,I've alreadystartedwritingcodetodothis,butI'm facedwitha > misunderstandingof howto correctlycreatea > conditionfor"OR"expressionsthatare notsubjectto transformation. >> >> For example,the expressions b=1in the query below: >> >> alena@postgres=# explain select * from x where ( (a =5 or a=4) and a >> = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN >> ---------------------------------------------------------------------------------- >> Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) >> OR (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows) >> >> I see that two expressions have remained unchanged and it only works >> for "AND" binary operations. >> >> But I think it might be worth applying this together, where does the >> optimizer generate indexes (build_paths_for_OR function)? >> Iimplementedsuchcode,butatthe analysisstageinplanner,anditwasn'tfullyreadyyet,butIwas ableto drawsomeimportantconclusions.Firstof all,Ifacedtheproblemof the inequalityof the numberof columnsinthe expressionwiththe requiredone,atleastsomeextracolumnappeared,judgingby the crust.Ihaven'tfullyrealizedityet andhaven'tfixedit. #0 __pthread_kill_implementation (no_tid=0, signo=6, threadid=134300960061248) at ./nptl/pthread_kill.c:44 #1 __pthread_kill_internal (signo=6, threadid=134300960061248) at ./nptl/pthread_kill.c:78 #2 __GI___pthread_kill (threadid=134300960061248, signo=signo@entry=6) at ./nptl/pthread_kill.c:89 #3 0x00007a2560042476 in __GI_raise (sig=sig@entry=6) at ../sysdeps/posix/raise.c:26 #4 0x00007a25600287f3 in __GI_abort () at ./stdlib/abort.c:79 #5 0x00005573f9df62a8 in ExceptionalCondition ( conditionName=0x5573f9fec4c8 "AttrNumberIsForUserDefinedAttr(list_attnums[i]) || !bms_is_member(attnum, clauses_attnums)", fileName=0x5573f9fec11c "dependencies.c", lineNumber=1525) at assert.c:66 #6 0x00005573f9b8b85f in dependencies_clauselist_selectivity (root=0x5573fad534e8, clauses=0x5573fad0b2d8, varRelid=0, jointype=JOIN_INNER, sjinfo=0x0, rel=0x5573fad54b38, estimatedclauses=0x7ffe2e43f178) at dependencies.c:1525 #7 0x00005573f9b8fed9 in statext_clauselist_selectivity (root=0x5573fad534e8, clauses=0x5573fad0b2d8, varRelid=0, jointype=JOIN_INNER, sjinfo=0x0, rel=0x5573fad54b38, estimatedclauses=0x7ffe2e43f178, is_or=false) at extended_stats.c:2035 --Type <RET> for more, q to quit, c to continue without paging-- #8 0x00005573f9a57f88 in clauselist_selectivity_ext (root=0x5573fad534e8, clauses=0x5573fad0b2d8, varRelid=0, jointype=JOIN_INNER, sjinfo=0x0, use_extended_stats=true) at clausesel.c:153 #9 0x00005573f9a57e30 in clauselist_selectivity (root=0x5573fad534e8, clauses=0x5573fad0b2d8, varRelid=0, jointype=JOIN_INNER, sjinfo=0x0) at clausesel.c:106 #10 0x00005573f9a62e03 in set_baserel_size_estimates (root=0x5573fad534e8, rel=0x5573fad54b38) at costsize.c:5247 #11 0x00005573f9a51aa5 in set_plain_rel_size (root=0x5573fad534e8, rel=0x5573fad54b38, rte=0x5573fad0ec58) at allpaths.c:581 #12 0x00005573f9a516ce in set_rel_size (root=0x5573fad534e8, rel=0x5573fad54b38, rti=1, rte=0x5573fad0ec58) at allpaths.c:411 #13 0x00005573f9a514c7 in set_base_rel_sizes (root=0x5573fad534e8) at allpaths.c:322 #14 0x00005573f9a5119d in make_one_rel (root=0x5573fad534e8, joinlist=0x5573fad0adf8) at allpaths.c:183 #15 0x00005573f9a94d45 in query_planner (root=0x5573fad534e8, qp_callback=0x5573f9a9b59e <standard_qp_callback>, qp_extra=0x7ffe2e43f540) at planmain.c:280 #16 0x00005573f9a977a8 in grouping_planner (root=0x5573fad534e8, tuple_fraction=0, setops=0x0) at planner.c:1520 #17 0x00005573f9a96e47 in subquery_planner (glob=0x5573fad533d8, parse=0x5573fad0ea48, parent_root=0x0, hasRecursion=false, tuple_fraction=0, setops=0x0) at planner.c:1089 #18 0x00005573f9a954aa in standard_planner (parse=0x5573fad0ea48, query_string=0x5573fad8b3b0 "explain analyze SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = '1'", cursorOptions=2048, boundParams=0x0) at planner.c:415 #19 0x00005573f9a951d4 in planner (parse=0x5573fad0ea48, --Type <RET> for more, q to quit, c to continue without paging-- query_string=0x5573fad8b3b0 "explain analyze SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = '1'", cursorOptions=2048, boundParams=0x0) at planner.c:282 #20 0x00005573f9bf4e2e in pg_plan_query (querytree=0x5573fad0ea48, query_string=0x5573fad8b3b0 "explain analyze SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = '1'", cursorOptions=2048, boundParams=0x0) at postgres.c:904 #21 0x00005573f98613e7 in standard_ExplainOneQuery (query=0x5573fad0ea48, cursorOptions=2048, into=0x0, es=0x5573fad57da0, queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = '1'", params=0x0, queryEnv=0x0) at explain.c:489 #22 0x00005573f9861205 in ExplainOneQuery (query=0x5573fad0ea48, cursorOptions=2048, into=0x0, es=0x5573fad57da0, queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = '1'", params=0x0, queryEnv=0x0) at explain.c:445 #23 0x00005573f9860e35 in ExplainQuery (pstate=0x5573fad57c90, stmt=0x5573fad8b5a0, params=0x0, dest=0x5573fad57c00) at explain.c:341 #24 0x00005573f9bff3a8 in standard_ProcessUtility (pstmt=0x5573fad8b490, queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = '1'", readOnlyTree=false, context=PROCESS_UTILITY_QUERY, params=0x0, queryEnv=0x0, dest=0x5573fad57c00, qc=0x7ffe2e43fcd0) at utility.c:863 #25 0x00005573f9bfe91a in ProcessUtility (pstmt=0x5573fad8b490, queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = '1'", readOnlyTree=false, context=PROCESS_UTILITY_QUERY, params=0x0, --Type <RET> for more, q to quit, c to continue without paging-- queryEnv=0x0, dest=0x5573fad57c00, qc=0x7ffe2e43fcd0) at utility.c:523 #26 0x00005573f9bfd195 in PortalRunUtility (portal=0x5573fac6bcf0, pstmt=0x5573fad8b490, isTopLevel=false, setHoldSnapshot=true, dest=0x5573fad57c00, qc=0x7ffe2e43fcd0) at pquery.c:1158 #27 0x00005573f9bfced2 in FillPortalStore (portal=0x5573fac6bcf0, isTopLevel=false) at pquery.c:1031 #28 0x00005573f9bfd778 in PortalRunFetch (portal=0x5573fac6bcf0, fdirection=FETCH_FORWARD, count=10, dest=0x5573fa1d6880 <spi_printtupDR>) at pquery.c:1442 #29 0x00005573f9992675 in _SPI_cursor_operation (portal=0x5573fac6bcf0, direction=FETCH_FORWARD, count=10, dest=0x5573fa1d6880 <spi_printtupDR>) at spi.c:3019 #30 0x00005573f9990849 in SPI_cursor_fetch (portal=0x5573fac6bcf0, forward=true, count=10) at spi.c:1805 #31 0x00007a25603e0aa5 in exec_for_query (estate=0x7ffe2e440200, stmt=0x5573fad067c8, portal=0x5573fac6bcf0, prefetch_ok=true) at pl_exec.c:5889 #32 0x00007a25603de728 in exec_stmt_dynfors (estate=0x7ffe2e440200, stmt=0x5573fad067c8) at pl_exec.c:4647 #33 0x00007a25603d8b1c in exec_stmts (estate=0x7ffe2e440200, stmts=0x5573fad06ec8) at pl_exec.c:2100 #34 0x00007a25603d8697 in exec_stmt_block (estate=0x7ffe2e440200, block=0x5573fad06f18) at pl_exec.c:1943 #35 0x00007a25603d7d9e in exec_toplevel_block (estate=0x7ffe2e440200, block=0x5573fad06f18) at pl_exec.c:1634 #36 0x00007a25603d5a2e in plpgsql_exec_function (func=0x5573fac2c1e0, fcinfo=0x5573fad2af60, simple_eval_estate=0x0, simple_eval_resowner=0x0, procedure_resowner=0x0, atomic=true) at pl_exec.c:623 #37 0x00007a25603f277f in plpgsql_call_handler (fcinfo=0x5573fad2af60) at pl_handler.c:277 #38 0x00005573f993589a in ExecMakeTableFunctionResult (setexpr=0x5573facfd8c8, econtext=0x5573facfd798, --Type <RET> for more, q to quit, c to continue without paging-- argContext=0x5573fad2ae60, expectedDesc=0x5573facfe130, randomAccess=false) at execSRF.c:234 #39 0x00005573f995299c in FunctionNext (node=0x5573facfd588) at nodeFunctionscan.c:94 #40 0x00005573f993735f in ExecScanFetch (node=0x5573facfd588, accessMtd=0x5573f99528e6 <FunctionNext>, recheckMtd=0x5573f9952ced <FunctionRecheck>) at execScan.c:131 #41 0x00005573f99373d8 in ExecScan (node=0x5573facfd588, accessMtd=0x5573f99528e6 <FunctionNext>, recheckMtd=0x5573f9952ced <FunctionRecheck>) at execScan.c:180 #42 0x00005573f9952d46 in ExecFunctionScan (pstate=0x5573facfd588) at nodeFunctionscan.c:269 #43 0x00005573f9932c7f in ExecProcNodeFirst (node=0x5573facfd588) at execProcnode.c:464 #44 0x00005573f9925df5 in ExecProcNode (node=0x5573facfd588) at ../../../src/include/executor/executor.h:274 #45 0x00005573f9928bf9 in ExecutePlan (estate=0x5573facfd360, planstate=0x5573facfd588, use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true, numberTuples=0, direction=ForwardScanDirection, dest=0x5573fad8f6e0, execute_once=true) at execMain.c:1646 #46 0x00005573f992653d in standard_ExecutorRun (queryDesc=0x5573fad87f70, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:363 #47 0x00005573f9926316 in ExecutorRun (queryDesc=0x5573fad87f70, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:304 #48 0x00005573f9bfcb7d in PortalRunSelect (portal=0x5573fac6bbe0, forward=true, count=0, dest=0x5573fad8f6e0) at pquery.c:924 #49 0x00005573f9bfc7a5 in PortalRun (portal=0x5573fac6bbe0, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x5573fad8f6e0, altdest=0x5573fad8f6e0, qc=0x7ffe2e440a60) at pquery.c:768 #50 0x00005573f9bf5512 in exec_simple_query ( --Type <RET> for more, q to quit, c to continue without paging-- query_string=0x5573fabea030 "SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');") at postgres.c:1274 #51 0x00005573f9bfa5b7 in PostgresMain (dbname=0x5573fab52240 "regression", username=0x5573fac27c98 "alena") at postgres.c:4680 #52 0x00005573f9bf137e in BackendMain (startup_data=0x7ffe2e440ce4 "", startup_data_len=4) at backend_startup.c:105 #53 0x00005573f9b06852 in postmaster_child_launch (child_type=B_BACKEND, startup_data=0x7ffe2e440ce4 "", startup_data_len=4, client_sock=0x7ffe2e440d30) at launch_backend.c:265 #54 0x00005573f9b0cd66 in BackendStartup (client_sock=0x7ffe2e440d30) at postmaster.c:3593 #55 0x00005573f9b09db1 in ServerLoop () at postmaster.c:1674 #56 0x00005573f9b09678 in PostmasterMain (argc=8, argv=0x5573fab500d0) at postmaster.c:1372 #57 0x00005573f99b5f79 in main (argc=8, argv=0x5573fab500d0) at main.c:197 Secondly,Isawdiffchangesinqueriesthatshowedcaseswherethe optimizerdid noteliminateoneofthe redundantexpressionsandprocessedbothof them.Thisindicatestheproblemthatthe optimizerhas notlearnedhow to handleitinallcases.IthinkI'll needtoaddsomecodetohandleit. EXPLAIN (COSTS OFF) SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on tenk1 - Recheck Cond: (((hundred = 42) AND ((thousand = ANY ('{42,99}'::integer[])) OR (tenthous < 2))) OR (thousand = 41)) + Recheck Cond: (((((thousand = 42) AND (thousand = ANY ('{42,99}'::integer[]))) OR ((thousand = 99) AND (thousand = ANY ('{42,99}'::integer[])))) OR (tenthous < 2)) OR (thousand = 41)) + Filter: (((hundred = 42) AND ((((thousand = 42) OR (thousand = 99)) AND (thousand = ANY ('{42,99}'::integer[]))) OR (tenthous < 2))) OR (thousand = 41)) -> BitmapOr - -> BitmapAnd - -> Bitmap Index Scan on tenk1_hundred - Index Cond: (hundred = 42) + -> BitmapOr -> BitmapOr -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand = ANY ('{42,99}'::integer[])) + Index Cond: ((thousand = 42) AND (thousand = ANY ('{42,99}'::integer[]))) -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (tenthous < 2) + Index Cond: ((thousand = 99) AND (thousand = ANY ('{42,99}'::integer[]))) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (tenthous < 2) -> Bitmap Index Scan on tenk1_thous_tenthous Index Cond: (thousand = 41) -(14 rows) +(15 rows) SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; @@ -1986,20 +1987,21 @@ EXPLAIN (COSTS OFF) SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on tenk1 - Recheck Cond: ((hundred = 42) AND ((thousand = ANY ('{41,42}'::integer[])) OR ((thousand = 99) AND (tenthous = 2)))) - -> BitmapAnd - -> Bitmap Index Scan on tenk1_hundred - Index Cond: (hundred = 42) + Recheck Cond: ((((thousand = 42) AND (thousand = ANY ('{41,42}'::integer[]))) OR ((thousand = 41) AND (thousand = ANY ('{41,42}'::integer[])))) OR ((thousand = 99) AND (tenthous = 2))) + Filter: (hundred = 42) + -> BitmapOr -> BitmapOr -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand = ANY ('{41,42}'::integer[])) + Index Cond: ((thousand = 42) AND (thousand = ANY ('{41,42}'::integer[]))) -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: ((thousand = 99) AND (tenthous = 2)) -(11 rows) + Index Cond: ((thousand = 41) AND (thousand = ANY ('{41,42}'::integer[]))) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: ((thousand = 99) AND (tenthous = 2)) +(12 rows) SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); diff -U3 /home/alena/postgrespro5/src/test/regress/expected/inherit.out /home/alena/postgrespro5/src/test/regress/results/inherit.out --- /home/alena/postgrespro5/src/test/regress/expected/inherit.out 2024-06-20 12:28:52.324011724 +0300 +++ /home/alena/postgrespro5/src/test/regress/results/inherit.out 2024-07-11 02:00:55.404006843 +0300 @@ -2126,7 +2126,7 @@ QUERY PLAN --------------------------------------------------------------------------------- Seq Scan on part_ab_cd list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) + Filter: (((a)::text = ANY ('{NULL,cd}'::text[])) OR ((a)::text = 'ab'::text)) (2 rows) explain (costs off) select * from list_parted where a = 'ab'; diff -U3 /home/alena/postgrespro5/src/test/regress/expected/join.out /home/alena/postgrespro5/src/test/regress/results/join.out --- /home/alena/postgrespro5/src/test/regress/expected/join.out 2024-06-28 11:05:44.304135987 +0300 +++ /home/alena/postgrespro5/src/test/regress/results/join.out 2024-07-11 02:00:58.152006921 +0300 @@ -4210,10 +4210,17 @@ 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 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) + Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) + -> 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 = ANY ('{3,7}'::integer[])) -> Bitmap Heap Scan on tenk1 b Recheck Cond: ((unique1 = 2) OR (hundred = 4)) -> BitmapOr @@ -4221,25 +4228,24 @@ 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 = ANY ('{3,7}'::integer[])) -(17 rows) +(16 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 ------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) + Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) + -> 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 = ANY ('{3,7}'::integer[])) -> Bitmap Heap Scan on tenk1 b Recheck Cond: ((unique1 = 2) OR (hundred = 4)) -> BitmapOr @@ -4247,37 +4253,29 @@ 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 = ANY ('{3,7}'::integer[])) -(17 rows) +(16 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 ---------------------------------------------------------------------------------------------------------------------------------------------------------------- + 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))) + Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)) OR (a.unique1 < 20) OR (a.unique1 = 3)) -> 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[]))) + Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])) OR (unique1 < 20) OR (unique1 = 3)) -> 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[])) + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 < 20) + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = 3) (15 rows) Thirdly,Ihaveevidencethatthismayaffecttheunderestimationof power.I'll lookinto thisin detaillater. diff -U3 /home/alena/postgrespro5/src/test/regress/expected/stats_ext.out /home/alena/postgrespro5/src/test/regress/results/stats_ext.out --- /home/alena/postgrespro5/src/test/regress/expected/stats_ext.out 2024-06-28 11:05:44.304135987 +0300 +++ /home/alena/postgrespro5/src/test/regress/results/stats_ext.out 2024-07-11 02:01:06.596007159 +0300 @@ -1156,19 +1156,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1'''); estimated | actual -----------+-------- - 2 | 100 + 1 | 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 -----------+-------- - 4 | 100 + 1 | 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 -----------+-------- - 8 | 200 + 1 | 200 (1 row) -- OR clauses referencing different attributes @@ -1322,19 +1322,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1'''); estimated | actual -----------+-------- - 100 | 100 + 2 | 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 -----------+-------- - 100 | 100 + 2 | 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 -----------+-------- - 200 | 200 + 8 | 200 (1 row) -- Regards, Alena Rybakina Postgres Professional:http://www.postgrespro.com The Russian Postgres Company 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 Attachments: [text/plain] or.diff.no-cfbot (39.4K, ../../[email protected]/3-or.diff.no-cfbot) download | inline diff: 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 ^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-07-10 23:30 UTC | newest] Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-09-02 06:32 [PATCH v5 1/7] Row pattern recognition patch for raw parser. Tatsuo Ishii <[email protected]> 2024-06-24 15:28 Re: POC, WIP: OR-clause support for indexes Robert Haas <[email protected]> 2024-06-24 16:08 ` Re: POC, WIP: OR-clause support for indexes Peter Geoghegan <[email protected]> 2024-06-24 17:29 ` Re: POC, WIP: OR-clause support for indexes Robert Haas <[email protected]> 2024-06-24 17:46 ` Re: POC, WIP: OR-clause support for indexes Peter Geoghegan <[email protected]> 2024-06-24 17:54 ` Re: POC, WIP: OR-clause support for indexes Peter Geoghegan <[email protected]> 2024-06-24 18:28 ` Re: POC, WIP: OR-clause support for indexes Robert Haas <[email protected]> 2024-06-24 19:02 ` Re: POC, WIP: OR-clause support for indexes Peter Geoghegan <[email protected]> 2024-06-25 23:13 ` Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[email protected]> 2024-06-26 20:19 ` Re: POC, WIP: OR-clause support for indexes Robert Haas <[email protected]> 2024-06-27 19:56 ` Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[email protected]> 2024-06-27 20:06 ` Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[email protected]> 2024-07-10 23:30 ` Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[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