public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v1 3/4] Add a new MODE_SINGLE_QUERY to the core parser and use it in pg_parse_query.
4+ messages / 3 participants
[nested] [flat]
* [PATCH v1 3/4] Add a new MODE_SINGLE_QUERY to the core parser and use it in pg_parse_query.
@ 2021-04-21 17:33 Julien Rouhaud <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Julien Rouhaud @ 2021-04-21 17:33 UTC (permalink / raw)
If a third-party module provides a parser_hook, pg_parse_query() switches to
single-query parsing so multi-query commands using different grammar can work
properly. If the third-party module supports the full set of SQL we support,
or want to prevent fallback on the core parser, it can ignore the
MODE_SINGLE_QUERY mode and parse the full query string. In that case they must
return a List with more than one RawStmt or a single RawStmt with a 0 length to
stop the parsing phase, or raise an ERROR.
Otherwise, plugins should parse a single query only and always return a List
containing a single RawStmt with a properly set length (possibly 0 if it was a
single query without end of query delimiter). If the command is valid but
doesn't contain any statements (e.g. a single semi-colon), a single RawStmt
with a NULL stmt field should be returned, containing the consumed query string
length so we can move to the next command in a single pass rather than 1 byte
at a time.
Also, third-party modules can choose to ignore some or all of parsing error if
they want to implement only subset of postgres suppoted syntax, or even a
totally different syntax, and fall-back on core grammar for unhandled case. In
thase case, they should set the error flag to true. The returned List will be
ignored and the same offset of the input string will be parsed using the core
parser.
Finally, note that third-party plugins that wants to fallback on other grammar
should first try to call a previous parser hook if any before setting the error
switch and returning.
---
.../pg_stat_statements/pg_stat_statements.c | 3 +-
src/backend/commands/tablecmds.c | 2 +-
src/backend/executor/spi.c | 4 +-
src/backend/parser/gram.y | 27 ++++
src/backend/parser/parse_type.c | 2 +-
src/backend/parser/parser.c | 7 +-
src/backend/parser/scan.l | 13 +-
src/backend/tcop/postgres.c | 131 ++++++++++++++++--
src/include/parser/parser.h | 5 +-
src/include/parser/scanner.h | 3 +-
src/include/tcop/tcopprot.h | 3 +-
src/pl/plpgsql/src/pl_gram.y | 2 +-
src/pl/plpgsql/src/pl_scanner.c | 2 +-
13 files changed, 179 insertions(+), 25 deletions(-)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index f42f07622e..7c911ef58d 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2711,7 +2711,8 @@ fill_in_constant_lengths(JumbleState *jstate, const char *query,
yyscanner = scanner_init(query,
&yyextra,
&ScanKeywords,
- ScanKeywordTokens);
+ ScanKeywordTokens,
+ 0);
/* we don't want to re-emit any escape string warnings */
yyextra.escape_string_warning = false;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d9ba87a2a3..cc9c86778c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12602,7 +12602,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
* parse_analyze() or the rewriter, but instead we need to pass them
* through parse_utilcmd.c to make them ready for execution.
*/
- raw_parsetree_list = raw_parser(cmd, RAW_PARSE_DEFAULT);
+ raw_parsetree_list = raw_parser(cmd, RAW_PARSE_DEFAULT, 0);
querytree_list = NIL;
foreach(list_item, raw_parsetree_list)
{
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 00aa78ea53..e456172fef 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -2121,7 +2121,7 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan)
/*
* Parse the request string into a list of raw parse trees.
*/
- raw_parsetree_list = raw_parser(src, plan->parse_mode);
+ raw_parsetree_list = raw_parser(src, plan->parse_mode, 0);
/*
* Do parse analysis and rule rewrite for each raw parsetree, storing the
@@ -2229,7 +2229,7 @@ _SPI_prepare_oneshot_plan(const char *src, SPIPlanPtr plan)
/*
* Parse the request string into a list of raw parse trees.
*/
- raw_parsetree_list = raw_parser(src, plan->parse_mode);
+ raw_parsetree_list = raw_parser(src, plan->parse_mode, 0);
/*
* Construct plancache entries, but don't do parse analysis yet.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b4ab4014c8..9733b30529 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -753,6 +753,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%token MODE_PLPGSQL_ASSIGN1
%token MODE_PLPGSQL_ASSIGN2
%token MODE_PLPGSQL_ASSIGN3
+%token MODE_SINGLE_QUERY
/* Precedence: lowest to highest */
@@ -858,6 +859,32 @@ parse_toplevel:
pg_yyget_extra(yyscanner)->parsetree =
list_make1(makeRawStmt((Node *) n, 0));
}
+ | MODE_SINGLE_QUERY toplevel_stmt ';'
+ {
+ RawStmt *raw = makeRawStmt($2, 0);
+ updateRawStmtEnd(raw, @3 + 1);
+ /* NOTE: we can return a raw statement containing a NULL stmt.
+ * This is done to allow pg_parse_query to ignore that part of
+ * the input string and move to the next command.
+ */
+ pg_yyget_extra(yyscanner)->parsetree = list_make1(raw);
+ YYACCEPT;
+ }
+ /*
+ * We need to explicitly look for EOF to parse non-semicolon
+ * terminated statements in single query mode, as we could
+ * otherwise successfully parse the beginning of an otherwise
+ * invalid query.
+ */
+ | MODE_SINGLE_QUERY toplevel_stmt YYEOF
+ {
+ /* NOTE: we can return a raw statement containing a NULL stmt.
+ * This is done to allow pg_parse_query to ignore that part of
+ * the input string.
+ */
+ pg_yyget_extra(yyscanner)->parsetree = list_make1(makeRawStmt($2, 0));
+ YYACCEPT;
+ }
;
/*
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
index abe131ebeb..e9a7b5d62a 100644
--- a/src/backend/parser/parse_type.c
+++ b/src/backend/parser/parse_type.c
@@ -746,7 +746,7 @@ typeStringToTypeName(const char *str)
ptserrcontext.previous = error_context_stack;
error_context_stack = &ptserrcontext;
- raw_parsetree_list = raw_parser(str, RAW_PARSE_TYPE_NAME);
+ raw_parsetree_list = raw_parser(str, RAW_PARSE_TYPE_NAME, 0);
error_context_stack = ptserrcontext.previous;
diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c
index 875de7ba28..7297733168 100644
--- a/src/backend/parser/parser.c
+++ b/src/backend/parser/parser.c
@@ -39,7 +39,7 @@ static char *str_udeescape(const char *str, char escape,
* list have the form required by the specified RawParseMode.
*/
List *
-raw_parser(const char *str, RawParseMode mode)
+raw_parser(const char *str, RawParseMode mode, int offset)
{
core_yyscan_t yyscanner;
base_yy_extra_type yyextra;
@@ -47,7 +47,7 @@ raw_parser(const char *str, RawParseMode mode)
/* initialize the flex scanner */
yyscanner = scanner_init(str, &yyextra.core_yy_extra,
- &ScanKeywords, ScanKeywordTokens);
+ &ScanKeywords, ScanKeywordTokens, offset);
/* base_yylex() only needs us to initialize the lookahead token, if any */
if (mode == RAW_PARSE_DEFAULT)
@@ -61,7 +61,8 @@ raw_parser(const char *str, RawParseMode mode)
MODE_PLPGSQL_EXPR, /* RAW_PARSE_PLPGSQL_EXPR */
MODE_PLPGSQL_ASSIGN1, /* RAW_PARSE_PLPGSQL_ASSIGN1 */
MODE_PLPGSQL_ASSIGN2, /* RAW_PARSE_PLPGSQL_ASSIGN2 */
- MODE_PLPGSQL_ASSIGN3 /* RAW_PARSE_PLPGSQL_ASSIGN3 */
+ MODE_PLPGSQL_ASSIGN3, /* RAW_PARSE_PLPGSQL_ASSIGN3 */
+ MODE_SINGLE_QUERY /* RAW_PARSE_SINGLE_QUERY */
};
yyextra.have_lookahead = true;
diff --git a/src/backend/parser/scan.l b/src/backend/parser/scan.l
index 9f9d8a1706..2191360a72 100644
--- a/src/backend/parser/scan.l
+++ b/src/backend/parser/scan.l
@@ -1189,8 +1189,10 @@ core_yyscan_t
scanner_init(const char *str,
core_yy_extra_type *yyext,
const ScanKeywordList *keywordlist,
- const uint16 *keyword_tokens)
+ const uint16 *keyword_tokens,
+ int offset)
{
+ YY_BUFFER_STATE state;
Size slen = strlen(str);
yyscan_t scanner;
@@ -1213,13 +1215,20 @@ scanner_init(const char *str,
yyext->scanbuflen = slen;
memcpy(yyext->scanbuf, str, slen);
yyext->scanbuf[slen] = yyext->scanbuf[slen + 1] = YY_END_OF_BUFFER_CHAR;
- yy_scan_buffer(yyext->scanbuf, slen + 2, scanner);
+ state = yy_scan_buffer(yyext->scanbuf, slen + 2, scanner);
/* initialize literal buffer to a reasonable but expansible size */
yyext->literalalloc = 1024;
yyext->literalbuf = (char *) palloc(yyext->literalalloc);
yyext->literallen = 0;
+ /*
+ * Adjust the offset in the input string. This is required in single-query
+ * mode, as we need to register the same token locations as we would have
+ * in normal mode with multi-statement query string.
+ */
+ state->yy_buf_pos += offset;
+
return scanner;
}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index e91db69830..a45dd602c0 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -602,17 +602,130 @@ ProcessClientWriteInterrupt(bool blocked)
List *
pg_parse_query(const char *query_string)
{
- List *raw_parsetree_list = NIL;
+ List *result = NIL;
+ int stmt_len, offset;
TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);
if (log_parser_stats)
ResetUsage();
- if (parser_hook)
- raw_parsetree_list = (*parser_hook) (query_string, RAW_PARSE_DEFAULT);
- else
- raw_parsetree_list = raw_parser(query_string, RAW_PARSE_DEFAULT);
+ stmt_len = 0; /* lazily computed when needed */
+ offset = 0;
+
+ while(true)
+ {
+ List *raw_parsetree_list;
+ RawStmt *raw;
+ bool error = false;
+
+ /*----------------
+ * Start parsing the input string. If a third-party module provided a
+ * parser_hook, we switch to single-query parsing so multi-query
+ * commands using different grammar can work properly.
+ * If the third-party modules support the full set of SQL we support,
+ * or want to prevent fallback on the core parser, it can ignore the
+ * RAW_PARSE_SINGLE_QUERY flag and parse the full query string.
+ * In that case they must return a List with more than one RawStmt or a
+ * single RawStmt with a 0 length to stop the parsing phase, or raise
+ * an ERROR.
+ *
+ * Otherwise, plugins should parse a single query only and always
+ * return a List containing a single RawStmt with a properly set length
+ * (possibly 0 if it was a single query without end of query
+ * delimiter). If the command is valid but doesn't contain any
+ * statements (e.g. a single semi-colon), a single RawStmt with a NULL
+ * stmt field should be returned, containing the consumed query string
+ * length so we can move to the next command in a single pass rather
+ * than 1 byte at a time.
+ *
+ * Also, third-party modules can choose to ignore some or all of
+ * parsing error if they want to implement only subset of postgres
+ * suppoted syntax, or even a totally different syntax, and fall-back
+ * on core grammar for unhandled case. In thase case, they should set
+ * the error flag to true. The returned List will be ignored and the
+ * same offset of the input string will be parsed using the core
+ * parser.
+ *
+ * Finally, note that third-party modules that wants to fallback on
+ * other grammar should first try to call a previous parser hook if any
+ * before setting the error switch and returning .
+ */
+ if (parser_hook)
+ raw_parsetree_list = (*parser_hook) (query_string,
+ RAW_PARSE_SINGLE_QUERY,
+ offset,
+ &error);
+
+ /*
+ * If a third-party module couldn't parse a single query or if no
+ * third-party module is configured, fallback on core parser.
+ */
+ if (error || !parser_hook)
+ raw_parsetree_list = raw_parser(query_string,
+ error ? RAW_PARSE_SINGLE_QUERY : RAW_PARSE_DEFAULT, offset);
+
+ /*
+ * If there are no third-party plugin, or none of the parsers found a
+ * valid query, or if a third party module consumed the whole
+ * query string we're done.
+ */
+ if (!parser_hook || raw_parsetree_list == NIL ||
+ list_length(raw_parsetree_list) > 1)
+ {
+ /*
+ * Warn third-party plugins if they mix "single query" and "whole
+ * input string" strategy rather than silently accepting it and
+ * maybe allow fallback on core grammar even if they want to avoid
+ * that. This way plugin authors can be warned early of the issue.
+ */
+ if (result != NIL)
+ {
+ Assert(parser_hook != NULL);
+ elog(ERROR, "parser_hook should parse a single statement at "
+ "a time or consume the whole input string at once");
+ }
+ result = raw_parsetree_list;
+ break;
+ }
+
+ if (stmt_len == 0)
+ stmt_len = strlen(query_string);
+
+ raw = linitial_node(RawStmt, raw_parsetree_list);
+
+ /*
+ * In single-query mode, the parser will return statement location info
+ * relative to the beginning of complete original string, not the part
+ * we just parsed, so adjust the location info.
+ */
+ if (offset > 0 && raw->stmt_len > 0)
+ {
+ Assert(raw->stmt_len > offset);
+ raw->stmt_location = offset;
+ raw->stmt_len -= offset;
+ }
+
+ /* Ignore the statement if it didn't contain any command. */
+ if (raw->stmt)
+ result = lappend(result, raw);
+
+ if (raw->stmt_len == 0)
+ {
+ /* The statement was the whole string, we're done. */
+ break;
+ }
+ else if (raw->stmt_len + offset >= stmt_len)
+ {
+ /* We consumed all of the input string, we're done. */
+ break;
+ }
+ else
+ {
+ /* Advance the offset to the next command. */
+ offset += raw->stmt_len;
+ }
+ }
if (log_parser_stats)
ShowUsage("PARSER STATISTICS");
@@ -620,13 +733,13 @@ pg_parse_query(const char *query_string)
#ifdef COPY_PARSE_PLAN_TREES
/* Optional debugging check: pass raw parsetrees through copyObject() */
{
- List *new_list = copyObject(raw_parsetree_list);
+ List *new_list = copyObject(result);
/* This checks both copyObject() and the equal() routines... */
- if (!equal(new_list, raw_parsetree_list))
+ if (!equal(new_list, result))
elog(WARNING, "copyObject() failed to produce an equal raw parse tree");
else
- raw_parsetree_list = new_list;
+ result = new_list;
}
#endif
@@ -638,7 +751,7 @@ pg_parse_query(const char *query_string)
TRACE_POSTGRESQL_QUERY_PARSE_DONE(query_string);
- return raw_parsetree_list;
+ return result;
}
/*
diff --git a/src/include/parser/parser.h b/src/include/parser/parser.h
index 853b0f1606..5694ae791a 100644
--- a/src/include/parser/parser.h
+++ b/src/include/parser/parser.h
@@ -41,7 +41,8 @@ typedef enum
RAW_PARSE_PLPGSQL_EXPR,
RAW_PARSE_PLPGSQL_ASSIGN1,
RAW_PARSE_PLPGSQL_ASSIGN2,
- RAW_PARSE_PLPGSQL_ASSIGN3
+ RAW_PARSE_PLPGSQL_ASSIGN3,
+ RAW_PARSE_SINGLE_QUERY
} RawParseMode;
/* Values for the backslash_quote GUC */
@@ -59,7 +60,7 @@ extern PGDLLIMPORT bool standard_conforming_strings;
/* Primary entry point for the raw parsing functions */
-extern List *raw_parser(const char *str, RawParseMode mode);
+extern List *raw_parser(const char *str, RawParseMode mode, int offset);
/* Utility functions exported by gram.y (perhaps these should be elsewhere) */
extern List *SystemFuncName(char *name);
diff --git a/src/include/parser/scanner.h b/src/include/parser/scanner.h
index 0d8182faa0..2747e8b1a0 100644
--- a/src/include/parser/scanner.h
+++ b/src/include/parser/scanner.h
@@ -136,7 +136,8 @@ extern PGDLLIMPORT const uint16 ScanKeywordTokens[];
extern core_yyscan_t scanner_init(const char *str,
core_yy_extra_type *yyext,
const ScanKeywordList *keywordlist,
- const uint16 *keyword_tokens);
+ const uint16 *keyword_tokens,
+ int offset);
extern void scanner_finish(core_yyscan_t yyscanner);
extern int core_yylex(core_YYSTYPE *lvalp, YYLTYPE *llocp,
core_yyscan_t yyscanner);
diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h
index 131dc2b22e..27201dde1d 100644
--- a/src/include/tcop/tcopprot.h
+++ b/src/include/tcop/tcopprot.h
@@ -45,7 +45,8 @@ typedef enum
extern PGDLLIMPORT int log_statement;
/* Hook for plugins to get control in pg_parse_query() */
-typedef List *(*parser_hook_type) (const char *str, RawParseMode mode);
+typedef List *(*parser_hook_type) (const char *str, RawParseMode mode,
+ int offset, bool *error);
extern PGDLLIMPORT parser_hook_type parser_hook;
extern List *pg_parse_query(const char *query_string);
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 34e0520719..6e09f01370 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -3690,7 +3690,7 @@ check_sql_expr(const char *stmt, RawParseMode parseMode, int location)
error_context_stack = &syntax_errcontext;
oldCxt = MemoryContextSwitchTo(plpgsql_compile_tmp_cxt);
- (void) raw_parser(stmt, parseMode);
+ (void) raw_parser(stmt, parseMode, 0);
MemoryContextSwitchTo(oldCxt);
/* Restore former ereport callback */
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index e4c7a91ab5..a2886c42ec 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -587,7 +587,7 @@ plpgsql_scanner_init(const char *str)
{
/* Start up the core scanner */
yyscanner = scanner_init(str, &core_yy,
- &ReservedPLKeywords, ReservedPLKeywordTokens);
+ &ReservedPLKeywords, ReservedPLKeywordTokens, 0);
/*
* scanorig points to the original string, which unlike the scanner's
--
2.30.1
--ymfuxy2befenx45f
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v1-0004-Teach-sqlol-to-use-the-new-MODE_SINGLE_QUERY-pars.patch"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Support run-time partition pruning for hash join
@ 2023-11-04 10:00 Alexander Lakhin <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Alexander Lakhin @ 2023-11-04 10:00 UTC (permalink / raw)
To: Richard Guo <[email protected]>; David Rowley <[email protected]>; +Cc: Andy Fan <[email protected]>; pgsql-hackers
Hello Richard,
02.11.2023 14:19, Richard Guo wrote:
>
> However, the cfbot indicates that there are test cases that fail on
> FreeBSD [1] (no failure on other platforms). So I set up a FreeBSD-13
> locally but just cannot reproduce the failure. I must be doing
> something wrong. Can anyone give me some hints or suggestions?
>
> FYI. The failure looks like:
>
> explain (costs off)
> select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2
> on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';
> - QUERY PLAN
> -----------------------------------------------------
> - Hash Join
> - Hash Cond: (p2.a = p1.a)
> - -> Seq Scan on permtest_grandchild p2
> - -> Hash
> - -> Seq Scan on permtest_grandchild p1
> - Filter: ("left"(c, 3) ~ 'a1$'::text)
> -(6 rows)
> -
> +ERROR: unrecognized node type: 1130127496
I've managed to reproduce that failure on my Ubuntu with:
CPPFLAGS="-Og -DWRITE_READ_PARSE_PLAN_TREES -DCOPY_PARSE_PLAN_TREES" ./configure ... make check
...
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------
- Sort
- Sort Key: t1.a, t2.b
- -> Hash Right Join
- Hash Cond: (t2.b = t1.a)
- -> Append
- -> Seq Scan on prt2_p1 t2_1
- -> Seq Scan on prt2_p2 t2_2
- -> Seq Scan on prt2_p3 t2_3
- -> Hash
- -> Append
- -> Seq Scan on prt1_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_p3 t1_3
- Filter: (b = 0)
-(16 rows)
-
+ERROR: unrecognized node type: -1465804424
...
As far as I can see from https://cirrus-ci.com/task/6642692659085312,
the FreeBSD host has the following CPPFLAGS specified:
-DRELCACHE_FORCE_RELEASE
-DCOPY_PARSE_PLAN_TREES
-DWRITE_READ_PARSE_PLAN_TREES
-DRAW_EXPRESSION_COVERAGE_TEST
-DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
Best regards,
Alexander
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Support run-time partition pruning for hash join
@ 2023-11-06 03:05 Richard Guo <[email protected]>
parent: Alexander Lakhin <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Guo @ 2023-11-06 03:05 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: David Rowley <[email protected]>; Andy Fan <[email protected]>; pgsql-hackers
On Sat, Nov 4, 2023 at 6:00 PM Alexander Lakhin <[email protected]> wrote:
> 02.11.2023 14:19, Richard Guo wrote:
>
> However, the cfbot indicates that there are test cases that fail on
> FreeBSD [1] (no failure on other platforms). So I set up a FreeBSD-13
> locally but just cannot reproduce the failure. I must be doing
> something wrong. Can anyone give me some hints or suggestions?
>
> I've managed to reproduce that failure on my Ubuntu with:
> CPPFLAGS="-Og -DWRITE_READ_PARSE_PLAN_TREES -DCOPY_PARSE_PLAN_TREES"
> ./configure ... make check
>
Wow, thank you so much. You saved me a lot of time. It turns out that
it was caused by me not making JoinPartitionPruneInfo a node. The same
issue can also exist for JoinPartitionPruneCandidateInfo - if you
pprint(root) at some point you'll see 'could not dump unrecognized node
type' warning.
Fixed this issue in v4.
Thanks
Richard
Attachments:
[application/octet-stream] v4-0001-Support-run-time-partition-pruning-for-hash-join.patch (59.1K, ../../CAMbWs49bPXs6SVFxJnV4zh_nH6AV0nhHRiJb+E+vFHy9xnvKCA@mail.gmail.com/3-v4-0001-Support-run-time-partition-pruning-for-hash-join.patch)
download | inline diff:
From 9878f4d4ff6f3c4c9b4e5e17b2a2370d40ce047b Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Mon, 14 Aug 2023 14:55:26 +0800
Subject: [PATCH v4] Support run-time partition pruning for hash join
If we have a hash join with an Append node on the outer side, something
like
Hash Join
Hash Cond: (pt.a = t.a)
-> Append
-> Seq Scan on pt_p1 pt_1
-> Seq Scan on pt_p2 pt_2
-> Seq Scan on pt_p3 pt_3
-> Hash
-> Seq Scan on t
We can actually prune those subnodes of the Append that cannot possibly
contain any matching tuples from the other side of the join. To do
that, when building the Hash table, for each row from the inner side we
can compute the minimum set of subnodes that can possibly match the join
condition. When we have built the Hash table and start to execute the
Append node, we should have known which subnodes are survived and thus
can skip other subnodes.
This patch implements this idea.
---
src/backend/commands/explain.c | 61 ++++
src/backend/executor/execPartition.c | 127 +++++++-
src/backend/executor/nodeAppend.c | 32 +-
src/backend/executor/nodeHash.c | 75 +++++
src/backend/executor/nodeHashjoin.c | 10 +
src/backend/executor/nodeMergeAppend.c | 22 +-
src/backend/optimizer/path/costsize.c | 106 +++++++
src/backend/optimizer/plan/createplan.c | 49 ++-
src/backend/optimizer/plan/setrefs.c | 61 ++++
src/backend/partitioning/partprune.c | 288 ++++++++++++++++--
src/include/executor/execPartition.h | 17 +-
src/include/nodes/execnodes.h | 3 +
src/include/nodes/pathnodes.h | 3 +
src/include/nodes/plannodes.h | 36 +++
src/include/optimizer/cost.h | 4 +
src/include/partitioning/partprune.h | 12 +-
src/test/regress/expected/partition_prune.out | 86 ++++++
src/test/regress/sql/partition_prune.sql | 39 +++
18 files changed, 982 insertions(+), 49 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index f1d71bc54e..c51cf6beb6 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -18,6 +18,7 @@
#include "commands/createas.h"
#include "commands/defrem.h"
#include "commands/prepare.h"
+#include "executor/execPartition.h"
#include "executor/nodeHash.h"
#include "foreign/fdwapi.h"
#include "jit/jit.h"
@@ -118,6 +119,9 @@ static void show_instrumentation_count(const char *qlabel, int which,
PlanState *planstate, ExplainState *es);
static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es);
static void show_eval_params(Bitmapset *bms_params, ExplainState *es);
+static void show_join_pruning_result_info(Bitmapset *join_prune_paramids,
+ ExplainState *es);
+static void show_joinpartprune_info(HashState *hashstate, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);
static void show_buffer_usage(ExplainState *es, const BufferUsage *usage,
bool planning);
@@ -2057,9 +2061,17 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_incremental_sort_info(castNode(IncrementalSortState, planstate),
es);
break;
+ case T_Append:
+ if (es->verbose)
+ show_join_pruning_result_info(((Append *) plan)->join_prune_paramids,
+ es);
+ break;
case T_MergeAppend:
show_merge_append_keys(castNode(MergeAppendState, planstate),
ancestors, es);
+ if (es->verbose)
+ show_join_pruning_result_info(((MergeAppend *) plan)->join_prune_paramids,
+ es);
break;
case T_Result:
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
@@ -2075,6 +2087,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
break;
case T_Hash:
show_hash_info(castNode(HashState, planstate), es);
+ if (es->verbose)
+ show_joinpartprune_info(castNode(HashState, planstate), es);
break;
case T_Memoize:
show_memoize_info(castNode(MemoizeState, planstate), ancestors,
@@ -3515,6 +3529,53 @@ show_eval_params(Bitmapset *bms_params, ExplainState *es)
ExplainPropertyList("Params Evaluated", params, es);
}
+/*
+ * Show join partition pruning results at Append/MergeAppend nodes.
+ */
+static void
+show_join_pruning_result_info(Bitmapset *join_prune_paramids, ExplainState *es)
+{
+ int paramid = -1;
+ List *params = NIL;
+
+ if (bms_is_empty(join_prune_paramids))
+ return;
+
+ while ((paramid = bms_next_member(join_prune_paramids, paramid)) >= 0)
+ {
+ char param[32];
+
+ snprintf(param, sizeof(param), "$%d", paramid);
+ params = lappend(params, pstrdup(param));
+ }
+
+ ExplainPropertyList("Join Partition Pruning", params, es);
+}
+
+/*
+ * Show join partition pruning infos at Hash nodes.
+ */
+static void
+show_joinpartprune_info(HashState *hashstate, ExplainState *es)
+{
+ List *params = NIL;
+ ListCell *lc;
+
+ if (!hashstate->joinpartprune_state_list)
+ return;
+
+ foreach(lc, hashstate->joinpartprune_state_list)
+ {
+ JoinPartitionPruneState *jpstate = (JoinPartitionPruneState *) lfirst(lc);
+ char param[32];
+
+ snprintf(param, sizeof(param), "$%d", jpstate->paramid);
+ params = lappend(params, pstrdup(param));
+ }
+
+ ExplainPropertyList("Partition Prune", params, es);
+}
+
/*
* Fetch the name of an index in an EXPLAIN
*
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index f6c34328b8..35a9149a39 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -199,6 +199,8 @@ static void find_matching_subplans_recurse(PartitionPruningData *prunedata,
PartitionedRelPruningData *pprune,
bool initial_prune,
Bitmapset **validsubplans);
+static bool get_join_prune_matching_subplans(PlanState *planstate,
+ Bitmapset **partset);
/*
@@ -1806,7 +1808,7 @@ ExecInitPartitionPruning(PlanState *planstate,
* Perform an initial partition prune pass, if required.
*/
if (prunestate->do_initial_prune)
- *initially_valid_subplans = ExecFindMatchingSubPlans(prunestate, true);
+ *initially_valid_subplans = ExecFindMatchingSubPlans(prunestate, true, NULL);
else
{
/* No pruning, so we'll need to initialize all subplans */
@@ -1836,6 +1838,37 @@ ExecInitPartitionPruning(PlanState *planstate,
return prunestate;
}
+/*
+ * ExecInitJoinpartpruneList
+ * Initialize data structures needed for join partition pruning
+ */
+List *
+ExecInitJoinpartpruneList(PlanState *planstate,
+ List *joinpartprune_info_list)
+{
+ ListCell *lc;
+ List *result = NIL;
+
+ foreach(lc, joinpartprune_info_list)
+ {
+ JoinPartitionPruneInfo *jpinfo = (JoinPartitionPruneInfo *) lfirst(lc);
+ JoinPartitionPruneState *jpstate = palloc(sizeof(JoinPartitionPruneState));
+
+ jpstate->part_prune_state =
+ CreatePartitionPruneState(planstate, jpinfo->part_prune_info);
+ Assert(jpstate->part_prune_state->do_exec_prune);
+
+ jpstate->paramid = jpinfo->paramid;
+ jpstate->nplans = jpinfo->nplans;
+ jpstate->finished = false;
+ jpstate->part_prune_result = NULL;
+
+ result = lappend(result, jpstate);
+ }
+
+ return result;
+}
+
/*
* CreatePartitionPruneState
* Build the data structure required for calling ExecFindMatchingSubPlans
@@ -2273,7 +2306,9 @@ PartitionPruneFixSubPlanMap(PartitionPruneState *prunestate,
/*
* ExecFindMatchingSubPlans
* Determine which subplans match the pruning steps detailed in
- * 'prunestate' for the current comparison expression values.
+ * 'prunestate' if any for the current comparison expression values, and
+ * meanwhile match the join partition pruning results if any stored in
+ * Append/MergeAppend node's join_prune_paramids.
*
* Pass initial_prune if PARAM_EXEC Params cannot yet be evaluated. This
* differentiates the initial executor-time pruning step from later
@@ -2281,11 +2316,30 @@ PartitionPruneFixSubPlanMap(PartitionPruneState *prunestate,
*/
Bitmapset *
ExecFindMatchingSubPlans(PartitionPruneState *prunestate,
- bool initial_prune)
+ bool initial_prune,
+ PlanState *planstate)
{
Bitmapset *result = NULL;
MemoryContext oldcontext;
int i;
+ Bitmapset *join_prune_partset = NULL;
+ bool do_join_prune;
+
+ /* Retrieve the join partition pruning results if any */
+ do_join_prune =
+ get_join_prune_matching_subplans(planstate, &join_prune_partset);
+
+ /*
+ * Either we're here on partition prune done according to the pruning steps
+ * detailed in 'prunestate', or we have done join partition prune.
+ */
+ Assert(do_join_prune || prunestate != NULL);
+
+ /*
+ * If there is no 'prunestate', then rely entirely on join pruning.
+ */
+ if (prunestate == NULL)
+ return join_prune_partset;
/*
* Either we're here on the initial prune done during pruning
@@ -2326,6 +2380,10 @@ ExecFindMatchingSubPlans(PartitionPruneState *prunestate,
/* Add in any subplans that partition pruning didn't account for */
result = bms_add_members(result, prunestate->other_subplans);
+ /* Intersect join partition pruning results */
+ if (do_join_prune)
+ result = bms_intersect(result, join_prune_partset);
+
MemoryContextSwitchTo(oldcontext);
/* Copy result out of the temp context before we reset it */
@@ -2396,3 +2454,66 @@ find_matching_subplans_recurse(PartitionPruningData *prunedata,
}
}
}
+
+/*
+ * get_join_prune_matching_subplans
+ * Retrieve the join partition pruning results if any stored in
+ * Append/MergeAppend node's join_prune_paramids. Return true if we can
+ * do join partition pruning, otherwise return false.
+ *
+ * Adds valid (non-prunable) subplan IDs to *partset
+ */
+static bool
+get_join_prune_matching_subplans(PlanState *planstate, Bitmapset **partset)
+{
+ Bitmapset *join_prune_paramids;
+ int nplans;
+ int paramid;
+
+ if (planstate == NULL)
+ return false;
+
+ if (IsA(planstate, AppendState))
+ {
+ join_prune_paramids =
+ ((Append *) planstate->plan)->join_prune_paramids;
+ nplans = ((AppendState *) planstate)->as_nplans;
+ }
+ else if (IsA(planstate, MergeAppendState))
+ {
+ join_prune_paramids =
+ ((MergeAppend *) planstate->plan)->join_prune_paramids;
+ nplans = ((MergeAppendState *) planstate)->ms_nplans;
+ }
+ else
+ {
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(planstate));
+ return false;
+ }
+
+ if (bms_is_empty(join_prune_paramids))
+ return false;
+
+ Assert(nplans > 0);
+ *partset = bms_add_range(NULL, 0, nplans - 1);
+
+ paramid = -1;
+ while ((paramid = bms_next_member(join_prune_paramids, paramid)) >= 0)
+ {
+ ParamExecData *param;
+ JoinPartitionPruneState *jpstate;
+
+ param = &(planstate->state->es_param_exec_vals[paramid]);
+ Assert(param->execPlan == NULL);
+ Assert(!param->isnull);
+ jpstate = (JoinPartitionPruneState *) DatumGetPointer(param->value);
+
+ if (jpstate != NULL)
+ *partset = bms_intersect(*partset, jpstate->part_prune_result);
+ else /* the Hash node for this pruning has not been executed */
+ elog(WARNING, "Join partition pruning $%d has not been performed yet.",
+ paramid);
+ }
+
+ return true;
+}
diff --git a/src/backend/executor/nodeAppend.c b/src/backend/executor/nodeAppend.c
index 609df6b9e6..c8dd8583d2 100644
--- a/src/backend/executor/nodeAppend.c
+++ b/src/backend/executor/nodeAppend.c
@@ -151,11 +151,13 @@ ExecInitAppend(Append *node, EState *estate, int eflags)
nplans = bms_num_members(validsubplans);
/*
- * When no run-time pruning is required and there's at least one
- * subplan, we can fill as_valid_subplans immediately, preventing
- * later calls to ExecFindMatchingSubPlans.
+ * When no run-time pruning or join pruning is required and there's at
+ * least one subplan, we can fill as_valid_subplans immediately,
+ * preventing later calls to ExecFindMatchingSubPlans.
*/
- if (!prunestate->do_exec_prune && nplans > 0)
+ if (!prunestate->do_exec_prune &&
+ bms_is_empty(node->join_prune_paramids) &&
+ nplans > 0)
{
appendstate->as_valid_subplans = bms_add_range(NULL, 0, nplans - 1);
appendstate->as_valid_subplans_identified = true;
@@ -170,10 +172,18 @@ ExecInitAppend(Append *node, EState *estate, int eflags)
* subplans as valid; they must also all be initialized.
*/
Assert(nplans > 0);
- appendstate->as_valid_subplans = validsubplans =
- bms_add_range(NULL, 0, nplans - 1);
- appendstate->as_valid_subplans_identified = true;
+ validsubplans = bms_add_range(NULL, 0, nplans - 1);
appendstate->as_prune_state = NULL;
+
+ /*
+ * When join pruning is not enabled we can fill as_valid_subplans
+ * immediately, preventing later calls to ExecFindMatchingSubPlans.
+ */
+ if (bms_is_empty(node->join_prune_paramids))
+ {
+ appendstate->as_valid_subplans = validsubplans;
+ appendstate->as_valid_subplans_identified = true;
+ }
}
/*
@@ -580,7 +590,7 @@ choose_next_subplan_locally(AppendState *node)
else if (!node->as_valid_subplans_identified)
{
node->as_valid_subplans =
- ExecFindMatchingSubPlans(node->as_prune_state, false);
+ ExecFindMatchingSubPlans(node->as_prune_state, false, &node->ps);
node->as_valid_subplans_identified = true;
}
@@ -647,7 +657,7 @@ choose_next_subplan_for_leader(AppendState *node)
if (!node->as_valid_subplans_identified)
{
node->as_valid_subplans =
- ExecFindMatchingSubPlans(node->as_prune_state, false);
+ ExecFindMatchingSubPlans(node->as_prune_state, false, &node->ps);
node->as_valid_subplans_identified = true;
/*
@@ -723,7 +733,7 @@ choose_next_subplan_for_worker(AppendState *node)
else if (!node->as_valid_subplans_identified)
{
node->as_valid_subplans =
- ExecFindMatchingSubPlans(node->as_prune_state, false);
+ ExecFindMatchingSubPlans(node->as_prune_state, false, &node->ps);
node->as_valid_subplans_identified = true;
mark_invalid_subplans_as_finished(node);
@@ -876,7 +886,7 @@ ExecAppendAsyncBegin(AppendState *node)
if (!node->as_valid_subplans_identified)
{
node->as_valid_subplans =
- ExecFindMatchingSubPlans(node->as_prune_state, false);
+ ExecFindMatchingSubPlans(node->as_prune_state, false, &node->ps);
node->as_valid_subplans_identified = true;
classify_matching_subplans(node);
diff --git a/src/backend/executor/nodeHash.c b/src/backend/executor/nodeHash.c
index e72f0986c2..9ca8bf49d9 100644
--- a/src/backend/executor/nodeHash.c
+++ b/src/backend/executor/nodeHash.c
@@ -31,6 +31,7 @@
#include "catalog/pg_statistic.h"
#include "commands/tablespace.h"
#include "executor/execdebug.h"
+#include "executor/execPartition.h"
#include "executor/hashjoin.h"
#include "executor/nodeHash.h"
#include "executor/nodeHashjoin.h"
@@ -48,6 +49,8 @@ static void ExecHashIncreaseNumBatches(HashJoinTable hashtable);
static void ExecHashIncreaseNumBuckets(HashJoinTable hashtable);
static void ExecParallelHashIncreaseNumBatches(HashJoinTable hashtable);
static void ExecParallelHashIncreaseNumBuckets(HashJoinTable hashtable);
+static void ExecJoinPartitionPrune(HashState *node);
+static void ExecStoreJoinPartitionPruneResult(HashState *node);
static void ExecHashBuildSkewHash(HashJoinTable hashtable, Hash *node,
int mcvsToUse);
static void ExecHashSkewTableInsert(HashJoinTable hashtable,
@@ -189,8 +192,14 @@ MultiExecPrivateHash(HashState *node)
}
hashtable->totalTuples += 1;
}
+
+ /* Perform join partition pruning */
+ ExecJoinPartitionPrune(node);
}
+ /* Store the surviving partitions for Append/MergeAppend nodes */
+ ExecStoreJoinPartitionPruneResult(node);
+
/* resize the hash table if needed (NTUP_PER_BUCKET exceeded) */
if (hashtable->nbuckets != hashtable->nbuckets_optimal)
ExecHashIncreaseNumBuckets(hashtable);
@@ -401,6 +410,12 @@ ExecInitHash(Hash *node, EState *estate, int eflags)
hashstate->hashkeys =
ExecInitExprList(node->hashkeys, (PlanState *) hashstate);
+ /*
+ * initialize join partition pruning infos
+ */
+ hashstate->joinpartprune_state_list =
+ ExecInitJoinpartpruneList(&hashstate->ps, node->joinpartprune_info_list);
+
return hashstate;
}
@@ -1601,6 +1616,56 @@ ExecParallelHashIncreaseNumBuckets(HashJoinTable hashtable)
}
}
+/*
+ * ExecJoinPartitionPrune
+ * Perform join partition pruning at this join for each
+ * JoinPartitionPruneState.
+ */
+static void
+ExecJoinPartitionPrune(HashState *node)
+{
+ ListCell *lc;
+
+ foreach(lc, node->joinpartprune_state_list)
+ {
+ JoinPartitionPruneState *jpstate = (JoinPartitionPruneState *) lfirst(lc);
+ Bitmapset *matching_subPlans;
+
+ if (jpstate->finished)
+ continue;
+
+ matching_subPlans =
+ ExecFindMatchingSubPlans(jpstate->part_prune_state, false, NULL);
+ jpstate->part_prune_result =
+ bms_add_members(jpstate->part_prune_result, matching_subPlans);
+
+ if (bms_num_members(jpstate->part_prune_result) == jpstate->nplans)
+ jpstate->finished = true;
+ }
+}
+
+/*
+ * ExecStoreJoinPartitionPruneResult
+ * For each JoinPartitionPruneState, store the set of surviving partitions
+ * to make it available for the Append/MergeAppend node.
+ */
+static void
+ExecStoreJoinPartitionPruneResult(HashState *node)
+{
+ ListCell *lc;
+
+ foreach(lc, node->joinpartprune_state_list)
+ {
+ JoinPartitionPruneState *jpstate = (JoinPartitionPruneState *) lfirst(lc);
+ ParamExecData *param;
+
+ param = &(node->ps.state->es_param_exec_vals[jpstate->paramid]);
+ Assert(param->execPlan == NULL);
+ Assert(!param->isnull);
+ param->value = PointerGetDatum(jpstate);
+ }
+}
+
/*
* ExecHashTableInsert
* insert a tuple into the hash table depending on the hash value
@@ -2345,6 +2410,16 @@ void
ExecReScanHash(HashState *node)
{
PlanState *outerPlan = outerPlanState(node);
+ ListCell *lc;
+
+ /* reset the state in JoinPartitionPruneStates */
+ foreach(lc, node->joinpartprune_state_list)
+ {
+ JoinPartitionPruneState *jpstate = (JoinPartitionPruneState *) lfirst(lc);
+
+ jpstate->finished = false;
+ jpstate->part_prune_result = NULL;
+ }
/*
* if chgParam of subnode is not null then plan will be re-scanned by
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index 25a2d78f15..ddca824206 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -311,6 +311,16 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
*/
node->hj_FirstOuterTupleSlot = NULL;
}
+ else if (hashNode->joinpartprune_state_list != NIL)
+ {
+ /*
+ * Give the hash node a chance to run join partition
+ * pruning if there is any JoinPartitionPruneState that can
+ * be evaluated at it. So do not apply the empty-outer
+ * optimization in this case.
+ */
+ node->hj_FirstOuterTupleSlot = NULL;
+ }
else if (HJ_FILL_OUTER(node) ||
(outerNode->plan->startup_cost < hashNode->ps.plan->total_cost &&
!node->hj_OuterNotEmpty))
diff --git a/src/backend/executor/nodeMergeAppend.c b/src/backend/executor/nodeMergeAppend.c
index 21b5726e6e..9eb276abc8 100644
--- a/src/backend/executor/nodeMergeAppend.c
+++ b/src/backend/executor/nodeMergeAppend.c
@@ -99,11 +99,13 @@ ExecInitMergeAppend(MergeAppend *node, EState *estate, int eflags)
nplans = bms_num_members(validsubplans);
/*
- * When no run-time pruning is required and there's at least one
- * subplan, we can fill ms_valid_subplans immediately, preventing
- * later calls to ExecFindMatchingSubPlans.
+ * When no run-time pruning or join pruning is required and there's at
+ * least one subplan, we can fill ms_valid_subplans immediately,
+ * preventing later calls to ExecFindMatchingSubPlans.
*/
- if (!prunestate->do_exec_prune && nplans > 0)
+ if (!prunestate->do_exec_prune &&
+ bms_is_empty(node->join_prune_paramids) &&
+ nplans > 0)
mergestate->ms_valid_subplans = bms_add_range(NULL, 0, nplans - 1);
}
else
@@ -115,9 +117,15 @@ ExecInitMergeAppend(MergeAppend *node, EState *estate, int eflags)
* subplans as valid; they must also all be initialized.
*/
Assert(nplans > 0);
- mergestate->ms_valid_subplans = validsubplans =
- bms_add_range(NULL, 0, nplans - 1);
+ validsubplans = bms_add_range(NULL, 0, nplans - 1);
mergestate->ms_prune_state = NULL;
+
+ /*
+ * When join pruning is not enabled we can fill ms_valid_subplans
+ * immediately, preventing later calls to ExecFindMatchingSubPlans.
+ */
+ if (bms_is_empty(node->join_prune_paramids))
+ mergestate->ms_valid_subplans = validsubplans;
}
mergeplanstates = (PlanState **) palloc(nplans * sizeof(PlanState *));
@@ -218,7 +226,7 @@ ExecMergeAppend(PlanState *pstate)
*/
if (node->ms_valid_subplans == NULL)
node->ms_valid_subplans =
- ExecFindMatchingSubPlans(node->ms_prune_state, false);
+ ExecFindMatchingSubPlans(node->ms_prune_state, false, &node->ps);
/*
* First time through: pull the first tuple from each valid subplan,
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index d6ceafd51c..9bdc88a9db 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -173,6 +173,10 @@ static void get_restriction_qual_cost(PlannerInfo *root, RelOptInfo *baserel,
static bool has_indexed_join_quals(NestPath *path);
static double approx_tuple_count(PlannerInfo *root, JoinPath *path,
List *quals);
+static double get_joinrel_matching_outer_size(PlannerInfo *root,
+ RelOptInfo *outer_rel,
+ Relids inner_relids,
+ List *restrictlist);
static double calc_joinrel_size_estimate(PlannerInfo *root,
RelOptInfo *joinrel,
RelOptInfo *outer_rel,
@@ -5380,6 +5384,61 @@ get_parameterized_joinrel_size(PlannerInfo *root, RelOptInfo *rel,
return nrows;
}
+/*
+ * get_joinrel_matching_outer_size
+ * Make a size estimate for the outer side that matches the inner side.
+ */
+static double
+get_joinrel_matching_outer_size(PlannerInfo *root,
+ RelOptInfo *outer_rel,
+ Relids inner_relids,
+ List *restrictlist)
+{
+ double nrows;
+ Selectivity fkselec;
+ Selectivity jselec;
+ SpecialJoinInfo *sjinfo;
+ SpecialJoinInfo sjinfo_data;
+
+ sjinfo = &sjinfo_data;
+ sjinfo->type = T_SpecialJoinInfo;
+ sjinfo->min_lefthand = outer_rel->relids;
+ sjinfo->min_righthand = inner_relids;
+ sjinfo->syn_lefthand = outer_rel->relids;
+ sjinfo->syn_righthand = inner_relids;
+ sjinfo->jointype = JOIN_SEMI;
+ sjinfo->ojrelid = 0;
+ sjinfo->commute_above_l = NULL;
+ sjinfo->commute_above_r = NULL;
+ sjinfo->commute_below_l = NULL;
+ sjinfo->commute_below_r = NULL;
+ /* we don't bother trying to make the remaining fields valid */
+ sjinfo->lhs_strict = false;
+ sjinfo->semi_can_btree = false;
+ sjinfo->semi_can_hash = false;
+ sjinfo->semi_operators = NIL;
+ sjinfo->semi_rhs_exprs = NIL;
+
+ fkselec = get_foreign_key_join_selectivity(root,
+ outer_rel->relids,
+ inner_relids,
+ sjinfo,
+ &restrictlist);
+ jselec = clauselist_selectivity(root,
+ restrictlist,
+ 0,
+ sjinfo->jointype,
+ sjinfo);
+
+ nrows = outer_rel->rows * fkselec * jselec;
+ nrows = clamp_row_est(nrows);
+
+ /* For safety, make sure result is not more than the base estimate */
+ if (nrows > outer_rel->rows)
+ nrows = outer_rel->rows;
+ return nrows;
+}
+
/*
* calc_joinrel_size_estimate
* Workhorse for set_joinrel_size_estimates and
@@ -6495,3 +6554,50 @@ compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel, Path *bitmapqual,
return pages_fetched;
}
+
+/*
+ * compute_partprune_cost
+ * Compute the overhead of join partition pruning.
+ */
+double
+compute_partprune_cost(PlannerInfo *root, RelOptInfo *appendrel,
+ Cost append_total_cost, int append_nplans,
+ Relids inner_relids, double inner_rows,
+ List *prunequal)
+{
+ Cost prune_cost;
+ Cost saved_cost;
+ double matching_outer_rows;
+ double unmatched_nplans;
+
+ switch (appendrel->part_scheme->strategy)
+ {
+
+ case PARTITION_STRATEGY_LIST:
+ case PARTITION_STRATEGY_RANGE:
+ prune_cost = cpu_operator_cost * LOG2(append_nplans) * inner_rows;
+ break;
+ case PARTITION_STRATEGY_HASH:
+ prune_cost = cpu_operator_cost * append_nplans * inner_rows;
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) appendrel->part_scheme->strategy);
+ break;
+ }
+
+ matching_outer_rows =
+ get_joinrel_matching_outer_size(root,
+ appendrel,
+ inner_relids,
+ prunequal);
+
+ /*
+ * We assume that each outer joined row occupies one new partition. This
+ * is really the worst case.
+ */
+ unmatched_nplans = append_nplans - Min(matching_outer_rows, append_nplans);
+ saved_cost = (unmatched_nplans / append_nplans) * append_total_cost;
+
+ return prune_cost - saved_cost;
+}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 34ca6d4ac2..308ff452d3 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -242,7 +242,8 @@ static Hash *make_hash(Plan *lefttree,
List *hashkeys,
Oid skewTable,
AttrNumber skewColumn,
- bool skewInherit);
+ bool skewInherit,
+ List *joinpartprune_info_list);
static MergeJoin *make_mergejoin(List *tlist,
List *joinclauses, List *otherclauses,
List *mergeclauses,
@@ -342,6 +343,7 @@ create_plan(PlannerInfo *root, Path *best_path)
/* Initialize this module's workspace in PlannerInfo */
root->curOuterRels = NULL;
root->curOuterParams = NIL;
+ root->join_partition_prune_candidates = NIL;
/* Recursively process the path tree, demanding the correct tlist result */
plan = create_plan_recurse(root, best_path, CP_EXACT_TLIST);
@@ -369,6 +371,8 @@ create_plan(PlannerInfo *root, Path *best_path)
if (root->curOuterParams != NIL)
elog(ERROR, "failed to assign all NestLoopParams to plan nodes");
+ Assert(root->join_partition_prune_candidates == NIL);
+
/*
* Reset plan_params to ensure param IDs used for nestloop params are not
* re-used later
@@ -1223,6 +1227,7 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
int nasyncplans = 0;
RelOptInfo *rel = best_path->path.parent;
PartitionPruneInfo *partpruneinfo = NULL;
+ Bitmapset *join_prune_paramids = NULL;
int nodenumsortkeys = 0;
AttrNumber *nodeSortColIdx = NULL;
Oid *nodeSortOperators = NULL;
@@ -1377,6 +1382,8 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
* If any quals exist, they may be useful to perform further partition
* pruning during execution. Gather information needed by the executor to
* do partition pruning.
+ *
+ * Also gather information needed by the executor to do join pruning.
*/
if (enable_partition_pruning)
{
@@ -1399,13 +1406,20 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
partpruneinfo =
make_partition_pruneinfo(root, rel,
best_path->subpaths,
- prunequal);
+ prunequal,
+ NULL);
+
+ join_prune_paramids =
+ make_join_partition_pruneinfos(root, rel,
+ (Path *) best_path,
+ best_path->subpaths);
}
plan->appendplans = subplans;
plan->nasyncplans = nasyncplans;
plan->first_partial_plan = best_path->first_partial_path;
plan->part_prune_info = partpruneinfo;
+ plan->join_prune_paramids = join_prune_paramids;
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -1445,6 +1459,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path,
ListCell *subpaths;
RelOptInfo *rel = best_path->path.parent;
PartitionPruneInfo *partpruneinfo = NULL;
+ Bitmapset *join_prune_paramids = NULL;
/*
* We don't have the actual creation of the MergeAppend node split out
@@ -1541,6 +1556,8 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path,
* If any quals exist, they may be useful to perform further partition
* pruning during execution. Gather information needed by the executor to
* do partition pruning.
+ *
+ * Also gather information needed by the executor to do join pruning.
*/
if (enable_partition_pruning)
{
@@ -1554,11 +1571,18 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path,
if (prunequal != NIL)
partpruneinfo = make_partition_pruneinfo(root, rel,
best_path->subpaths,
- prunequal);
+ prunequal,
+ NULL);
+
+ join_prune_paramids =
+ make_join_partition_pruneinfos(root, rel,
+ (Path *) best_path,
+ best_path->subpaths);
}
node->mergeplans = subplans;
node->part_prune_info = partpruneinfo;
+ node->join_prune_paramids = join_prune_paramids;
/*
* If prepare_sort_from_pathkeys added sort columns, but we were told to
@@ -4734,6 +4758,13 @@ create_hashjoin_plan(PlannerInfo *root,
AttrNumber skewColumn = InvalidAttrNumber;
bool skewInherit = false;
ListCell *lc;
+ List *joinpartprune_info_list;
+
+ /*
+ * Collect information required to build JoinPartitionPruneInfos at this
+ * join.
+ */
+ prepare_join_partition_prune_candidate(root, &best_path->jpath);
/*
* HashJoin can project, so we don't have to demand exact tlists from the
@@ -4745,6 +4776,11 @@ create_hashjoin_plan(PlannerInfo *root,
outer_plan = create_plan_recurse(root, best_path->jpath.outerjoinpath,
(best_path->num_batches > 1) ? CP_SMALL_TLIST : 0);
+ /*
+ * Retrieve all the JoinPartitionPruneInfos for this join.
+ */
+ joinpartprune_info_list = get_join_partition_prune_candidate(root);
+
inner_plan = create_plan_recurse(root, best_path->jpath.innerjoinpath,
CP_SMALL_TLIST);
@@ -4850,7 +4886,8 @@ create_hashjoin_plan(PlannerInfo *root,
inner_hashkeys,
skewTable,
skewColumn,
- skewInherit);
+ skewInherit,
+ joinpartprune_info_list);
/*
* Set Hash node's startup & total costs equal to total cost of input
@@ -5977,7 +6014,8 @@ make_hash(Plan *lefttree,
List *hashkeys,
Oid skewTable,
AttrNumber skewColumn,
- bool skewInherit)
+ bool skewInherit,
+ List *joinpartprune_info_list)
{
Hash *node = makeNode(Hash);
Plan *plan = &node->plan;
@@ -5991,6 +6029,7 @@ make_hash(Plan *lefttree,
node->skewTable = skewTable;
node->skewColumn = skewColumn;
node->skewInherit = skewInherit;
+ node->joinpartprune_info_list = joinpartprune_info_list;
return node;
}
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index fc3709510d..c416e7ccda 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -156,6 +156,11 @@ static Plan *set_mergeappend_references(PlannerInfo *root,
MergeAppend *mplan,
int rtoffset);
static void set_hash_references(PlannerInfo *root, Plan *plan, int rtoffset);
+static void set_joinpartitionprune_references(PlannerInfo *root,
+ List *joinpartprune_info_list,
+ indexed_tlist *outer_itlist,
+ int rtoffset,
+ double num_exec);
static Relids offset_relid_set(Relids relids, int rtoffset);
static Node *fix_scan_expr(PlannerInfo *root, Node *node,
int rtoffset, double num_exec);
@@ -1897,6 +1902,62 @@ set_hash_references(PlannerInfo *root, Plan *plan, int rtoffset)
/* Hash nodes don't have their own quals */
Assert(plan->qual == NIL);
+
+ set_joinpartitionprune_references(root,
+ hplan->joinpartprune_info_list,
+ outer_itlist,
+ rtoffset,
+ NUM_EXEC_TLIST(plan));
+}
+
+/*
+ * set_joinpartitionprune_references
+ * Do set_plan_references processing on JoinPartitionPruneInfos
+ */
+static void
+set_joinpartitionprune_references(PlannerInfo *root,
+ List *joinpartprune_info_list,
+ indexed_tlist *outer_itlist,
+ int rtoffset,
+ double num_exec)
+{
+ ListCell *l;
+
+ foreach(l, joinpartprune_info_list)
+ {
+ JoinPartitionPruneInfo *jpinfo = (JoinPartitionPruneInfo *) lfirst(l);
+ ListCell *l1;
+
+ foreach(l1, jpinfo->part_prune_info->prune_infos)
+ {
+ List *prune_infos = lfirst(l1);
+ ListCell *l2;
+
+ foreach(l2, prune_infos)
+ {
+ PartitionedRelPruneInfo *pinfo = lfirst(l2);
+
+ pinfo->rtindex += rtoffset;
+
+ pinfo->initial_pruning_steps = (List *)
+ fix_upper_expr(root,
+ (Node *) pinfo->initial_pruning_steps,
+ outer_itlist,
+ OUTER_VAR,
+ rtoffset,
+ NRM_EQUAL,
+ num_exec);
+ pinfo->exec_pruning_steps = (List *)
+ fix_upper_expr(root,
+ (Node *) pinfo->exec_pruning_steps,
+ outer_itlist,
+ OUTER_VAR,
+ rtoffset,
+ NRM_EQUAL,
+ num_exec);
+ }
+ }
+ }
}
/*
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index 3f31ecc956..a6d6f0ad88 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -48,7 +48,9 @@
#include "optimizer/appendinfo.h"
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
+#include "optimizer/paramassign.h"
#include "optimizer/pathnode.h"
+#include "optimizer/restrictinfo.h"
#include "parser/parsetree.h"
#include "partitioning/partbounds.h"
#include "partitioning/partprune.h"
@@ -103,15 +105,16 @@ typedef enum PartClauseTarget
*
* gen_partprune_steps() initializes and returns an instance of this struct.
*
- * Note that has_mutable_op, has_mutable_arg, and has_exec_param are set if
- * we found any potentially-useful-for-pruning clause having those properties,
- * whether or not we actually used the clause in the steps list. This
- * definition allows us to skip the PARTTARGET_EXEC pass in some cases.
+ * Note that has_mutable_op, has_mutable_arg, has_exec_param and has_vars are
+ * set if we found any potentially-useful-for-pruning clause having those
+ * properties, whether or not we actually used the clause in the steps list.
+ * This definition allows us to skip the PARTTARGET_EXEC pass in some cases.
*/
typedef struct GeneratePruningStepsContext
{
/* Copies of input arguments for gen_partprune_steps: */
RelOptInfo *rel; /* the partitioned relation */
+ Bitmapset *available_rels; /* rels whose Vars may be used for pruning */
PartClauseTarget target; /* use-case we're generating steps for */
/* Result data: */
List *steps; /* list of PartitionPruneSteps */
@@ -119,6 +122,7 @@ typedef struct GeneratePruningStepsContext
bool has_mutable_arg; /* clauses include any mutable comparison
* values, *other than* exec params */
bool has_exec_param; /* clauses include any PARAM_EXEC params */
+ bool has_vars; /* clauses include any Vars from 'available_rels' */
bool contradictory; /* clauses were proven self-contradictory */
/* Working state: */
int next_step_id;
@@ -144,8 +148,10 @@ static List *make_partitionedrel_pruneinfo(PlannerInfo *root,
List *prunequal,
Bitmapset *partrelids,
int *relid_subplan_map,
+ Bitmapset *available_rels,
Bitmapset **matchedsubplans);
static void gen_partprune_steps(RelOptInfo *rel, List *clauses,
+ Bitmapset *available_rels,
PartClauseTarget target,
GeneratePruningStepsContext *context);
static List *gen_partprune_steps_internal(GeneratePruningStepsContext *context,
@@ -204,6 +210,10 @@ static PartClauseMatchStatus match_boolean_partition_clause(Oid partopfamily,
static void partkey_datum_from_expr(PartitionPruneContext *context,
Expr *expr, int stateidx,
Datum *value, bool *isnull);
+static bool contain_forbidden_var_clause(Node *node,
+ GeneratePruningStepsContext *context);
+static bool contain_forbidden_var_clause_walker(Node *node,
+ GeneratePruningStepsContext *context);
/*
@@ -216,11 +226,14 @@ static void partkey_datum_from_expr(PartitionPruneContext *context,
* of scan paths for its child rels.
* 'prunequal' is a list of potential pruning quals (i.e., restriction
* clauses that are applicable to the appendrel).
+ * 'available_rels' is the relid set of rels whose Vars may be used for
+ * pruning.
*/
PartitionPruneInfo *
make_partition_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
List *subpaths,
- List *prunequal)
+ List *prunequal,
+ Bitmapset *available_rels)
{
PartitionPruneInfo *pruneinfo;
Bitmapset *allmatchedsubplans = NULL;
@@ -313,6 +326,7 @@ make_partition_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
prunequal,
partrelids,
relid_subplan_map,
+ available_rels,
&matchedsubplans);
/* When pruning is possible, record the matched subplans */
@@ -360,6 +374,174 @@ make_partition_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
return pruneinfo;
}
+/*
+ * make_join_partition_pruneinfos
+ * Builds one JoinPartitionPruneInfo for each join at which join partition
+ * pruning is possible for this appendrel.
+ *
+ * 'parentrel' is the RelOptInfo for an appendrel, and 'subpaths' is the list
+ * of scan paths for its child rels.
+ */
+Bitmapset *
+make_join_partition_pruneinfos(PlannerInfo *root, RelOptInfo *parentrel,
+ Path *best_path, List *subpaths)
+{
+ Bitmapset *result = NULL;
+ ListCell *lc;
+
+ if (!IS_PARTITIONED_REL(parentrel))
+ return NULL;
+
+ foreach(lc, root->join_partition_prune_candidates)
+ {
+ JoinPartitionPruneCandidateInfo *candidate =
+ (JoinPartitionPruneCandidateInfo *) lfirst(lc);
+ PartitionPruneInfo *part_prune_info;
+ List *prunequal;
+ Relids joinrelids;
+ ListCell *l;
+ double prune_cost;
+
+ if (candidate == NULL)
+ continue;
+
+ /*
+ * Identify all joinclauses that are movable to this appendrel given
+ * this inner side relids. Only those clauses can be used for join
+ * partition pruning.
+ */
+ joinrelids = bms_union(parentrel->relids, candidate->inner_relids);
+ prunequal = NIL;
+ foreach(l, candidate->joinrestrictinfo)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
+
+ if (join_clause_is_movable_into(rinfo,
+ parentrel->relids,
+ joinrelids))
+ prunequal = lappend(prunequal, rinfo);
+ }
+
+ if (prunequal == NIL)
+ continue;
+
+ /*
+ * Check the overhead of this pruning
+ */
+ prune_cost = compute_partprune_cost(root,
+ parentrel,
+ best_path->total_cost,
+ list_length(subpaths),
+ candidate->inner_relids,
+ candidate->inner_rows,
+ prunequal);
+ if (prune_cost > 0)
+ continue;
+
+ part_prune_info = make_partition_pruneinfo(root, parentrel,
+ subpaths,
+ prunequal,
+ candidate->inner_relids);
+
+ if (part_prune_info)
+ {
+ JoinPartitionPruneInfo *jpinfo;
+
+ jpinfo = makeNode(JoinPartitionPruneInfo);
+
+ jpinfo->part_prune_info = part_prune_info;
+ jpinfo->paramid = assign_special_exec_param(root);
+ jpinfo->nplans = list_length(subpaths);
+
+ candidate->joinpartprune_info_list =
+ lappend(candidate->joinpartprune_info_list, jpinfo);
+
+ result = bms_add_member(result, jpinfo->paramid);
+ }
+ }
+
+ return result;
+}
+
+/*
+ * prepare_join_partition_prune_candidate
+ * Check if join partition pruning is possible at this join and if so
+ * collect information required to build JoinPartitionPruneInfos.
+ *
+ * Note that we may build more than one JoinPartitionPruneInfo at one join, for
+ * different Append/MergeAppend paths.
+ */
+void
+prepare_join_partition_prune_candidate(PlannerInfo *root, JoinPath *jpath)
+{
+ JoinPartitionPruneCandidateInfo *candidate;
+
+ if (!enable_partition_pruning)
+ {
+ root->join_partition_prune_candidates =
+ lappend(root->join_partition_prune_candidates, NULL);
+ return;
+ }
+
+ /*
+ * We cannot perform join partition pruning if the outer is the
+ * non-nullable side.
+ */
+ if (!(jpath->jointype == JOIN_INNER ||
+ jpath->jointype == JOIN_SEMI ||
+ jpath->jointype == JOIN_RIGHT ||
+ jpath->jointype == JOIN_RIGHT_ANTI))
+ {
+ root->join_partition_prune_candidates =
+ lappend(root->join_partition_prune_candidates, NULL);
+ return;
+ }
+
+ /*
+ * For now we only support HashJoin.
+ */
+ if (jpath->path.pathtype != T_HashJoin)
+ {
+ root->join_partition_prune_candidates =
+ lappend(root->join_partition_prune_candidates, NULL);
+ return;
+ }
+
+ candidate = makeNode(JoinPartitionPruneCandidateInfo);
+ candidate->joinrestrictinfo = jpath->joinrestrictinfo;
+ candidate->inner_relids = jpath->innerjoinpath->parent->relids;
+ candidate->inner_rows = jpath->innerjoinpath->parent->rows;
+ candidate->joinpartprune_info_list = NIL;
+
+ root->join_partition_prune_candidates =
+ lappend(root->join_partition_prune_candidates, candidate);
+}
+
+/*
+ * get_join_partition_prune_candidate
+ * Pop out the JoinPartitionPruneCandidateInfo for this join and retrieve
+ * the JoinPartitionPruneInfos.
+ */
+List *
+get_join_partition_prune_candidate(PlannerInfo *root)
+{
+ JoinPartitionPruneCandidateInfo *candidate;
+ List *result;
+
+ candidate = llast(root->join_partition_prune_candidates);
+ root->join_partition_prune_candidates =
+ list_delete_last(root->join_partition_prune_candidates);
+
+ if (candidate == NULL)
+ return NIL;
+
+ result = candidate->joinpartprune_info_list;
+
+ pfree(candidate);
+
+ return result;
+}
+
/*
* add_part_relids
* Add new info to a list of Bitmapsets of partitioned relids.
@@ -428,6 +610,8 @@ add_part_relids(List *allpartrelids, Bitmapset *partrelids)
* partrelids: Set of RT indexes identifying relevant partitioned tables
* within a single partitioning hierarchy
* relid_subplan_map[]: maps child relation relids to subplan indexes
+ * available_rels: the relid set of rels whose Vars may be used for
+ * pruning.
* matchedsubplans: on success, receives the set of subplan indexes which
* were matched to this partition hierarchy
*
@@ -440,6 +624,7 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
List *prunequal,
Bitmapset *partrelids,
int *relid_subplan_map,
+ Bitmapset *available_rels,
Bitmapset **matchedsubplans)
{
RelOptInfo *targetpart = NULL;
@@ -539,8 +724,8 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
* pruning steps and detects whether there's any possibly-useful quals
* that would require per-scan pruning.
*/
- gen_partprune_steps(subpart, partprunequal, PARTTARGET_INITIAL,
- &context);
+ gen_partprune_steps(subpart, partprunequal, available_rels,
+ PARTTARGET_INITIAL, &context);
if (context.contradictory)
{
@@ -567,14 +752,15 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
initial_pruning_steps = NIL;
/*
- * If no exec Params appear in potentially-usable pruning clauses,
- * then there's no point in even thinking about per-scan pruning.
+ * If no exec Params or available Vars appear in potentially-usable
+ * pruning clauses, then there's no point in even thinking about
+ * per-scan pruning.
*/
- if (context.has_exec_param)
+ if (context.has_exec_param || context.has_vars)
{
/* ... OK, we'd better think about it */
- gen_partprune_steps(subpart, partprunequal, PARTTARGET_EXEC,
- &context);
+ gen_partprune_steps(subpart, partprunequal, available_rels,
+ PARTTARGET_EXEC, &context);
if (context.contradictory)
{
@@ -587,11 +773,14 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
/*
* Detect which exec Params actually got used; the fact that some
* were in available clauses doesn't mean we actually used them.
- * Skip per-scan pruning if there are none.
*/
execparamids = get_partkey_exec_paramids(exec_pruning_steps);
- if (bms_is_empty(execparamids))
+ /*
+ * Skip per-scan pruning if there are none used exec Params and
+ * there are none available Vars.
+ */
+ if (bms_is_empty(execparamids) && !context.has_vars)
exec_pruning_steps = NIL;
}
else
@@ -703,6 +892,9 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
* Process 'clauses' (typically a rel's baserestrictinfo list of clauses)
* and create a list of "partition pruning steps".
*
+ * 'available_rels' is the relid set of rels whose Vars may be used for
+ * pruning.
+ *
* 'target' tells whether to generate pruning steps for planning (use
* immutable clauses only), or for executor startup (use any allowable
* clause except ones containing PARAM_EXEC Params), or for executor
@@ -712,12 +904,13 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
* some subsidiary flags; see the GeneratePruningStepsContext typedef.
*/
static void
-gen_partprune_steps(RelOptInfo *rel, List *clauses, PartClauseTarget target,
- GeneratePruningStepsContext *context)
+gen_partprune_steps(RelOptInfo *rel, List *clauses, Bitmapset *available_rels,
+ PartClauseTarget target, GeneratePruningStepsContext *context)
{
/* Initialize all output values to zero/false/NULL */
memset(context, 0, sizeof(GeneratePruningStepsContext));
context->rel = rel;
+ context->available_rels = available_rels;
context->target = target;
/*
@@ -773,7 +966,7 @@ prune_append_rel_partitions(RelOptInfo *rel)
* If the clauses are found to be contradictory, we can return the empty
* set.
*/
- gen_partprune_steps(rel, clauses, PARTTARGET_PLANNER,
+ gen_partprune_steps(rel, clauses, NULL, PARTTARGET_PLANNER,
&gcontext);
if (gcontext.contradictory)
return NULL;
@@ -1957,9 +2150,10 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
return PARTCLAUSE_UNSUPPORTED;
/*
- * We can never prune using an expression that contains Vars.
+ * We can never prune using an expression that contains Vars except
+ * for Vars belonging to context->available_rels.
*/
- if (contain_var_clause((Node *) expr))
+ if (contain_forbidden_var_clause((Node *) expr, context))
return PARTCLAUSE_UNSUPPORTED;
/*
@@ -2155,9 +2349,10 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
return PARTCLAUSE_UNSUPPORTED;
/*
- * We can never prune using an expression that contains Vars.
+ * We can never prune using an expression that contains Vars except
+ * for Vars belonging to context->available_rels.
*/
- if (contain_var_clause((Node *) rightop))
+ if (contain_forbidden_var_clause((Node *) rightop, context))
return PARTCLAUSE_UNSUPPORTED;
/*
@@ -3727,3 +3922,54 @@ partkey_datum_from_expr(PartitionPruneContext *context,
*value = ExecEvalExprSwitchContext(exprstate, ectx, isnull);
}
}
+
+/*
+ * contain_forbidden_var_clause
+ * Recursively scan a clause to discover whether it contains any Var nodes
+ * (of the current query level) that do not belong to relations in
+ * context->available_rels.
+ *
+ * Returns true if any such varnode found.
+ *
+ * Does not examine subqueries, therefore must only be used after reduction
+ * of sublinks to subplans!
+ */
+static bool
+contain_forbidden_var_clause(Node *node, GeneratePruningStepsContext *context)
+{
+ return contain_forbidden_var_clause_walker(node, context);
+}
+
+static bool
+contain_forbidden_var_clause_walker(Node *node, GeneratePruningStepsContext *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varlevelsup != 0)
+ return false;
+
+ if (!bms_is_member(var->varno, context->available_rels))
+ return true; /* abort the tree traversal and return true */
+
+ context->has_vars = true;
+
+ if (context->target != PARTTARGET_EXEC)
+ return true; /* abort the tree traversal and return true */
+
+ return false;
+ }
+ if (IsA(node, CurrentOfExpr))
+ return true;
+ if (IsA(node, PlaceHolderVar))
+ {
+ if (((PlaceHolderVar *) node)->phlevelsup == 0)
+ return true; /* abort the tree traversal and return true */
+ /* else fall through to check the contained expr */
+ }
+ return expression_tree_walker(node, contain_forbidden_var_clause_walker,
+ (void *) context);
+}
diff --git a/src/include/executor/execPartition.h b/src/include/executor/execPartition.h
index 15ec869ac8..720bcc1149 100644
--- a/src/include/executor/execPartition.h
+++ b/src/include/executor/execPartition.h
@@ -121,11 +121,26 @@ typedef struct PartitionPruneState
PartitionPruningData *partprunedata[FLEXIBLE_ARRAY_MEMBER];
} PartitionPruneState;
+/*
+ * JoinPartitionPruneState - State object required for plan nodes to perform
+ * join partition pruning.
+ */
+typedef struct JoinPartitionPruneState
+{
+ PartitionPruneState *part_prune_state;
+ int paramid;
+ int nplans;
+ bool finished;
+ Bitmapset *part_prune_result;
+} JoinPartitionPruneState;
+
extern PartitionPruneState *ExecInitPartitionPruning(PlanState *planstate,
int n_total_subplans,
PartitionPruneInfo *pruneinfo,
Bitmapset **initially_valid_subplans);
+extern List *ExecInitJoinpartpruneList(PlanState *planstate, List *joinpartprune_info_list);
extern Bitmapset *ExecFindMatchingSubPlans(PartitionPruneState *prunestate,
- bool initial_prune);
+ bool initial_prune,
+ PlanState *planstate);
#endif /* EXECPARTITION_H */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 5d7f17dee0..0aeafcabff 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2678,6 +2678,9 @@ typedef struct HashState
/* Parallel hash state. */
struct ParallelHashJoinState *parallel_state;
+
+ /* Infos for join partition pruning. */
+ List *joinpartprune_state_list;
} HashState;
/* ----------------
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index ed85dc7414..d066b6105c 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -530,6 +530,9 @@ struct PlannerInfo
/* not-yet-assigned NestLoopParams */
List *curOuterParams;
+ /* a stack of JoinPartitionPruneInfos */
+ List *join_partition_prune_candidates;
+
/*
* These fields are workspace for setrefs.c. Each is an array
* corresponding to glob->subplans. (We could probably teach
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 24d46c76dc..00058a735e 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -276,6 +276,9 @@ typedef struct Append
/* Info for run-time subplan pruning; NULL if we're not doing that */
struct PartitionPruneInfo *part_prune_info;
+
+ /* Info for join partition pruning; NULL if we're not doing that */
+ Bitmapset *join_prune_paramids;
} Append;
/* ----------------
@@ -311,6 +314,9 @@ typedef struct MergeAppend
/* Info for run-time subplan pruning; NULL if we're not doing that */
struct PartitionPruneInfo *part_prune_info;
+
+ /* Info for join partition pruning; NULL if we're not doing that */
+ Bitmapset *join_prune_paramids;
} MergeAppend;
/* ----------------
@@ -1207,6 +1213,7 @@ typedef struct Hash
bool skewInherit; /* is outer join rel an inheritance tree? */
/* all other info is in the parent HashJoin node */
Cardinality rows_total; /* estimate total rows if parallel_aware */
+ List *joinpartprune_info_list; /* infos for join partition pruning */
} Hash;
/* ----------------
@@ -1553,6 +1560,35 @@ typedef struct PartitionPruneStepCombine
List *source_stepids;
} PartitionPruneStepCombine;
+/*
+ * JoinPartitionPruneCandidateInfo - Information required to build
+ * JoinPartitionPruneInfos.
+ */
+typedef struct JoinPartitionPruneCandidateInfo
+{
+ pg_node_attr(no_equal, no_query_jumble)
+
+ NodeTag type;
+ List *joinrestrictinfo;
+ Bitmapset *inner_relids;
+ double inner_rows;
+ List *joinpartprune_info_list;
+} JoinPartitionPruneCandidateInfo;
+
+/*
+ * JoinPartitionPruneInfo - Details required to allow the executor to prune
+ * partitions during join.
+ */
+typedef struct JoinPartitionPruneInfo
+{
+ pg_node_attr(no_equal, no_query_jumble)
+
+ NodeTag type;
+ PartitionPruneInfo *part_prune_info;
+ int paramid;
+ int nplans;
+} JoinPartitionPruneInfo;
+
/*
* Plan invalidation info
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 6d50afbf74..52de844f6d 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -211,5 +211,9 @@ extern void set_foreign_size_estimates(PlannerInfo *root, RelOptInfo *rel);
extern PathTarget *set_pathtarget_cost_width(PlannerInfo *root, PathTarget *target);
extern double compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel,
Path *bitmapqual, int loop_count, Cost *cost, double *tuple);
+extern double compute_partprune_cost(PlannerInfo *root, RelOptInfo *appendrel,
+ Cost append_total_cost, int append_nplans,
+ Relids inner_relids, double inner_rows,
+ List *prunequal);
#endif /* COST_H */
diff --git a/src/include/partitioning/partprune.h b/src/include/partitioning/partprune.h
index 8636e04e37..899aa61b34 100644
--- a/src/include/partitioning/partprune.h
+++ b/src/include/partitioning/partprune.h
@@ -19,6 +19,8 @@
struct PlannerInfo; /* avoid including pathnodes.h here */
struct RelOptInfo;
+struct Path;
+struct JoinPath;
/*
@@ -73,7 +75,15 @@ typedef struct PartitionPruneContext
extern PartitionPruneInfo *make_partition_pruneinfo(struct PlannerInfo *root,
struct RelOptInfo *parentrel,
List *subpaths,
- List *prunequal);
+ List *prunequal,
+ Bitmapset *available_rels);
+extern Bitmapset *make_join_partition_pruneinfos(struct PlannerInfo *root,
+ struct RelOptInfo *parentrel,
+ struct Path *best_path,
+ List *subpaths);
+extern void prepare_join_partition_prune_candidate(struct PlannerInfo *root,
+ struct JoinPath *jpath);
+extern List *get_join_partition_prune_candidate(struct PlannerInfo *root);
extern Bitmapset *prune_append_rel_partitions(struct RelOptInfo *rel);
extern Bitmapset *get_matching_partitions(PartitionPruneContext *context,
List *pruning_steps);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 9a4c48c055..a08e7a1f0a 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3003,6 +3003,92 @@ order by tbl1.col1, tprt.col1;
------+------
(0 rows)
+-- join partition pruning
+-- The 'Memory Usage' from the Hash node can vary between machines. Let's just
+-- replace the number with an 'N'.
+-- We need to run EXPLAIN ANALYZE because we need to see '(never executed)'
+-- notations because that's the only way to verify runtime pruning.
+create function explain_join_partition_pruning(text) returns setof text
+language plpgsql as
+$$
+declare
+ ln text;
+begin
+ for ln in
+ execute format('explain (analyze, verbose, costs off, summary off, timing off) %s',
+ $1)
+ loop
+ ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
+ return next ln;
+ end loop;
+end;
+$$;
+delete from tbl1;
+insert into tbl1 values (501), (505);
+analyze tbl1, tprt;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+set enable_hashjoin = on;
+select explain_join_partition_pruning('
+select * from tprt p1
+ inner join tprt p2 on p1.col1 = p2.col1
+ right join tbl1 t on p1.col1 = t.col1 and p2.col1 = t.col1;');
+ explain_join_partition_pruning
+--------------------------------------------------------------------------------
+ Hash Right Join (actual rows=2 loops=1)
+ Output: p1.col1, p2.col1, t.col1
+ Hash Cond: ((p1.col1 = t.col1) AND (p2.col1 = t.col1))
+ -> Hash Join (actual rows=3 loops=1)
+ Output: p1.col1, p2.col1
+ Hash Cond: (p1.col1 = p2.col1)
+ -> Append (actual rows=3 loops=1)
+ Join Partition Pruning: $0
+ -> Seq Scan on public.tprt_1 p1_1 (never executed)
+ Output: p1_1.col1
+ -> Seq Scan on public.tprt_2 p1_2 (actual rows=3 loops=1)
+ Output: p1_2.col1
+ -> Seq Scan on public.tprt_3 p1_3 (never executed)
+ Output: p1_3.col1
+ -> Seq Scan on public.tprt_4 p1_4 (never executed)
+ Output: p1_4.col1
+ -> Seq Scan on public.tprt_5 p1_5 (never executed)
+ Output: p1_5.col1
+ -> Seq Scan on public.tprt_6 p1_6 (never executed)
+ Output: p1_6.col1
+ -> Hash (actual rows=3 loops=1)
+ Output: p2.col1
+ Buckets: 1024 Batches: 1 Memory Usage: NkB
+ -> Append (actual rows=3 loops=1)
+ Join Partition Pruning: $1
+ -> Seq Scan on public.tprt_1 p2_1 (never executed)
+ Output: p2_1.col1
+ -> Seq Scan on public.tprt_2 p2_2 (actual rows=3 loops=1)
+ Output: p2_2.col1
+ -> Seq Scan on public.tprt_3 p2_3 (never executed)
+ Output: p2_3.col1
+ -> Seq Scan on public.tprt_4 p2_4 (never executed)
+ Output: p2_4.col1
+ -> Seq Scan on public.tprt_5 p2_5 (never executed)
+ Output: p2_5.col1
+ -> Seq Scan on public.tprt_6 p2_6 (never executed)
+ Output: p2_6.col1
+ -> Hash (actual rows=2 loops=1)
+ Output: t.col1
+ Buckets: 1024 Batches: 1 Memory Usage: NkB
+ Partition Prune: $0, $1
+ -> Seq Scan on public.tbl1 t (actual rows=2 loops=1)
+ Output: t.col1
+(43 rows)
+
+select * from tprt p1
+ inner join tprt p2 on p1.col1 = p2.col1
+ right join tbl1 t on p1.col1 = t.col1 and p2.col1 = t.col1;
+ col1 | col1 | col1
+------+------+------
+ 501 | 501 | 501
+ 505 | 505 | 505
+(2 rows)
+
drop table tbl1, tprt;
-- Test with columns defined in varying orders between each level
create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 7bf3920827..fc5982edcf 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -727,6 +727,45 @@ select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
+-- join partition pruning
+
+-- The 'Memory Usage' from the Hash node can vary between machines. Let's just
+-- replace the number with an 'N'.
+-- We need to run EXPLAIN ANALYZE because we need to see '(never executed)'
+-- notations because that's the only way to verify runtime pruning.
+create function explain_join_partition_pruning(text) returns setof text
+language plpgsql as
+$$
+declare
+ ln text;
+begin
+ for ln in
+ execute format('explain (analyze, verbose, costs off, summary off, timing off) %s',
+ $1)
+ loop
+ ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
+ return next ln;
+ end loop;
+end;
+$$;
+
+delete from tbl1;
+insert into tbl1 values (501), (505);
+analyze tbl1, tprt;
+
+set enable_nestloop = off;
+set enable_mergejoin = off;
+set enable_hashjoin = on;
+
+select explain_join_partition_pruning('
+select * from tprt p1
+ inner join tprt p2 on p1.col1 = p2.col1
+ right join tbl1 t on p1.col1 = t.col1 and p2.col1 = t.col1;');
+
+select * from tprt p1
+ inner join tprt p2 on p1.col1 = p2.col1
+ right join tbl1 t on p1.col1 = t.col1 and p2.col1 = t.col1;
+
drop table tbl1, tprt;
-- Test with columns defined in varying orders between each level
--
2.31.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Support run-time partition pruning for hash join
@ 2023-11-06 15:00 Alexander Lakhin <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Alexander Lakhin @ 2023-11-06 15:00 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: David Rowley <[email protected]>; Andy Fan <[email protected]>; pgsql-hackers
Hello Richard,
06.11.2023 06:05, Richard Guo wrote:
>
> Fixed this issue in v4.
>
Please look at a warning and an assertion failure triggered by the
following script:
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set min_parallel_table_scan_size = '1kB';
create table t1 (i int) partition by range (i);
create table t1_1 partition of t1 for values from (1) to (2);
create table t1_2 partition of t1 for values from (2) to (3);
insert into t1 values (1), (2);
create table t2(i int);
insert into t2 values (1), (2);
analyze t1, t2;
select * from t1 right join t2 on t1.i = t2.i;
2023-11-06 14:11:37.398 UTC|law|regression|6548f419.392cf5|WARNING: Join partition pruning $0 has not been performed yet.
TRAP: failed Assert("node->as_prune_state"), File: "nodeAppend.c", Line: 846, PID: 3747061
Best regards,
Alexander
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-11-06 15:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-04-21 17:33 [PATCH v1 3/4] Add a new MODE_SINGLE_QUERY to the core parser and use it in pg_parse_query. Julien Rouhaud <[email protected]>
2023-11-04 10:00 Re: Support run-time partition pruning for hash join Alexander Lakhin <[email protected]>
2023-11-06 03:05 ` Re: Support run-time partition pruning for hash join Richard Guo <[email protected]>
2023-11-06 15:00 ` Re: Support run-time partition pruning for hash join Alexander Lakhin <[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