public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v7 2/2] Add a --outdated option to reindexdb
9+ messages / 7 participants
[nested] [flat]
* [PATCH v7 2/2] Add a --outdated option to reindexdb
@ 2021-02-24 17:33 Julien Rouhaud <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Julien Rouhaud @ 2021-02-24 17:33 UTC (permalink / raw)
This uses the new OUTDATED option for REINDEX. If user asks for multiple job,
the list of tables to process will be sorted by the total size of underlying
indexes that have outdated dependency using a new
pg_index_has_outdated_dependency(regclass) SQL function.
Catversion (should be) bumped.
Author: Julien Rouhaud <[email protected]>
Reviewed-by: Michael Paquier, Mark Dilger
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
doc/src/sgml/func.sgml | 27 ++++--
src/backend/catalog/index.c | 22 +++++
src/bin/scripts/reindexdb.c | 143 ++++++++++++++++++++++++-----
src/bin/scripts/t/090_reindexdb.pl | 34 ++++++-
src/include/catalog/pg_proc.dat | 4 +
5 files changed, 198 insertions(+), 32 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9492a3c6b9..0eda6678ac 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26448,12 +26448,13 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
<para>
<xref linkend="functions-admin-index-table"/> shows the functions
- available for index maintenance tasks. (Note that these maintenance
- tasks are normally done automatically by autovacuum; use of these
- functions is only required in special cases.)
- These functions cannot be executed during recovery.
- Use of these functions is restricted to superusers and the owner
- of the given index.
+ available for index maintenance tasks. (Note that the maintenance
+ tasks performing actions on indexes are normally done automatically by
+ autovacuum; use of these functions is only required in special cases.)
+ The functions performing actions on indexes cannot be executed during
+ recovery.
+ Use of the functions performing actions on indexes is restricted to
+ superusers and the owner of the given index.
</para>
<table id="functions-admin-index-table">
@@ -26538,6 +26539,20 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
option.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_index_has_outdated_dependency</primary>
+ </indexterm>
+ <function>pg_index_has_outdated_dependency</function> ( <parameter>index</parameter> <type>regclass</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Check if the specified index has any outdated dependency. For now only
+ dependency on collations are supported.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 47e6a54149..6848e61df3 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1390,6 +1390,28 @@ do_check_index_has_outdated_collation(const ObjectAddress *otherObject,
return false;
}
+/*
+ * SQL wrapper around index_has_outdated_dependency.
+ */
+Datum
+pg_index_has_outdated_dependency(PG_FUNCTION_ARGS)
+{
+ Oid indexOid = PG_GETARG_OID(0);
+ Relation rel;
+ bool res;
+
+ rel = try_index_open(indexOid, AccessShareLock);
+
+ if (rel == NULL)
+ PG_RETURN_NULL();
+
+ res = index_has_outdated_dependency(indexOid);
+
+ relation_close(rel, AccessShareLock);
+
+ PG_RETURN_BOOL(res);
+}
+
/*
* Check whether the given index has a dependency with an outdated
* collation version.
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
index fc0681538a..d252b1454f 100644
--- a/src/bin/scripts/reindexdb.c
+++ b/src/bin/scripts/reindexdb.c
@@ -35,20 +35,23 @@ typedef enum ReindexType
static SimpleStringList *get_parallel_object_list(PGconn *conn,
ReindexType type,
SimpleStringList *user_list,
+ bool outdated,
bool echo);
static void reindex_one_database(ConnParams *cparams, ReindexType type,
SimpleStringList *user_list,
const char *progname,
bool echo, bool verbose, bool concurrently,
- int concurrentCons, const char *tablespace);
+ int concurrentCons, const char *tablespace,
+ bool outdated);
static void reindex_all_databases(ConnParams *cparams,
const char *progname, bool echo,
bool quiet, bool verbose, bool concurrently,
- int concurrentCons, const char *tablespace);
+ int concurrentCons, const char *tablespace,
+ bool outdated);
static void run_reindex_command(PGconn *conn, ReindexType type,
const char *name, bool echo, bool verbose,
bool concurrently, bool async,
- const char *tablespace);
+ const char *tablespace, bool outdated);
static void help(const char *progname);
@@ -74,6 +77,7 @@ main(int argc, char *argv[])
{"concurrently", no_argument, NULL, 1},
{"maintenance-db", required_argument, NULL, 2},
{"tablespace", required_argument, NULL, 3},
+ {"outdated", no_argument, NULL, 4},
{NULL, 0, NULL, 0}
};
@@ -95,6 +99,7 @@ main(int argc, char *argv[])
bool quiet = false;
bool verbose = false;
bool concurrently = false;
+ bool outdated = false;
SimpleStringList indexes = {NULL, NULL};
SimpleStringList tables = {NULL, NULL};
SimpleStringList schemas = {NULL, NULL};
@@ -170,6 +175,9 @@ main(int argc, char *argv[])
case 3:
tablespace = pg_strdup(optarg);
break;
+ case 4:
+ outdated = true;
+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -234,7 +242,8 @@ main(int argc, char *argv[])
cparams.dbname = maintenance_db;
reindex_all_databases(&cparams, progname, echo, quiet, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
}
else if (syscatalog)
{
@@ -253,12 +262,17 @@ main(int argc, char *argv[])
pg_log_error("cannot reindex specific index(es) and system catalogs at the same time");
exit(1);
}
-
if (concurrentCons > 1)
{
pg_log_error("cannot use multiple jobs to reindex system catalogs");
exit(1);
}
+ if (outdated)
+ {
+ pg_log_error("cannot filter indexes having outdated dependencies "
+ "and reindex system catalogs at the same time");
+ exit(1);
+ }
if (dbname == NULL)
{
@@ -274,7 +288,7 @@ main(int argc, char *argv[])
reindex_one_database(&cparams, REINDEX_SYSTEM, NULL,
progname, echo, verbose,
- concurrently, 1, tablespace);
+ concurrently, 1, tablespace, outdated);
}
else
{
@@ -304,17 +318,20 @@ main(int argc, char *argv[])
if (schemas.head != NULL)
reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
progname, echo, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
if (indexes.head != NULL)
reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
progname, echo, verbose,
- concurrently, 1, tablespace);
+ concurrently, 1, tablespace,
+ outdated);
if (tables.head != NULL)
reindex_one_database(&cparams, REINDEX_TABLE, &tables,
progname, echo, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
/*
* reindex database only if neither index nor table nor schema is
@@ -323,7 +340,8 @@ main(int argc, char *argv[])
if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
progname, echo, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
}
exit(0);
@@ -334,7 +352,7 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
SimpleStringList *user_list,
const char *progname, bool echo,
bool verbose, bool concurrently, int concurrentCons,
- const char *tablespace)
+ const char *tablespace, bool outdated)
{
PGconn *conn;
SimpleStringListCell *cell;
@@ -363,6 +381,14 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
exit(1);
}
+ if (outdated && PQserverVersion(conn) < 140000)
+ {
+ PQfinish(conn);
+ pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+ "outdated", "14");
+ exit(1);
+ }
+
if (!parallel)
{
switch (process_type)
@@ -399,14 +425,24 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
*/
if (concurrently)
pg_log_warning("cannot reindex system catalogs concurrently, skipping all");
+ else if (outdated)
+ {
+ /*
+ * The only supported kind of object that can be outdated
+ * is collation. No system catalog has any index that can
+ * depend on an outdated collation, so skip system
+ * catalogs.
+ */
+ }
else
run_reindex_command(conn, REINDEX_SYSTEM, PQdb(conn), echo,
verbose, concurrently, false,
- tablespace);
+ tablespace, outdated);
/* Build a list of relations from the database */
process_list = get_parallel_object_list(conn, process_type,
- user_list, echo);
+ user_list, outdated,
+ echo);
process_type = REINDEX_TABLE;
/* Bail out if nothing to process */
@@ -419,7 +455,8 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
/* Build a list of relations from all the schemas */
process_list = get_parallel_object_list(conn, process_type,
- user_list, echo);
+ user_list, outdated,
+ echo);
process_type = REINDEX_TABLE;
/* Bail out if nothing to process */
@@ -485,7 +522,8 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
run_reindex_command(free_slot->connection, process_type, objname,
- echo, verbose, concurrently, true, tablespace);
+ echo, verbose, concurrently, true, tablespace,
+ outdated);
cell = cell->next;
} while (cell != NULL);
@@ -510,7 +548,7 @@ finish:
static void
run_reindex_command(PGconn *conn, ReindexType type, const char *name,
bool echo, bool verbose, bool concurrently, bool async,
- const char *tablespace)
+ const char *tablespace, bool outdated)
{
const char *paren = "(";
const char *comma = ", ";
@@ -537,6 +575,12 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
sep = comma;
}
+ if (outdated)
+ {
+ appendPQExpBuffer(&sql, "%sOUTDATED", sep);
+ sep = comma;
+ }
+
if (sep != paren)
appendPQExpBufferStr(&sql, ") ");
@@ -642,7 +686,7 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
*/
static SimpleStringList *
get_parallel_object_list(PGconn *conn, ReindexType type,
- SimpleStringList *user_list, bool echo)
+ SimpleStringList *user_list, bool outdated, bool echo)
{
PQExpBufferData catalog_query;
PQExpBufferData buf;
@@ -661,16 +705,41 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
{
case REINDEX_DATABASE:
Assert(user_list == NULL);
+
appendPQExpBufferStr(&catalog_query,
"SELECT c.relname, ns.nspname\n"
" FROM pg_catalog.pg_class c\n"
" JOIN pg_catalog.pg_namespace ns"
- " ON c.relnamespace = ns.oid\n"
+ " ON c.relnamespace = ns.oid\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " JOIN pg_catalog.pg_index i"
+ " ON c.oid = i.indrelid\n"
+ " JOIN pg_catalog.pg_class ci"
+ " ON i.indexrelid = ci.oid\n");
+ }
+
+ appendPQExpBufferStr(&catalog_query,
" WHERE ns.nspname != 'pg_catalog'\n"
" AND c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
- CppAsString2(RELKIND_MATVIEW) ")\n"
- " ORDER BY c.relpages DESC;");
+ CppAsString2(RELKIND_MATVIEW) ")\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " GROUP BY c.relname, ns.nspname\n"
+ " ORDER BY pg_catalog.sum(ci.relpages)"
+ " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+ }
+ else
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " ORDER BY c.relpages DESC;");
+ }
+
break;
case REINDEX_SCHEMA:
@@ -688,7 +757,18 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
"SELECT c.relname, ns.nspname\n"
" FROM pg_catalog.pg_class c\n"
" JOIN pg_catalog.pg_namespace ns"
- " ON c.relnamespace = ns.oid\n"
+ " ON c.relnamespace = ns.oid\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " JOIN pg_catalog.pg_index i"
+ " ON c.oid = i.indrelid\n"
+ " JOIN pg_catalog.pg_class ci"
+ " ON i.indexrelid = ci.oid\n");
+ }
+
+ appendPQExpBufferStr(&catalog_query,
" WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) ")\n"
@@ -706,8 +786,20 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
appendStringLiteralConn(&catalog_query, nspname, conn);
}
- appendPQExpBufferStr(&catalog_query, ")\n"
- " ORDER BY c.relpages DESC;");
+ appendPQExpBufferStr(&catalog_query, ")\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " GROUP BY c.relname, ns.nspname\n"
+ " ORDER BY pg_catalog.sum(ci.relpages)"
+ " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+ }
+ else
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " ORDER BY c.relpages DESC;");
+ }
}
break;
@@ -755,7 +847,7 @@ static void
reindex_all_databases(ConnParams *cparams,
const char *progname, bool echo, bool quiet, bool verbose,
bool concurrently, int concurrentCons,
- const char *tablespace)
+ const char *tablespace, bool outdated)
{
PGconn *conn;
PGresult *result;
@@ -779,7 +871,7 @@ reindex_all_databases(ConnParams *cparams,
reindex_one_database(cparams, REINDEX_DATABASE, NULL,
progname, echo, verbose, concurrently,
- concurrentCons, tablespace);
+ concurrentCons, tablespace, outdated);
}
PQclear(result);
@@ -798,6 +890,7 @@ help(const char *progname)
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n"));
+ printf(_(" --outdated only process indexes having outdated dependencies\n"));
printf(_(" -q, --quiet don't write any messages\n"));
printf(_(" -s, --system reindex system catalogs\n"));
printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_reindexdb.pl
index 159b637230..91fd602041 100644
--- a/src/bin/scripts/t/090_reindexdb.pl
+++ b/src/bin/scripts/t/090_reindexdb.pl
@@ -3,7 +3,7 @@ use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 58;
+use Test::More tests => 70;
program_help_ok('reindexdb');
program_version_ok('reindexdb');
@@ -174,6 +174,9 @@ $node->command_fails(
$node->command_fails(
[ 'reindexdb', '-j', '2', '-i', 'i1', 'postgres' ],
'parallel reindexdb cannot process indexes');
+$node->command_fails(
+ [ 'reindexdb', '-s', '--outdated' ],
+ 'cannot reindex system catalog and filter indexes having outdated dependencies');
$node->issues_sql_like(
[ 'reindexdb', '-j', '2', 'postgres' ],
qr/statement:\ REINDEX SYSTEM postgres;
@@ -196,3 +199,32 @@ $node->command_checks_all(
qr/^reindexdb: warning: cannot reindex system catalogs concurrently, skipping all/s
],
'parallel reindexdb for system with --concurrently skips catalogs');
+
+# Temporarily downgrade client-min-message to get the no-op report
+$ENV{PGOPTIONS} = '--client-min-messages=NOTICE';
+$node->command_checks_all(
+ [ 'reindexdb', '--outdated', '-v', '-t', 's1.t1', 'postgres' ],
+ 0,
+ [qr/^$/],
+ [qr/table "t1" has no indexes to reindex/],
+ 'verbose reindexdb for outdated dependencies on a specific table reports no-op tables');
+
+$node->command_checks_all(
+ [ 'reindexdb', '--outdated', '-v', '-d', 'postgres' ],
+ 0,
+ [qr/^$/],
+ [qr/index "t2_id_idx" has no outdated dependency/],
+ 'verbose reindexdb for outdated dependencies database wide reports all ignored indexes');
+$node->command_checks_all(
+ [ 'reindexdb', '--outdated', '-v', '-j', '2', '-d', 'postgres' ],
+ 0,
+ [qr/^$/],
+ [qr/table "t1" has no indexes to reindex/],
+ 'parallel verbose reindexdb for outdated dependencies database wide reports no-op tables');
+
+# Switch back to WARNING client-min-message
+$ENV{PGOPTIONS} = '--client-min-messages=WARNING';
+$node->issues_sql_like(
+ [ 'reindexdb', '--outdated', '-t', 's1.t1', 'postgres' ],
+ qr/.*statement: REINDEX \(OUTDATED\) TABLE s1\.t1;/,
+ 'reindexdb for outdated dependencies specify the OUTDATED keyword');
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 93393fcfd4..911c12ee2c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -949,6 +949,10 @@
proname => 'pg_indexam_has_property', provolatile => 's',
prorettype => 'bool', proargtypes => 'oid text',
prosrc => 'pg_indexam_has_property' },
+{ oid => '8102', descr => 'test property of an index',
+ proname => 'pg_index_has_outdated_dependency', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'regclass',
+ prosrc => 'pg_index_has_outdated_dependency' },
{ oid => '637', descr => 'test property of an index',
proname => 'pg_index_has_property', provolatile => 's', prorettype => 'bool',
proargtypes => 'regclass text', prosrc => 'pg_index_has_property' },
--
2.30.1
--rpt4sswdwkhtp4fw--
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2022-01-23 01:25 James Coleman <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: James Coleman @ 2022-01-23 01:25 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
On Fri, Jan 21, 2022 at 3:20 PM Robert Haas <[email protected]> wrote:
>
> On Fri, Jan 14, 2022 at 2:25 PM James Coleman <[email protected]> wrote:
> > I've been chewing on this a bit, and I was about to go re-read the
> > code and see how easy it'd be to do exactly what you're suggesting in
> > generate_gather_paths() (and verifying it doesn't need to happen in
> > other places). However there's one (I think large) gotcha with that
> > approach (assuming it otherwise makes sense): it means we do
> > unnecessary work. In the current patch series we only need to recheck
> > parallel safety if we're in a situation where we might actually
> > benefit from doing that work (namely when we have a correlated
> > subquery we might otherwise be able to execute in a parallel plan). If
> > we don't track that status we'd have to recheck the full parallel
> > safety of the path for all paths -- even without correlated
> > subqueries.
>
> I don't think there's an intrinsic problem with the idea of making a
> tentative determination about parallel safety and then refining it
> later, but I'm not sure why you think it would be a lot of work to
> figure this out at the point where we generate gather paths. I think
> it's just a matter of testing whether the set of parameters that the
> path needs as input is the empty set. It may be that neither extParam
> nor allParam are precisely that thing, but I think both are very
> close, and it seems to me that there's no theoretical reason why we
> can't know for every path the set of inputs that it requires "from the
> outside."
As I understand it now (not sure I realized this before) you're
suggesting that *even when there are required params* marking it as
parallel safe, and then checking the params for parallel safety later.
From a purely theoretical perspective that seemed reasonable, so I
took a pass at that approach.
The first, and likely most interesting, thing I discovered was that
the vast majority of what the patch accomplishes it does so not via
the delayed params safety checking but rather via the required outer
relids checks I'd added to generate_useful_gather_paths.
For that to happen I did have to mark PARAM_EXEC params as presumed
parallel safe. That means that parallel_safe now doesn't strictly mean
"parallel safe in the current context" but "parallel safe as long as
any params are provided". That's a real change, but probably
acceptable as long as a project policy decision is made in that
direction.
There are a few concerns I have (and I'm not sure what level they rise to):
1. From what I can tell we don't have access on a path to the set of
params required by that path (I believe this is what Tom was
referencing in his sister reply at this point in the thread). That
means we have to rely on checking that the required outer relids are
provided by the current query level. I'm not quite sure yet whether or
not that guarantees (or if the rest of the path construction logic
guarantees for us) that the params provided by the outer rel are used
in a correlated way that isn't shared across workers. And because we
don't have the param information available we can't add additional
checks (that I can tell) to verify that.
2. Are we excluding any paths (by having one that will always be
invalid win the cost comparisons in add_partial_path)? I suppose this
danger actually exists in the previous version of the patch as well,
and I don't actually have any examples of this being a problem. Also
maybe this can only be a problem if (1) reveals a bug.
3. The new patch series actually ends up allowing parallelization of
correlated params in a few more places than the original patch series.
From what I can tell all of the cases are in fact safe to execute in
parallel, which, if true, means this is a feature not a concern. The
changed query plans fall into two categories: a.) putting a gather
inside a subplan and b.) correlated param usages in a subquery scan
path on the inner side of a join. I've separated out those specific
changes in a separate patch to make it easier to tell which ones I'm
referencing.
On the other hand this is a dramatically simpler patch series.
Assuming the approach is sound, it should much easier to maintain than
the previous version.
The final patch in the series is a set of additional checks I could
imagine to try to be more explicit, but at least in the current test
suite there isn't anything at all they affect.
Does this look at least somewhat more like what you'd envisionsed
(granting the need to squint hard given the relids checks instead of
directly checking params)?
Thanks,
James Coleman
Attachments:
[text/x-patch] v4-0001-Allow-parallel-LATERAL-subqueries-with-LIMIT-OFFS.patch (3.5K, ../../CAAaqYe8m0DHUWk7gLKb_C4abTD4nMkU26ErE=ahow4zNMZbzPQ@mail.gmail.com/2-v4-0001-Allow-parallel-LATERAL-subqueries-with-LIMIT-OFFS.patch)
download | inline diff:
From 6596bb6909b9dfaadd6e6e834be4032e7903c4e9 Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Mon, 30 Nov 2020 11:36:35 -0500
Subject: [PATCH v4 1/4] Allow parallel LATERAL subqueries with LIMIT/OFFSET
The code that determined whether or not a rel should be considered for
parallel query excluded subqueries with LIMIT/OFFSET. That's correct in
the general case: as the comment notes that'd mean we have to guarantee
ordering (and claims it's not worth checking that) for results to be
consistent across workers. However there's a simpler case that hasn't
been considered: LATERAL subqueries with LIMIT/OFFSET don't fall under
the same reasoning since they're executed (when not converted to a JOIN)
per tuple anyway, so consistency of results across workers isn't a
factor.
---
src/backend/optimizer/path/allpaths.c | 4 +++-
src/test/regress/expected/select_parallel.out | 15 +++++++++++++++
src/test/regress/sql/select_parallel.sql | 6 ++++++
3 files changed, 24 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 169b1d53fc..6a581e20fa 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -682,11 +682,13 @@ set_rel_consider_parallel(PlannerInfo *root, RelOptInfo *rel,
* inconsistent results at the top-level. (In some cases, where
* the result is ordered, we could relax this restriction. But it
* doesn't currently seem worth expending extra effort to do so.)
+ * LATERAL is an exception: LIMIT/OFFSET is safe to execute within
+ * workers since the sub-select is executed per tuple
*/
{
Query *subquery = castNode(Query, rte->subquery);
- if (limit_needed(subquery))
+ if (!rte->lateral && limit_needed(subquery))
return;
}
break;
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 4ea1aa7dfd..2303f70d6e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -1040,6 +1040,21 @@ explain (costs off)
Filter: (stringu1 ~~ '%AAAA'::text)
(11 rows)
+-- ...unless it's LATERAL
+savepoint settings;
+set parallel_tuple_cost=0;
+explain (costs off) select t.unique1 from tenk1 t
+join lateral (select t.unique1 from tenk1 offset 0) l on true;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Gather
+ Workers Planned: 4
+ -> Nested Loop
+ -> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
+ -> Index Only Scan using tenk1_hundred on tenk1
+(5 rows)
+
+rollback to savepoint settings;
-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 1;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index f924731248..019e17e751 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -390,6 +390,12 @@ explain (costs off, verbose)
explain (costs off)
select * from tenk1 a where two in
(select two from tenk1 b where stringu1 like '%AAAA' limit 3);
+-- ...unless it's LATERAL
+savepoint settings;
+set parallel_tuple_cost=0;
+explain (costs off) select t.unique1 from tenk1 t
+join lateral (select t.unique1 from tenk1 offset 0) l on true;
+rollback to savepoint settings;
-- to increase the parallel query test coverage
SAVEPOINT settings;
--
2.17.1
[text/x-patch] v4-0003-Changed-queries.patch (8.1K, ../../CAAaqYe8m0DHUWk7gLKb_C4abTD4nMkU26ErE=ahow4zNMZbzPQ@mail.gmail.com/3-v4-0003-Changed-queries.patch)
download | inline diff:
From 200c4ed4118f92014253b49faa41a76ecec84c96 Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Sat, 22 Jan 2022 18:34:54 -0500
Subject: [PATCH v4 3/4] Changed queries
---
src/test/regress/expected/partition_prune.out | 104 +++++++++---------
src/test/regress/expected/select_parallel.out | 26 +++--
2 files changed, 69 insertions(+), 61 deletions(-)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c77..5c45f9c0a5 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1284,60 +1284,64 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x;
--
-- pruning won't work for mc3p, because some keys are Params
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
- QUERY PLAN
------------------------------------------------------------------------
- Nested Loop
- -> Append
- -> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
- -> Seq Scan on mc2p2 t1_2
- Filter: (a = 1)
- -> Seq Scan on mc2p_default t1_3
- Filter: (a = 1)
- -> Aggregate
- -> Append
- -> Seq Scan on mc3p0 t2_1
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p1 t2_2
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p2 t2_3
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p3 t2_4
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p4 t2_5
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p5 t2_6
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p6 t2_7
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p7 t2_8
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p_default t2_9
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
-(28 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Nested Loop
+ -> Parallel Append
+ -> Parallel Seq Scan on mc2p1 t1_1
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p2 t1_2
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p_default t1_3
+ Filter: (a = 1)
+ -> Aggregate
+ -> Append
+ -> Seq Scan on mc3p0 t2_1
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1 t2_2
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p2 t2_3
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p3 t2_4
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p4 t2_5
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p5 t2_6
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p6 t2_7
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p7 t2_8
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default t2_9
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+(30 rows)
-- pruning should work fine, because values for a prefix of keys (a, b) are
-- available
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1;
- QUERY PLAN
------------------------------------------------------------------------
- Nested Loop
- -> Append
- -> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
- -> Seq Scan on mc2p2 t1_2
- Filter: (a = 1)
- -> Seq Scan on mc2p_default t1_3
- Filter: (a = 1)
- -> Aggregate
- -> Append
- -> Seq Scan on mc3p0 t2_1
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p1 t2_2
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p_default t2_3
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
-(16 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Nested Loop
+ -> Parallel Append
+ -> Parallel Seq Scan on mc2p1 t1_1
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p2 t1_2
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p_default t1_3
+ Filter: (a = 1)
+ -> Aggregate
+ -> Append
+ -> Seq Scan on mc3p0 t2_1
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1 t2_2
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default t2_3
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+(18 rows)
-- also here, because values for all keys are provided
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 124fe9fec5..9bb60c2c1e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -137,8 +137,8 @@ create table part_pa_test_p2 partition of part_pa_test for values from (0) to (m
explain (costs off)
select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a)))
from part_pa_test pa2;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Aggregate
-> Gather
Workers Planned: 3
@@ -148,12 +148,14 @@ explain (costs off)
SubPlan 2
-> Result
SubPlan 1
- -> Append
- -> Seq Scan on part_pa_test_p1 pa1_1
- Filter: (a = pa2.a)
- -> Seq Scan on part_pa_test_p2 pa1_2
- Filter: (a = pa2.a)
-(14 rows)
+ -> Gather
+ Workers Planned: 3
+ -> Parallel Append
+ -> Parallel Seq Scan on part_pa_test_p1 pa1_1
+ Filter: (a = pa2.a)
+ -> Parallel Seq Scan on part_pa_test_p2 pa1_2
+ Filter: (a = pa2.a)
+(16 rows)
drop table part_pa_test;
-- test with leader participation disabled
@@ -1330,8 +1332,10 @@ SELECT 1 FROM tenk1_vw_sec
-> Parallel Index Only Scan using tenk1_unique1 on tenk1
SubPlan 1
-> Aggregate
- -> Seq Scan on int4_tbl
- Filter: (f1 < tenk1_vw_sec.unique1)
-(9 rows)
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Seq Scan on int4_tbl
+ Filter: (f1 < tenk1_vw_sec.unique1)
+(11 rows)
rollback;
--
2.17.1
[text/x-patch] v4-0004-Possible-additional-checks.patch (3.1K, ../../CAAaqYe8m0DHUWk7gLKb_C4abTD4nMkU26ErE=ahow4zNMZbzPQ@mail.gmail.com/4-v4-0004-Possible-additional-checks.patch)
download | inline diff:
From c11d38b8a88e8f7a46528712fc9341de34171f8d Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Sat, 22 Jan 2022 17:33:23 -0500
Subject: [PATCH v4 4/4] Possible additional checks
---
src/backend/optimizer/path/allpaths.c | 33 ++++++++++++++++++++++-----
1 file changed, 27 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 776f002054..7e30824320 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -2682,11 +2682,16 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
ListCell *lc;
double rows;
double *rowsp = NULL;
+ Relids required_outer = rel->lateral_relids;
/* If there are no partial paths, there's nothing to do here. */
if (rel->partial_pathlist == NIL)
return;
+ if (!bms_is_subset(required_outer, rel->relids))
+ return;
+
+
/* Should we override the rel's rowcount estimate? */
if (override_rows)
rowsp = &rows;
@@ -2697,12 +2702,16 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
* of partial_pathlist because of the way add_partial_path works.
*/
cheapest_partial_path = linitial(rel->partial_pathlist);
- rows =
- cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
- simple_gather_path = (Path *)
- create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
- rel->lateral_relids, rowsp);
- add_path(rel, simple_gather_path);
+ if (cheapest_partial_path->param_info == NULL ||
+ bms_is_subset(cheapest_partial_path->param_info->ppi_req_outer, rel->relids))
+ {
+ rows =
+ cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
+ simple_gather_path = (Path *)
+ create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
+ rel->lateral_relids, rowsp);
+ add_path(rel, simple_gather_path);
+ }
/*
* For each useful ordering, we can consider an order-preserving Gather
@@ -2716,6 +2725,10 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
if (subpath->pathkeys == NIL)
continue;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
rows = subpath->rows * subpath->parallel_workers;
path = create_gather_merge_path(root, rel, subpath, rel->reltarget,
subpath->pathkeys, rel->lateral_relids, rowsp);
@@ -2888,6 +2901,10 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
{
Path *tmp;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
tmp = (Path *) create_sort_path(root,
rel,
subpath,
@@ -2916,6 +2933,10 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
{
Path *tmp;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
/*
* We should have already excluded pathkeys of length 1
* because then presorted_keys > 0 would imply is_sorted was
--
2.17.1
[text/x-patch] v4-0002-Parallelize-correlated-subqueries.patch (18.6K, ../../CAAaqYe8m0DHUWk7gLKb_C4abTD4nMkU26ErE=ahow4zNMZbzPQ@mail.gmail.com/5-v4-0002-Parallelize-correlated-subqueries.patch)
download | inline diff:
From ba785f2abca7c9f5199f0fc27c3b71f6d1d8010b Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Fri, 21 Jan 2022 22:38:46 -0500
Subject: [PATCH v4 2/4] Parallelize correlated subqueries
When params are provided at the current query level (i.e., are generated
within a single worker and not shared across workers) we can safely
execute these in parallel.
Alternative approach using just relids subset check
---
doc/src/sgml/parallel.sgml | 3 +-
src/backend/optimizer/path/allpaths.c | 18 ++-
src/backend/optimizer/path/joinpath.c | 16 ++-
src/backend/optimizer/util/clauses.c | 3 +
src/backend/optimizer/util/pathnode.c | 2 +
src/backend/utils/misc/guc.c | 1 +
src/include/nodes/pathnodes.h | 2 +-
.../regress/expected/incremental_sort.out | 28 ++--
src/test/regress/expected/select_parallel.out | 125 ++++++++++++++++++
src/test/regress/sql/select_parallel.sql | 25 ++++
10 files changed, 197 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/parallel.sgml b/doc/src/sgml/parallel.sgml
index 13479d7e5e..2d924dd2ac 100644
--- a/doc/src/sgml/parallel.sgml
+++ b/doc/src/sgml/parallel.sgml
@@ -517,7 +517,8 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
<listitem>
<para>
- Plan nodes that reference a correlated <literal>SubPlan</literal>.
+ Plan nodes that reference a correlated <literal>SubPlan</literal> where
+ the result is shared between workers.
</para>
</listitem>
</itemizedlist>
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 6a581e20fa..776f002054 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -556,7 +556,8 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
* (see grouping_planner).
*/
if (rel->reloptkind == RELOPT_BASEREL &&
- bms_membership(root->all_baserels) != BMS_SINGLETON)
+ bms_membership(root->all_baserels) != BMS_SINGLETON
+ && (rel->subplan_params == NIL || rte->rtekind != RTE_SUBQUERY))
generate_useful_gather_paths(root, rel, false);
/* Now find the cheapest of the paths for this rel */
@@ -2700,7 +2701,7 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
simple_gather_path = (Path *)
create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
- NULL, rowsp);
+ rel->lateral_relids, rowsp);
add_path(rel, simple_gather_path);
/*
@@ -2717,7 +2718,7 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
rows = subpath->rows * subpath->parallel_workers;
path = create_gather_merge_path(root, rel, subpath, rel->reltarget,
- subpath->pathkeys, NULL, rowsp);
+ subpath->pathkeys, rel->lateral_relids, rowsp);
add_path(rel, &path->path);
}
}
@@ -2819,11 +2820,15 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
double *rowsp = NULL;
List *useful_pathkeys_list = NIL;
Path *cheapest_partial_path = NULL;
+ Relids required_outer = rel->lateral_relids;
/* If there are no partial paths, there's nothing to do here. */
if (rel->partial_pathlist == NIL)
return;
+ if (!bms_is_subset(required_outer, rel->relids))
+ return;
+
/* Should we override the rel's rowcount estimate? */
if (override_rows)
rowsp = &rows;
@@ -2895,7 +2900,7 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
tmp,
rel->reltarget,
tmp->pathkeys,
- NULL,
+ required_outer,
rowsp);
add_path(rel, &path->path);
@@ -2929,7 +2934,7 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
tmp,
rel->reltarget,
tmp->pathkeys,
- NULL,
+ required_outer,
rowsp);
add_path(rel, &path->path);
@@ -3108,7 +3113,8 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
/*
* Except for the topmost scan/join rel, consider gathering
* partial paths. We'll do the same for the topmost scan/join rel
- * once we know the final targetlist (see grouping_planner).
+ * once we know the final targetlist (see
+ * apply_scanjoin_target_to_paths).
*/
if (lev < levels_needed)
generate_useful_gather_paths(root, rel, false);
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index f96fc9fd28..e85b5449ea 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -1791,16 +1791,24 @@ match_unsorted_outer(PlannerInfo *root,
* partial path and the joinrel is parallel-safe. However, we can't
* handle JOIN_UNIQUE_OUTER, because the outer path will be partial, and
* therefore we won't be able to properly guarantee uniqueness. Nor can
- * we handle joins needing lateral rels, since partial paths must not be
- * parameterized. Similarly, we can't handle JOIN_FULL and JOIN_RIGHT,
- * because they can produce false null extended rows.
+ * we handle JOIN_FULL and JOIN_RIGHT, because they can produce false null
+ * extended rows.
+ *
+ * Partial paths may only have parameters in limited cases
+ * where the parameterization is fully satisfied without sharing state
+ * between workers, so we only allow lateral rels on inputs to the join
+ * if the resulting join contains no lateral rels, the inner rel's laterals
+ * are fully satisfied by the outer rel, and the outer rel doesn't depend
+ * on the inner rel to produce any laterals.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
outerrel->partial_pathlist != NIL &&
- bms_is_empty(joinrel->lateral_relids))
+ bms_is_empty(joinrel->lateral_relids) &&
+ bms_is_subset(innerrel->lateral_relids, outerrel->relids) &&
+ (bms_is_empty(outerrel->lateral_relids) || !bms_is_subset(outerrel->lateral_relids, innerrel->relids)))
{
if (nestjoinOK)
consider_parallel_nestloop(root, joinrel, outerrel, innerrel,
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a707dc9f26..f0002f6887 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -822,6 +822,9 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
if (param->paramkind == PARAM_EXTERN)
return false;
+ if (param->paramkind == PARAM_EXEC)
+ return false;
+
if (param->paramkind != PARAM_EXEC ||
!list_member_int(context->safe_param_ids, param->paramid))
{
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 5c32c96b71..144b2c485d 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -2418,6 +2418,8 @@ create_nestloop_path(PlannerInfo *root,
NestPath *pathnode = makeNode(NestPath);
Relids inner_req_outer = PATH_REQ_OUTER(inner_path);
+ /* TODO: Assert lateral relids subset safety? */
+
/*
* If the inner path is parameterized by the outer, we must drop any
* restrict_clauses that are due to be moved into the inner path. We have
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index effb9d03a0..52cd3512b3 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -58,6 +58,7 @@
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/geqo.h"
#include "optimizer/optimizer.h"
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 1f33fe13c1..75681d6fb9 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2478,7 +2478,7 @@ typedef struct MinMaxAggInfo
* for conflicting purposes.
*
* In addition, PARAM_EXEC slots are assigned for Params representing outputs
- * from subplans (values that are setParam items for those subplans). These
+ * from subplans (values that are setParam items for those subplans). [TODO: is this true, or only for init plans?] These
* IDs need not be tracked via PlannerParamItems, since we do not need any
* duplicate-elimination nor later processing of the represented expressions.
* Instead, we just record the assignment of the slot number by appending to
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 545e301e48..8f9ca05e60 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -1614,16 +1614,16 @@ from tenk1 t, generate_series(1, 1000);
QUERY PLAN
---------------------------------------------------------------------------------
Unique
- -> Sort
- Sort Key: t.unique1, ((SubPlan 1))
- -> Gather
- Workers Planned: 2
+ -> Gather Merge
+ Workers Planned: 2
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan 1))
-> Nested Loop
-> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
-> Function Scan on generate_series
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
(11 rows)
explain (costs off) select
@@ -1633,16 +1633,16 @@ from tenk1 t, generate_series(1, 1000)
order by 1, 2;
QUERY PLAN
---------------------------------------------------------------------------
- Sort
- Sort Key: t.unique1, ((SubPlan 1))
- -> Gather
- Workers Planned: 2
+ Gather Merge
+ Workers Planned: 2
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan 1))
-> Nested Loop
-> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
-> Function Scan on generate_series
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
(10 rows)
-- Parallel sort but with expression not available until the upper rel.
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 2303f70d6e..124fe9fec5 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -311,6 +311,131 @@ select count(*) from tenk1 where (two, four) not in
10000
(1 row)
+-- test parallel plans for queries containing correlated subplans
+-- where the subplan only needs params available from the current
+-- worker's scan.
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t, generate_series(1, 10);
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Gather
+ Output: ((SubPlan 1))
+ Workers Planned: 4
+ -> Nested Loop
+ Output: (SubPlan 1)
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ -> Function Scan on pg_catalog.generate_series
+ Output: generate_series.generate_series
+ Function Call: generate_series(1, 10)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(14 rows)
+
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
+ Output: ((SubPlan 1))
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(9 rows)
+
+explain (analyze, costs off, summary off, verbose, timing off) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t
+ limit 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Limit (actual rows=1 loops=1)
+ Output: ((SubPlan 1))
+ -> Gather (actual rows=1 loops=1)
+ Output: ((SubPlan 1))
+ Workers Planned: 4
+ Workers Launched: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t (actual rows=1 loops=5)
+ Output: (SubPlan 1)
+ Heap Fetches: 0
+ Worker 0: actual rows=1 loops=1
+ Worker 1: actual rows=1 loops=1
+ Worker 2: actual rows=1 loops=1
+ Worker 3: actual rows=1 loops=1
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1 (actual rows=1 loops=5)
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+ Heap Fetches: 0
+ Worker 0: actual rows=1 loops=1
+ Worker 1: actual rows=1 loops=1
+ Worker 2: actual rows=1 loops=1
+ Worker 3: actual rows=1 loops=1
+(22 rows)
+
+explain (costs off, verbose) select t.unique1
+ from tenk1 t
+ where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
+ Output: t.unique1
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ Filter: (t.unique1 = (SubPlan 1))
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(10 rows)
+
+explain (costs off, verbose) select *
+ from tenk1 t
+ order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Merge
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
+ Workers Planned: 4
+ -> Sort
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
+ Sort Key: ((SubPlan 1))
+ -> Parallel Seq Scan on public.tenk1 t
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(12 rows)
+
+-- test subplan in join/lateral join
+explain (costs off, verbose, timing off) select t.unique1, l.*
+ from tenk1 t
+ join lateral (
+ select (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1 offset 0)
+ ) l on true;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
+ Output: t.unique1, ((SubPlan 1))
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1, (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(9 rows)
+
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 019e17e751..c49799b6d4 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -111,6 +111,31 @@ explain (costs off)
(select hundred, thousand from tenk2 where thousand > 100);
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
+-- test parallel plans for queries containing correlated subplans
+-- where the subplan only needs params available from the current
+-- worker's scan.
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t, generate_series(1, 10);
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t;
+explain (analyze, costs off, summary off, verbose, timing off) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t
+ limit 1;
+explain (costs off, verbose) select t.unique1
+ from tenk1 t
+ where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+explain (costs off, verbose) select *
+ from tenk1 t
+ order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+-- test subplan in join/lateral join
+explain (costs off, verbose, timing off) select t.unique1, l.*
+ from tenk1 t
+ join lateral (
+ select (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1 offset 0)
+ ) l on true;
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
--
2.17.1
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2022-03-22 00:48 Andres Freund <[email protected]>
parent: James Coleman <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Andres Freund @ 2022-03-22 00:48 UTC (permalink / raw)
To: James Coleman <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
Hi,
On 2022-01-22 20:25:19 -0500, James Coleman wrote:
> On the other hand this is a dramatically simpler patch series.
> Assuming the approach is sound, it should much easier to maintain than
> the previous version.
>
> The final patch in the series is a set of additional checks I could
> imagine to try to be more explicit, but at least in the current test
> suite there isn't anything at all they affect.
>
> Does this look at least somewhat more like what you'd envisionsed
> (granting the need to squint hard given the relids checks instead of
> directly checking params)?
This fails on freebsd (so likely a timing issue): https://cirrus-ci.com/task/4758411492458496?logs=test_world#L2225
Marked as waiting on author.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2022-08-02 20:57 Jacob Champion <[email protected]>
parent: Andres Freund <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Jacob Champion @ 2022-08-02 20:57 UTC (permalink / raw)
To: Andres Freund <[email protected]>; James Coleman <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
This entry has been waiting on author input for a while (our current
threshold is roughly two weeks), so I've marked it Returned with
Feedback.
Once you think the patchset is ready for review again, you (or any
interested party) can resurrect the patch entry by visiting
https://commitfest.postgresql.org/38/3246/
and changing the status to "Needs Review", and then changing the
status again to "Move to next CF". (Don't forget the second step;
hopefully we will have streamlined this in the near future!)
Thanks,
--Jacob
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2022-09-27 02:56 James Coleman <[email protected]>
parent: Andres Freund <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: James Coleman @ 2022-09-27 02:56 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
On Mon, Mar 21, 2022 at 8:48 PM Andres Freund <[email protected]> wrote:
>
> Hi,
>
> On 2022-01-22 20:25:19 -0500, James Coleman wrote:
> > On the other hand this is a dramatically simpler patch series.
> > Assuming the approach is sound, it should much easier to maintain than
> > the previous version.
> >
> > The final patch in the series is a set of additional checks I could
> > imagine to try to be more explicit, but at least in the current test
> > suite there isn't anything at all they affect.
> >
> > Does this look at least somewhat more like what you'd envisionsed
> > (granting the need to squint hard given the relids checks instead of
> > directly checking params)?
>
> This fails on freebsd (so likely a timing issue): https://cirrus-ci.com/task/4758411492458496?logs=test_world#L2225
>
> Marked as waiting on author.
I've finally gotten around to checking this out, and the issue was an
"explain analyze" test that had actual loops different on FreeBSD.
There doesn't seem to be a way to disable loop output, but instead of
processing the explain output with e.g. a function (as we do some
other places) to remove the offending and unnecessary output I've just
removed the "analyze" (as I don't believe it was actually necessary).
Attached is an updated patch series. In this version I've removed the
"parallelize some subqueries with limit" patch since discussion is
proceeding in the spun off thread. The first patch adds additional
tests so that you can see how those new tests change with the code
changes in the 2nd patch in the series. As before the final patch in
the series includes changes where we may also want to verify
correctness but don't have a test demonstrating the need.
Thanks,
James Coleman
Attachments:
[application/octet-stream] v5-0002-Parallelize-correlated-subqueries.patch (25.2K, ../../CAAaqYe9hJ=GzcaVSH82e8o6edr3bkYkauhk_yLiGVbF=8xss1w@mail.gmail.com/2-v5-0002-Parallelize-correlated-subqueries.patch)
download | inline diff:
From 07ccd5b8d1776b5109c54d20ed3dcaef22d752f9 Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Fri, 21 Jan 2022 22:38:46 -0500
Subject: [PATCH v5 2/3] Parallelize correlated subqueries
When params are provided at the current query level (i.e., are generated
within a single worker and not shared across workers) we can safely
execute these in parallel.
Alternative approach using just relids subset check
---
doc/src/sgml/parallel.sgml | 3 +-
src/backend/optimizer/path/allpaths.c | 18 ++-
src/backend/optimizer/path/joinpath.c | 16 ++-
src/backend/optimizer/util/clauses.c | 3 +
src/backend/optimizer/util/pathnode.c | 2 +
src/include/nodes/pathnodes.h | 2 +-
.../regress/expected/incremental_sort.out | 28 ++--
src/test/regress/expected/partition_prune.out | 104 +++++++-------
src/test/regress/expected/select_parallel.out | 128 ++++++++++--------
9 files changed, 169 insertions(+), 135 deletions(-)
diff --git a/doc/src/sgml/parallel.sgml b/doc/src/sgml/parallel.sgml
index c37fb67065..d44325dd89 100644
--- a/doc/src/sgml/parallel.sgml
+++ b/doc/src/sgml/parallel.sgml
@@ -517,7 +517,8 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
<listitem>
<para>
- Plan nodes that reference a correlated <literal>SubPlan</literal>.
+ Plan nodes that reference a correlated <literal>SubPlan</literal> where
+ the result is shared between workers.
</para>
</listitem>
</itemizedlist>
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 8fc28007f5..e1ad9ae372 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -558,7 +558,8 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
* the final scan/join targetlist is available (see grouping_planner).
*/
if (rel->reloptkind == RELOPT_BASEREL &&
- !bms_equal(rel->relids, root->all_baserels))
+ !bms_equal(rel->relids, root->all_baserels)
+ && (rel->subplan_params == NIL || rte->rtekind != RTE_SUBQUERY))
generate_useful_gather_paths(root, rel, false);
/* Now find the cheapest of the paths for this rel */
@@ -3025,7 +3026,7 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
simple_gather_path = (Path *)
create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
- NULL, rowsp);
+ rel->lateral_relids, rowsp);
add_path(rel, simple_gather_path);
/*
@@ -3042,7 +3043,7 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
rows = subpath->rows * subpath->parallel_workers;
path = create_gather_merge_path(root, rel, subpath, rel->reltarget,
- subpath->pathkeys, NULL, rowsp);
+ subpath->pathkeys, rel->lateral_relids, rowsp);
add_path(rel, &path->path);
}
}
@@ -3144,11 +3145,15 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
double *rowsp = NULL;
List *useful_pathkeys_list = NIL;
Path *cheapest_partial_path = NULL;
+ Relids required_outer = rel->lateral_relids;
/* If there are no partial paths, there's nothing to do here. */
if (rel->partial_pathlist == NIL)
return;
+ if (!bms_is_subset(required_outer, rel->relids))
+ return;
+
/* Should we override the rel's rowcount estimate? */
if (override_rows)
rowsp = &rows;
@@ -3220,7 +3225,7 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
tmp,
rel->reltarget,
tmp->pathkeys,
- NULL,
+ required_outer,
rowsp);
add_path(rel, &path->path);
@@ -3254,7 +3259,7 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
tmp,
rel->reltarget,
tmp->pathkeys,
- NULL,
+ required_outer,
rowsp);
add_path(rel, &path->path);
@@ -3433,7 +3438,8 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
/*
* Except for the topmost scan/join rel, consider gathering
* partial paths. We'll do the same for the topmost scan/join rel
- * once we know the final targetlist (see grouping_planner).
+ * once we know the final targetlist (see
+ * apply_scanjoin_target_to_paths).
*/
if (!bms_equal(rel->relids, root->all_baserels))
generate_useful_gather_paths(root, rel, false);
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 2a3f0ab7bf..21606f6e59 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -1791,16 +1791,24 @@ match_unsorted_outer(PlannerInfo *root,
* partial path and the joinrel is parallel-safe. However, we can't
* handle JOIN_UNIQUE_OUTER, because the outer path will be partial, and
* therefore we won't be able to properly guarantee uniqueness. Nor can
- * we handle joins needing lateral rels, since partial paths must not be
- * parameterized. Similarly, we can't handle JOIN_FULL and JOIN_RIGHT,
- * because they can produce false null extended rows.
+ * we handle JOIN_FULL and JOIN_RIGHT, because they can produce false null
+ * extended rows.
+ *
+ * Partial paths may only have parameters in limited cases
+ * where the parameterization is fully satisfied without sharing state
+ * between workers, so we only allow lateral rels on inputs to the join
+ * if the resulting join contains no lateral rels, the inner rel's laterals
+ * are fully satisfied by the outer rel, and the outer rel doesn't depend
+ * on the inner rel to produce any laterals.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
outerrel->partial_pathlist != NIL &&
- bms_is_empty(joinrel->lateral_relids))
+ bms_is_empty(joinrel->lateral_relids) &&
+ bms_is_subset(innerrel->lateral_relids, outerrel->relids) &&
+ (bms_is_empty(outerrel->lateral_relids) || !bms_is_subset(outerrel->lateral_relids, innerrel->relids)))
{
if (nestjoinOK)
consider_parallel_nestloop(root, joinrel, outerrel, innerrel,
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index bf3a7cae60..11bab7fa7e 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -822,6 +822,9 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
if (param->paramkind == PARAM_EXTERN)
return false;
+ if (param->paramkind == PARAM_EXEC)
+ return false;
+
if (param->paramkind != PARAM_EXEC ||
!list_member_int(context->safe_param_ids, param->paramid))
{
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index e10561d843..e8309342f0 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -2437,6 +2437,8 @@ create_nestloop_path(PlannerInfo *root,
NestPath *pathnode = makeNode(NestPath);
Relids inner_req_outer = PATH_REQ_OUTER(inner_path);
+ /* TODO: Assert lateral relids subset safety? */
+
/*
* If the inner path is parameterized by the outer, we must drop any
* restrict_clauses that are due to be moved into the inner path. We have
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 294cfe9c47..b69d4e2692 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2964,7 +2964,7 @@ typedef struct MinMaxAggInfo
* for conflicting purposes.
*
* In addition, PARAM_EXEC slots are assigned for Params representing outputs
- * from subplans (values that are setParam items for those subplans). These
+ * from subplans (values that are setParam items for those subplans). [TODO: is this true, or only for init plans?] These
* IDs need not be tracked via PlannerParamItems, since we do not need any
* duplicate-elimination nor later processing of the represented expressions.
* Instead, we just record the assignment of the slot number by appending to
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 49953eaade..66c09381f1 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -1612,16 +1612,16 @@ from tenk1 t, generate_series(1, 1000);
QUERY PLAN
---------------------------------------------------------------------------------
Unique
- -> Sort
- Sort Key: t.unique1, ((SubPlan 1))
- -> Gather
- Workers Planned: 2
+ -> Gather Merge
+ Workers Planned: 2
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan 1))
-> Nested Loop
-> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
-> Function Scan on generate_series
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
(11 rows)
explain (costs off) select
@@ -1631,16 +1631,16 @@ from tenk1 t, generate_series(1, 1000)
order by 1, 2;
QUERY PLAN
---------------------------------------------------------------------------
- Sort
- Sort Key: t.unique1, ((SubPlan 1))
- -> Gather
- Workers Planned: 2
+ Gather Merge
+ Workers Planned: 2
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan 1))
-> Nested Loop
-> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
-> Function Scan on generate_series
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
(10 rows)
-- Parallel sort but with expression not available until the upper rel.
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c77..5c45f9c0a5 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1284,60 +1284,64 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x;
--
-- pruning won't work for mc3p, because some keys are Params
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
- QUERY PLAN
------------------------------------------------------------------------
- Nested Loop
- -> Append
- -> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
- -> Seq Scan on mc2p2 t1_2
- Filter: (a = 1)
- -> Seq Scan on mc2p_default t1_3
- Filter: (a = 1)
- -> Aggregate
- -> Append
- -> Seq Scan on mc3p0 t2_1
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p1 t2_2
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p2 t2_3
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p3 t2_4
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p4 t2_5
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p5 t2_6
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p6 t2_7
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p7 t2_8
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p_default t2_9
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
-(28 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Nested Loop
+ -> Parallel Append
+ -> Parallel Seq Scan on mc2p1 t1_1
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p2 t1_2
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p_default t1_3
+ Filter: (a = 1)
+ -> Aggregate
+ -> Append
+ -> Seq Scan on mc3p0 t2_1
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1 t2_2
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p2 t2_3
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p3 t2_4
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p4 t2_5
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p5 t2_6
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p6 t2_7
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p7 t2_8
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default t2_9
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+(30 rows)
-- pruning should work fine, because values for a prefix of keys (a, b) are
-- available
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1;
- QUERY PLAN
------------------------------------------------------------------------
- Nested Loop
- -> Append
- -> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
- -> Seq Scan on mc2p2 t1_2
- Filter: (a = 1)
- -> Seq Scan on mc2p_default t1_3
- Filter: (a = 1)
- -> Aggregate
- -> Append
- -> Seq Scan on mc3p0 t2_1
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p1 t2_2
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p_default t2_3
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
-(16 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Nested Loop
+ -> Parallel Append
+ -> Parallel Seq Scan on mc2p1 t1_1
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p2 t1_2
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p_default t1_3
+ Filter: (a = 1)
+ -> Aggregate
+ -> Append
+ -> Seq Scan on mc3p0 t2_1
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1 t2_2
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default t2_3
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+(18 rows)
-- also here, because values for all keys are provided
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 9b4d7dd44a..01443e2ffb 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -137,8 +137,8 @@ create table part_pa_test_p2 partition of part_pa_test for values from (0) to (m
explain (costs off)
select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a)))
from part_pa_test pa2;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Aggregate
-> Gather
Workers Planned: 3
@@ -148,12 +148,14 @@ explain (costs off)
SubPlan 2
-> Result
SubPlan 1
- -> Append
- -> Seq Scan on part_pa_test_p1 pa1_1
- Filter: (a = pa2.a)
- -> Seq Scan on part_pa_test_p2 pa1_2
- Filter: (a = pa2.a)
-(14 rows)
+ -> Gather
+ Workers Planned: 3
+ -> Parallel Append
+ -> Parallel Seq Scan on part_pa_test_p1 pa1_1
+ Filter: (a = pa2.a)
+ -> Parallel Seq Scan on part_pa_test_p2 pa1_2
+ Filter: (a = pa2.a)
+(16 rows)
drop table part_pa_test;
-- test with leader participation disabled
@@ -320,19 +322,19 @@ explain (costs off, verbose) select
QUERY PLAN
----------------------------------------------------------------------------
Gather
- Output: (SubPlan 1)
+ Output: ((SubPlan 1))
Workers Planned: 4
-> Nested Loop
- Output: t.unique1
+ Output: (SubPlan 1)
-> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
Output: t.unique1
-> Function Scan on pg_catalog.generate_series
Output: generate_series.generate_series
Function Call: generate_series(1, 10)
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(14 rows)
explain (costs off, verbose) select
@@ -341,63 +343,69 @@ explain (costs off, verbose) select
QUERY PLAN
----------------------------------------------------------------------
Gather
- Output: (SubPlan 1)
+ Output: ((SubPlan 1))
Workers Planned: 4
-> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
- Output: t.unique1
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ Output: (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(9 rows)
explain (costs off, verbose) select
(select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
from tenk1 t
limit 1;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Limit
Output: ((SubPlan 1))
- -> Seq Scan on public.tenk1 t
- Output: (SubPlan 1)
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
-(8 rows)
+ -> Gather
+ Output: ((SubPlan 1))
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(11 rows)
explain (costs off, verbose) select t.unique1
from tenk1 t
where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
- QUERY PLAN
--------------------------------------------------------------
- Seq Scan on public.tenk1 t
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
Output: t.unique1
- Filter: (t.unique1 = (SubPlan 1))
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
-(7 rows)
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ Filter: (t.unique1 = (SubPlan 1))
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(10 rows)
explain (costs off, verbose) select *
from tenk1 t
order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Merge
Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
- Sort Key: ((SubPlan 1))
- -> Gather
- Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, (SubPlan 1)
- Workers Planned: 4
+ Workers Planned: 4
+ -> Sort
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
+ Sort Key: ((SubPlan 1))
-> Parallel Seq Scan on public.tenk1 t
- Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(12 rows)
-- test subplan in join/lateral join
@@ -409,14 +417,14 @@ explain (costs off, verbose, timing off) select t.unique1, l.*
QUERY PLAN
----------------------------------------------------------------------
Gather
- Output: t.unique1, (SubPlan 1)
+ Output: t.unique1, ((SubPlan 1))
Workers Planned: 4
-> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
- Output: t.unique1
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ Output: t.unique1, (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(9 rows)
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
@@ -1322,8 +1330,10 @@ SELECT 1 FROM tenk1_vw_sec
-> Parallel Index Only Scan using tenk1_unique1 on tenk1
SubPlan 1
-> Aggregate
- -> Seq Scan on int4_tbl
- Filter: (f1 < tenk1_vw_sec.unique1)
-(9 rows)
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Seq Scan on int4_tbl
+ Filter: (f1 < tenk1_vw_sec.unique1)
+(11 rows)
rollback;
--
2.32.1 (Apple Git-133)
[application/octet-stream] v5-0003-Possible-additional-checks.patch (3.2K, ../../CAAaqYe9hJ=GzcaVSH82e8o6edr3bkYkauhk_yLiGVbF=8xss1w@mail.gmail.com/3-v5-0003-Possible-additional-checks.patch)
download | inline diff:
From bb794cf9b4fd6cd33e01ea8aa89e863fffade13f Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Sat, 22 Jan 2022 17:33:23 -0500
Subject: [PATCH v5 3/3] Possible additional checks
---
src/backend/optimizer/path/allpaths.c | 33 ++++++++++++++++++++++-----
1 file changed, 27 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index e1ad9ae372..e19cbbe90f 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -3007,11 +3007,16 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
ListCell *lc;
double rows;
double *rowsp = NULL;
+ Relids required_outer = rel->lateral_relids;
/* If there are no partial paths, there's nothing to do here. */
if (rel->partial_pathlist == NIL)
return;
+ if (!bms_is_subset(required_outer, rel->relids))
+ return;
+
+
/* Should we override the rel's rowcount estimate? */
if (override_rows)
rowsp = &rows;
@@ -3022,12 +3027,16 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
* of partial_pathlist because of the way add_partial_path works.
*/
cheapest_partial_path = linitial(rel->partial_pathlist);
- rows =
- cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
- simple_gather_path = (Path *)
- create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
- rel->lateral_relids, rowsp);
- add_path(rel, simple_gather_path);
+ if (cheapest_partial_path->param_info == NULL ||
+ bms_is_subset(cheapest_partial_path->param_info->ppi_req_outer, rel->relids))
+ {
+ rows =
+ cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
+ simple_gather_path = (Path *)
+ create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
+ rel->lateral_relids, rowsp);
+ add_path(rel, simple_gather_path);
+ }
/*
* For each useful ordering, we can consider an order-preserving Gather
@@ -3041,6 +3050,10 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
if (subpath->pathkeys == NIL)
continue;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
rows = subpath->rows * subpath->parallel_workers;
path = create_gather_merge_path(root, rel, subpath, rel->reltarget,
subpath->pathkeys, rel->lateral_relids, rowsp);
@@ -3213,6 +3226,10 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
{
Path *tmp;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
tmp = (Path *) create_sort_path(root,
rel,
subpath,
@@ -3241,6 +3258,10 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
{
Path *tmp;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
/*
* We should have already excluded pathkeys of length 1
* because then presorted_keys > 0 would imply is_sorted was
--
2.32.1 (Apple Git-133)
[application/octet-stream] v5-0001-Add-tests-before-change.patch (7.2K, ../../CAAaqYe9hJ=GzcaVSH82e8o6edr3bkYkauhk_yLiGVbF=8xss1w@mail.gmail.com/4-v5-0001-Add-tests-before-change.patch)
download | inline diff:
From edc5bb3c0ba9d1aaa5d85e9716ce84856ad40514 Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Mon, 26 Sep 2022 20:30:23 -0400
Subject: [PATCH v5 1/3] Add tests before change
---
src/test/regress/expected/select_parallel.out | 108 ++++++++++++++++++
src/test/regress/sql/select_parallel.sql | 25 ++++
2 files changed, 133 insertions(+)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 91f74fe47a..9b4d7dd44a 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -311,6 +311,114 @@ select count(*) from tenk1 where (two, four) not in
10000
(1 row)
+-- test parallel plans for queries containing correlated subplans
+-- where the subplan only needs params available from the current
+-- worker's scan.
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t, generate_series(1, 10);
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Gather
+ Output: (SubPlan 1)
+ Workers Planned: 4
+ -> Nested Loop
+ Output: t.unique1
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ -> Function Scan on pg_catalog.generate_series
+ Output: generate_series.generate_series
+ Function Call: generate_series(1, 10)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(14 rows)
+
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
+ Output: (SubPlan 1)
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(9 rows)
+
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t
+ limit 1;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Limit
+ Output: ((SubPlan 1))
+ -> Seq Scan on public.tenk1 t
+ Output: (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(8 rows)
+
+explain (costs off, verbose) select t.unique1
+ from tenk1 t
+ where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on public.tenk1 t
+ Output: t.unique1
+ Filter: (t.unique1 = (SubPlan 1))
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(7 rows)
+
+explain (costs off, verbose) select *
+ from tenk1 t
+ order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
+ Sort Key: ((SubPlan 1))
+ -> Gather
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, (SubPlan 1)
+ Workers Planned: 4
+ -> Parallel Seq Scan on public.tenk1 t
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(12 rows)
+
+-- test subplan in join/lateral join
+explain (costs off, verbose, timing off) select t.unique1, l.*
+ from tenk1 t
+ join lateral (
+ select (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1 offset 0)
+ ) l on true;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
+ Output: t.unique1, (SubPlan 1)
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(9 rows)
+
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 62fb68c7a0..21c2f1c742 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -111,6 +111,31 @@ explain (costs off)
(select hundred, thousand from tenk2 where thousand > 100);
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
+-- test parallel plans for queries containing correlated subplans
+-- where the subplan only needs params available from the current
+-- worker's scan.
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t, generate_series(1, 10);
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t;
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t
+ limit 1;
+explain (costs off, verbose) select t.unique1
+ from tenk1 t
+ where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+explain (costs off, verbose) select *
+ from tenk1 t
+ order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+-- test subplan in join/lateral join
+explain (costs off, verbose, timing off) select t.unique1, l.*
+ from tenk1 t
+ join lateral (
+ select (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1 offset 0)
+ ) l on true;
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
--
2.32.1 (Apple Git-133)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2023-01-04 09:49 vignesh C <[email protected]>
parent: James Coleman <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: vignesh C @ 2023-01-04 09:49 UTC (permalink / raw)
To: James Coleman <[email protected]>; +Cc: Andres Freund <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
On Tue, 27 Sept 2022 at 08:26, James Coleman <[email protected]> wrote:
>
> On Mon, Mar 21, 2022 at 8:48 PM Andres Freund <[email protected]> wrote:
> >
> > Hi,
> >
> > On 2022-01-22 20:25:19 -0500, James Coleman wrote:
> > > On the other hand this is a dramatically simpler patch series.
> > > Assuming the approach is sound, it should much easier to maintain than
> > > the previous version.
> > >
> > > The final patch in the series is a set of additional checks I could
> > > imagine to try to be more explicit, but at least in the current test
> > > suite there isn't anything at all they affect.
> > >
> > > Does this look at least somewhat more like what you'd envisionsed
> > > (granting the need to squint hard given the relids checks instead of
> > > directly checking params)?
> >
> > This fails on freebsd (so likely a timing issue): https://cirrus-ci.com/task/4758411492458496?logs=test_world#L2225
> >
> > Marked as waiting on author.
>
> I've finally gotten around to checking this out, and the issue was an
> "explain analyze" test that had actual loops different on FreeBSD.
> There doesn't seem to be a way to disable loop output, but instead of
> processing the explain output with e.g. a function (as we do some
> other places) to remove the offending and unnecessary output I've just
> removed the "analyze" (as I don't believe it was actually necessary).
>
> Attached is an updated patch series. In this version I've removed the
> "parallelize some subqueries with limit" patch since discussion is
> proceeding in the spun off thread. The first patch adds additional
> tests so that you can see how those new tests change with the code
> changes in the 2nd patch in the series. As before the final patch in
> the series includes changes where we may also want to verify
> correctness but don't have a test demonstrating the need.
The patch does not apply on top of HEAD as in [1], please post a rebased patch:
=== Applying patches on top of PostgreSQL commit ID
bf03cfd162176d543da79f9398131abc251ddbb9 ===
=== applying patch ./v5-0002-Parallelize-correlated-subqueries.patch
patching file src/backend/optimizer/path/allpaths.c
...
Hunk #5 FAILED at 3225.
Hunk #6 FAILED at 3259.
Hunk #7 succeeded at 3432 (offset -6 lines).
2 out of 7 hunks FAILED -- saving rejects to file
src/backend/optimizer/path/allpaths.c.rej
[1] - http://cfbot.cputube.org/patch_41_3246.log
Regards,
Vignesh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2023-01-18 19:09 Tomas Vondra <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tomas Vondra @ 2023-01-18 19:09 UTC (permalink / raw)
To: vignesh C <[email protected]>; James Coleman <[email protected]>; +Cc: Andres Freund <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
Hi,
This patch hasn't been updated since September, and it got broken by
4a29eabd1d91c5484426bc5836e0a7143b064f5a which the incremental sort
stuff a little bit. But the breakage was rather limited, so I took a
stab at fixing it - attached is the result, hopefully correct.
I also added a couple minor comments about stuff I noticed while
rebasing and skimming the patch, I kept those in separate commits.
There's also a couple pre-existing TODOs.
James, what's your plan with this patch. Do you intend to work on it for
PG16, or are there some issues I missed in the thread?
One of the queries in in incremental_sort changed plans a little bit:
explain (costs off) select distinct
unique1,
(select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
from tenk1 t, generate_series(1, 1000);
switched from
Unique (cost=18582710.41..18747375.21 rows=10000 width=8)
-> Gather Merge (cost=18582710.41..18697375.21 rows=10000000 ...)
Workers Planned: 2
-> Sort (cost=18582710.39..18593127.06 rows=4166667 ...)
Sort Key: t.unique1, ((SubPlan 1))
...
to
Unique (cost=18582710.41..18614268.91 rows=10000 ...)
-> Gather Merge (cost=18582710.41..18614168.91 rows=20000 ...)
Workers Planned: 2
-> Unique (cost=18582710.39..18613960.39 rows=10000 ...)
-> Sort (cost=18582710.39..18593127.06 ...)
Sort Key: t.unique1, ((SubPlan 1))
...
which probably makes sense, as the cost estimate decreases a bit.
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] 0001-Add-tests-before-change-v6.patch (7.2K, ../../[email protected]/2-0001-Add-tests-before-change-v6.patch)
download | inline diff:
From 95db15fe16303ed3f4fdea52af3b8d6d05a8d7a6 Mon Sep 17 00:00:00 2001
From: jcoleman <[email protected]>
Date: Mon, 26 Sep 2022 20:30:23 -0400
Subject: [PATCH 1/5] Add tests before change
---
src/test/regress/expected/select_parallel.out | 108 ++++++++++++++++++
src/test/regress/sql/select_parallel.sql | 25 ++++
2 files changed, 133 insertions(+)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 91f74fe47a3..9b4d7dd44a4 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -311,6 +311,114 @@ select count(*) from tenk1 where (two, four) not in
10000
(1 row)
+-- test parallel plans for queries containing correlated subplans
+-- where the subplan only needs params available from the current
+-- worker's scan.
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t, generate_series(1, 10);
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Gather
+ Output: (SubPlan 1)
+ Workers Planned: 4
+ -> Nested Loop
+ Output: t.unique1
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ -> Function Scan on pg_catalog.generate_series
+ Output: generate_series.generate_series
+ Function Call: generate_series(1, 10)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(14 rows)
+
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
+ Output: (SubPlan 1)
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(9 rows)
+
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t
+ limit 1;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Limit
+ Output: ((SubPlan 1))
+ -> Seq Scan on public.tenk1 t
+ Output: (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(8 rows)
+
+explain (costs off, verbose) select t.unique1
+ from tenk1 t
+ where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on public.tenk1 t
+ Output: t.unique1
+ Filter: (t.unique1 = (SubPlan 1))
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(7 rows)
+
+explain (costs off, verbose) select *
+ from tenk1 t
+ order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
+ Sort Key: ((SubPlan 1))
+ -> Gather
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, (SubPlan 1)
+ Workers Planned: 4
+ -> Parallel Seq Scan on public.tenk1 t
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(12 rows)
+
+-- test subplan in join/lateral join
+explain (costs off, verbose, timing off) select t.unique1, l.*
+ from tenk1 t
+ join lateral (
+ select (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1 offset 0)
+ ) l on true;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
+ Output: t.unique1, (SubPlan 1)
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(9 rows)
+
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 62fb68c7a04..21c2f1c7424 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -111,6 +111,31 @@ explain (costs off)
(select hundred, thousand from tenk2 where thousand > 100);
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
+-- test parallel plans for queries containing correlated subplans
+-- where the subplan only needs params available from the current
+-- worker's scan.
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t, generate_series(1, 10);
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t;
+explain (costs off, verbose) select
+ (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
+ from tenk1 t
+ limit 1;
+explain (costs off, verbose) select t.unique1
+ from tenk1 t
+ where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+explain (costs off, verbose) select *
+ from tenk1 t
+ order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
+-- test subplan in join/lateral join
+explain (costs off, verbose, timing off) select t.unique1, l.*
+ from tenk1 t
+ join lateral (
+ select (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1 offset 0)
+ ) l on true;
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
--
2.39.0
[text/x-patch] 0002-Parallelize-correlated-subqueries-v6.patch (24.9K, ../../[email protected]/3-0002-Parallelize-correlated-subqueries-v6.patch)
download | inline diff:
From cd379afbe22a9b41ff2b5dd5d86719a19f15301f Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 18 Jan 2023 19:15:06 +0100
Subject: [PATCH 2/5] Parallelize correlated subqueries
When params are provided at the current query level (i.e., are generated
within a single worker and not shared across workers) we can safely
execute these in parallel.
Alternative approach using just relids subset check
---
doc/src/sgml/parallel.sgml | 3 +-
src/backend/optimizer/path/allpaths.c | 16 ++-
src/backend/optimizer/path/joinpath.c | 16 ++-
src/backend/optimizer/util/clauses.c | 3 +
src/backend/optimizer/util/pathnode.c | 2 +
src/include/nodes/pathnodes.h | 2 +-
.../regress/expected/incremental_sort.out | 28 ++--
src/test/regress/expected/partition_prune.out | 104 +++++++-------
src/test/regress/expected/select_parallel.out | 128 ++++++++++--------
9 files changed, 168 insertions(+), 134 deletions(-)
diff --git a/doc/src/sgml/parallel.sgml b/doc/src/sgml/parallel.sgml
index 5acc9537d6f..fd32572ec8b 100644
--- a/doc/src/sgml/parallel.sgml
+++ b/doc/src/sgml/parallel.sgml
@@ -518,7 +518,8 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
<listitem>
<para>
- Plan nodes that reference a correlated <literal>SubPlan</literal>.
+ Plan nodes that reference a correlated <literal>SubPlan</literal> where
+ the result is shared between workers.
</para>
</listitem>
</itemizedlist>
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index c2fc568dc8a..7108501b9a7 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -558,7 +558,8 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
* the final scan/join targetlist is available (see grouping_planner).
*/
if (rel->reloptkind == RELOPT_BASEREL &&
- !bms_equal(rel->relids, root->all_baserels))
+ !bms_equal(rel->relids, root->all_baserels)
+ && (rel->subplan_params == NIL || rte->rtekind != RTE_SUBQUERY))
generate_useful_gather_paths(root, rel, false);
/* Now find the cheapest of the paths for this rel */
@@ -3037,7 +3038,7 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
simple_gather_path = (Path *)
create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
- NULL, rowsp);
+ rel->lateral_relids, rowsp);
add_path(rel, simple_gather_path);
/*
@@ -3054,7 +3055,7 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
rows = subpath->rows * subpath->parallel_workers;
path = create_gather_merge_path(root, rel, subpath, rel->reltarget,
- subpath->pathkeys, NULL, rowsp);
+ subpath->pathkeys, rel->lateral_relids, rowsp);
add_path(rel, &path->path);
}
}
@@ -3156,11 +3157,15 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
double *rowsp = NULL;
List *useful_pathkeys_list = NIL;
Path *cheapest_partial_path = NULL;
+ Relids required_outer = rel->lateral_relids;
/* If there are no partial paths, there's nothing to do here. */
if (rel->partial_pathlist == NIL)
return;
+ if (!bms_is_subset(required_outer, rel->relids))
+ return;
+
/* Should we override the rel's rowcount estimate? */
if (override_rows)
rowsp = &rows;
@@ -3249,7 +3254,7 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
subpath,
rel->reltarget,
subpath->pathkeys,
- NULL,
+ required_outer,
rowsp);
add_path(rel, &path->path);
@@ -3427,7 +3432,8 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
/*
* Except for the topmost scan/join rel, consider gathering
* partial paths. We'll do the same for the topmost scan/join rel
- * once we know the final targetlist (see grouping_planner).
+ * once we know the final targetlist (see
+ * apply_scanjoin_target_to_paths).
*/
if (!bms_equal(rel->relids, root->all_baserels))
generate_useful_gather_paths(root, rel, false);
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index d345c0437a4..000e3ca9a25 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -1792,16 +1792,24 @@ match_unsorted_outer(PlannerInfo *root,
* partial path and the joinrel is parallel-safe. However, we can't
* handle JOIN_UNIQUE_OUTER, because the outer path will be partial, and
* therefore we won't be able to properly guarantee uniqueness. Nor can
- * we handle joins needing lateral rels, since partial paths must not be
- * parameterized. Similarly, we can't handle JOIN_FULL and JOIN_RIGHT,
- * because they can produce false null extended rows.
+ * we handle JOIN_FULL and JOIN_RIGHT, because they can produce false null
+ * extended rows.
+ *
+ * Partial paths may only have parameters in limited cases
+ * where the parameterization is fully satisfied without sharing state
+ * between workers, so we only allow lateral rels on inputs to the join
+ * if the resulting join contains no lateral rels, the inner rel's laterals
+ * are fully satisfied by the outer rel, and the outer rel doesn't depend
+ * on the inner rel to produce any laterals.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
outerrel->partial_pathlist != NIL &&
- bms_is_empty(joinrel->lateral_relids))
+ bms_is_empty(joinrel->lateral_relids) &&
+ bms_is_subset(innerrel->lateral_relids, outerrel->relids) &&
+ (bms_is_empty(outerrel->lateral_relids) || !bms_is_subset(outerrel->lateral_relids, innerrel->relids)))
{
if (nestjoinOK)
consider_parallel_nestloop(root, joinrel, outerrel, innerrel,
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index aa584848cf9..035471d05d0 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -816,6 +816,9 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
if (param->paramkind == PARAM_EXTERN)
return false;
+ if (param->paramkind == PARAM_EXEC)
+ return false;
+
if (param->paramkind != PARAM_EXEC ||
!list_member_int(context->safe_param_ids, param->paramid))
{
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 4478036bb6a..5667222e925 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -2437,6 +2437,8 @@ create_nestloop_path(PlannerInfo *root,
NestPath *pathnode = makeNode(NestPath);
Relids inner_req_outer = PATH_REQ_OUTER(inner_path);
+ /* TODO: Assert lateral relids subset safety? */
+
/*
* If the inner path is parameterized by the outer, we must drop any
* restrict_clauses that are due to be moved into the inner path. We have
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index c20b7298a3d..a5ba65a5616 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2962,7 +2962,7 @@ typedef struct MinMaxAggInfo
* for conflicting purposes.
*
* In addition, PARAM_EXEC slots are assigned for Params representing outputs
- * from subplans (values that are setParam items for those subplans). These
+ * from subplans (values that are setParam items for those subplans). [TODO: is this true, or only for init plans?] These
* IDs need not be tracked via PlannerParamItems, since we do not need any
* duplicate-elimination nor later processing of the represented expressions.
* Instead, we just record the assignment of the slot number by appending to
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 0c3433f8e58..0cb7c1a49c0 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -1600,16 +1600,16 @@ from tenk1 t, generate_series(1, 1000);
QUERY PLAN
---------------------------------------------------------------------------------
Unique
- -> Sort
- Sort Key: t.unique1, ((SubPlan 1))
- -> Gather
- Workers Planned: 2
+ -> Gather Merge
+ Workers Planned: 2
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan 1))
-> Nested Loop
-> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
-> Function Scan on generate_series
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
(11 rows)
explain (costs off) select
@@ -1619,16 +1619,16 @@ from tenk1 t, generate_series(1, 1000)
order by 1, 2;
QUERY PLAN
---------------------------------------------------------------------------
- Sort
- Sort Key: t.unique1, ((SubPlan 1))
- -> Gather
- Workers Planned: 2
+ Gather Merge
+ Workers Planned: 2
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan 1))
-> Nested Loop
-> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
-> Function Scan on generate_series
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
(10 rows)
-- Parallel sort but with expression not available until the upper rel.
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c779..5c45f9c0a50 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1284,60 +1284,64 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x;
--
-- pruning won't work for mc3p, because some keys are Params
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
- QUERY PLAN
------------------------------------------------------------------------
- Nested Loop
- -> Append
- -> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
- -> Seq Scan on mc2p2 t1_2
- Filter: (a = 1)
- -> Seq Scan on mc2p_default t1_3
- Filter: (a = 1)
- -> Aggregate
- -> Append
- -> Seq Scan on mc3p0 t2_1
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p1 t2_2
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p2 t2_3
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p3 t2_4
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p4 t2_5
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p5 t2_6
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p6 t2_7
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p7 t2_8
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p_default t2_9
- Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
-(28 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Nested Loop
+ -> Parallel Append
+ -> Parallel Seq Scan on mc2p1 t1_1
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p2 t1_2
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p_default t1_3
+ Filter: (a = 1)
+ -> Aggregate
+ -> Append
+ -> Seq Scan on mc3p0 t2_1
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1 t2_2
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p2 t2_3
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p3 t2_4
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p4 t2_5
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p5 t2_6
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p6 t2_7
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p7 t2_8
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default t2_9
+ Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
+(30 rows)
-- pruning should work fine, because values for a prefix of keys (a, b) are
-- available
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1;
- QUERY PLAN
------------------------------------------------------------------------
- Nested Loop
- -> Append
- -> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
- -> Seq Scan on mc2p2 t1_2
- Filter: (a = 1)
- -> Seq Scan on mc2p_default t1_3
- Filter: (a = 1)
- -> Aggregate
- -> Append
- -> Seq Scan on mc3p0 t2_1
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p1 t2_2
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
- -> Seq Scan on mc3p_default t2_3
- Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
-(16 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Nested Loop
+ -> Parallel Append
+ -> Parallel Seq Scan on mc2p1 t1_1
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p2 t1_2
+ Filter: (a = 1)
+ -> Parallel Seq Scan on mc2p_default t1_3
+ Filter: (a = 1)
+ -> Aggregate
+ -> Append
+ -> Seq Scan on mc3p0 t2_1
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1 t2_2
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default t2_3
+ Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
+(18 rows)
-- also here, because values for all keys are provided
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 9b4d7dd44a4..01443e2ffbe 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -137,8 +137,8 @@ create table part_pa_test_p2 partition of part_pa_test for values from (0) to (m
explain (costs off)
select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a)))
from part_pa_test pa2;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Aggregate
-> Gather
Workers Planned: 3
@@ -148,12 +148,14 @@ explain (costs off)
SubPlan 2
-> Result
SubPlan 1
- -> Append
- -> Seq Scan on part_pa_test_p1 pa1_1
- Filter: (a = pa2.a)
- -> Seq Scan on part_pa_test_p2 pa1_2
- Filter: (a = pa2.a)
-(14 rows)
+ -> Gather
+ Workers Planned: 3
+ -> Parallel Append
+ -> Parallel Seq Scan on part_pa_test_p1 pa1_1
+ Filter: (a = pa2.a)
+ -> Parallel Seq Scan on part_pa_test_p2 pa1_2
+ Filter: (a = pa2.a)
+(16 rows)
drop table part_pa_test;
-- test with leader participation disabled
@@ -320,19 +322,19 @@ explain (costs off, verbose) select
QUERY PLAN
----------------------------------------------------------------------------
Gather
- Output: (SubPlan 1)
+ Output: ((SubPlan 1))
Workers Planned: 4
-> Nested Loop
- Output: t.unique1
+ Output: (SubPlan 1)
-> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
Output: t.unique1
-> Function Scan on pg_catalog.generate_series
Output: generate_series.generate_series
Function Call: generate_series(1, 10)
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(14 rows)
explain (costs off, verbose) select
@@ -341,63 +343,69 @@ explain (costs off, verbose) select
QUERY PLAN
----------------------------------------------------------------------
Gather
- Output: (SubPlan 1)
+ Output: ((SubPlan 1))
Workers Planned: 4
-> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
- Output: t.unique1
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ Output: (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(9 rows)
explain (costs off, verbose) select
(select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
from tenk1 t
limit 1;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Limit
Output: ((SubPlan 1))
- -> Seq Scan on public.tenk1 t
- Output: (SubPlan 1)
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
-(8 rows)
+ -> Gather
+ Output: ((SubPlan 1))
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(11 rows)
explain (costs off, verbose) select t.unique1
from tenk1 t
where t.unique1 = (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
- QUERY PLAN
--------------------------------------------------------------
- Seq Scan on public.tenk1 t
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather
Output: t.unique1
- Filter: (t.unique1 = (SubPlan 1))
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
-(7 rows)
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
+ Output: t.unique1
+ Filter: (t.unique1 = (SubPlan 1))
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
+(10 rows)
explain (costs off, verbose) select *
from tenk1 t
order by (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Merge
Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
- Sort Key: ((SubPlan 1))
- -> Gather
- Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, (SubPlan 1)
- Workers Planned: 4
+ Workers Planned: 4
+ -> Sort
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, ((SubPlan 1))
+ Sort Key: ((SubPlan 1))
-> Parallel Seq Scan on public.tenk1 t
- Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ Output: t.unique1, t.unique2, t.two, t.four, t.ten, t.twenty, t.hundred, t.thousand, t.twothousand, t.fivethous, t.tenthous, t.odd, t.even, t.stringu1, t.stringu2, t.string4, (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(12 rows)
-- test subplan in join/lateral join
@@ -409,14 +417,14 @@ explain (costs off, verbose, timing off) select t.unique1, l.*
QUERY PLAN
----------------------------------------------------------------------
Gather
- Output: t.unique1, (SubPlan 1)
+ Output: t.unique1, ((SubPlan 1))
Workers Planned: 4
-> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
- Output: t.unique1
- SubPlan 1
- -> Index Only Scan using tenk1_unique1 on public.tenk1
- Output: t.unique1
- Index Cond: (tenk1.unique1 = t.unique1)
+ Output: t.unique1, (SubPlan 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: t.unique1
+ Index Cond: (tenk1.unique1 = t.unique1)
(9 rows)
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
@@ -1322,8 +1330,10 @@ SELECT 1 FROM tenk1_vw_sec
-> Parallel Index Only Scan using tenk1_unique1 on tenk1
SubPlan 1
-> Aggregate
- -> Seq Scan on int4_tbl
- Filter: (f1 < tenk1_vw_sec.unique1)
-(9 rows)
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Seq Scan on int4_tbl
+ Filter: (f1 < tenk1_vw_sec.unique1)
+(11 rows)
rollback;
--
2.39.0
[text/x-patch] 0003-review-comments-v6.patch (2.0K, ../../[email protected]/4-0003-review-comments-v6.patch)
download | inline diff:
From 1aa279e8e82a4e9771fb1a5068e9a5792a824a54 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 18 Jan 2023 19:51:38 +0100
Subject: [PATCH 3/5] review comments
---
src/backend/optimizer/path/allpaths.c | 5 +++--
src/backend/optimizer/util/clauses.c | 5 +++++
2 files changed, 8 insertions(+), 2 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 7108501b9a7..357dfaab3e8 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -558,8 +558,8 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
* the final scan/join targetlist is available (see grouping_planner).
*/
if (rel->reloptkind == RELOPT_BASEREL &&
- !bms_equal(rel->relids, root->all_baserels)
- && (rel->subplan_params == NIL || rte->rtekind != RTE_SUBQUERY))
+ !bms_equal(rel->relids, root->all_baserels) &&
+ (rel->subplan_params == NIL || rte->rtekind != RTE_SUBQUERY))
generate_useful_gather_paths(root, rel, false);
/* Now find the cheapest of the paths for this rel */
@@ -3163,6 +3163,7 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
if (rel->partial_pathlist == NIL)
return;
+ /* FIXME ??? */
if (!bms_is_subset(required_outer, rel->relids))
return;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 035471d05d0..9585acf8e69 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -819,6 +819,11 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
if (param->paramkind == PARAM_EXEC)
return false;
+ /*
+ * XXX The first condition is certainly true, thanks to the preceding
+ * check. The comment above should be updated to reflect this change,
+ * probably.
+ */
if (param->paramkind != PARAM_EXEC ||
!list_member_int(context->safe_param_ids, param->paramid))
{
--
2.39.0
[text/x-patch] 0004-Possible-additional-checks-v6.patch (2.9K, ../../[email protected]/5-0004-Possible-additional-checks-v6.patch)
download | inline diff:
From 45da97c1141ead1fe757fcff549e68fbd4c1ded9 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 18 Jan 2023 19:21:24 +0100
Subject: [PATCH 4/5] Possible additional checks
---
src/backend/optimizer/path/allpaths.c | 29 +++++++++++++++++++++------
1 file changed, 23 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 357dfaab3e8..eaa972ec64b 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -3019,11 +3019,16 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
ListCell *lc;
double rows;
double *rowsp = NULL;
+ Relids required_outer = rel->lateral_relids;
/* If there are no partial paths, there's nothing to do here. */
if (rel->partial_pathlist == NIL)
return;
+ if (!bms_is_subset(required_outer, rel->relids))
+ return;
+
+
/* Should we override the rel's rowcount estimate? */
if (override_rows)
rowsp = &rows;
@@ -3034,12 +3039,16 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
* of partial_pathlist because of the way add_partial_path works.
*/
cheapest_partial_path = linitial(rel->partial_pathlist);
- rows =
- cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
- simple_gather_path = (Path *)
- create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
- rel->lateral_relids, rowsp);
- add_path(rel, simple_gather_path);
+ if (cheapest_partial_path->param_info == NULL ||
+ bms_is_subset(cheapest_partial_path->param_info->ppi_req_outer, rel->relids))
+ {
+ rows =
+ cheapest_partial_path->rows * cheapest_partial_path->parallel_workers;
+ simple_gather_path = (Path *)
+ create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
+ rel->lateral_relids, rowsp);
+ add_path(rel, simple_gather_path);
+ }
/*
* For each useful ordering, we can consider an order-preserving Gather
@@ -3053,6 +3062,10 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
if (subpath->pathkeys == NIL)
continue;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
rows = subpath->rows * subpath->parallel_workers;
path = create_gather_merge_path(root, rel, subpath, rel->reltarget,
subpath->pathkeys, rel->lateral_relids, rowsp);
@@ -3223,6 +3236,10 @@ generate_useful_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_r
(presorted_keys == 0 || !enable_incremental_sort))
continue;
+ if (subpath->param_info != NULL &&
+ !bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
+ break;
+
/*
* Consider regular sort for any path that's not presorted or if
* incremental sort is disabled. We've no need to consider both
--
2.39.0
[text/x-patch] 0005-review-comments-v6.patch (1.1K, ../../[email protected]/6-0005-review-comments-v6.patch)
download | inline diff:
From 2ca25bd5bbe1aa6849c55cab58dafe202adacce9 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 18 Jan 2023 19:52:46 +0100
Subject: [PATCH 5/5] review comments
---
src/backend/optimizer/path/allpaths.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index eaa972ec64b..9dd10a1274c 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -3025,10 +3025,10 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
if (rel->partial_pathlist == NIL)
return;
+ /* FIXME ??? */
if (!bms_is_subset(required_outer, rel->relids))
return;
-
/* Should we override the rel's rowcount estimate? */
if (override_rows)
rowsp = &rows;
@@ -3062,6 +3062,7 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows)
if (subpath->pathkeys == NIL)
continue;
+ /* FIXME ??? */
if (subpath->param_info != NULL &&
!bms_is_subset(subpath->param_info->ppi_req_outer, rel->relids))
break;
--
2.39.0
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2023-01-19 02:34 James Coleman <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: James Coleman @ 2023-01-19 02:34 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: vignesh C <[email protected]>; Andres Freund <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
On Wed, Jan 18, 2023 at 2:09 PM Tomas Vondra
<[email protected]> wrote:
>
> Hi,
>
> This patch hasn't been updated since September, and it got broken by
> 4a29eabd1d91c5484426bc5836e0a7143b064f5a which the incremental sort
> stuff a little bit. But the breakage was rather limited, so I took a
> stab at fixing it - attached is the result, hopefully correct.
Thanks for fixing this up; the changes look correct to me.
> I also added a couple minor comments about stuff I noticed while
> rebasing and skimming the patch, I kept those in separate commits.
> There's also a couple pre-existing TODOs.
I started work on some of these, but wasn't able to finish this
evening, so I don't have an updated series yet.
> James, what's your plan with this patch. Do you intend to work on it for
> PG16, or are there some issues I missed in the thread?
I'd love to see it get into PG16. I don't have any known issues, but
reviewing activity has been light. Originally Robert had had some
concerns about my original approach; I think my updated approach
resolves those issues, but it'd be good to have that sign-off.
Beyond that I'm mostly looking for review and evaluation of the
approach I've taken; of note is my description of that in [1].
> One of the queries in in incremental_sort changed plans a little bit:
>
> explain (costs off) select distinct
> unique1,
> (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
> from tenk1 t, generate_series(1, 1000);
>
> switched from
>
> Unique (cost=18582710.41..18747375.21 rows=10000 width=8)
> -> Gather Merge (cost=18582710.41..18697375.21 rows=10000000 ...)
> Workers Planned: 2
> -> Sort (cost=18582710.39..18593127.06 rows=4166667 ...)
> Sort Key: t.unique1, ((SubPlan 1))
> ...
>
> to
>
> Unique (cost=18582710.41..18614268.91 rows=10000 ...)
> -> Gather Merge (cost=18582710.41..18614168.91 rows=20000 ...)
> Workers Planned: 2
> -> Unique (cost=18582710.39..18613960.39 rows=10000 ...)
> -> Sort (cost=18582710.39..18593127.06 ...)
> Sort Key: t.unique1, ((SubPlan 1))
> ...
>
> which probably makes sense, as the cost estimate decreases a bit.
Off the cuff that seems fine. I'll read it over again when I send the
updated series.
James Coleman
1: https://www.postgresql.org/message-id/CAAaqYe8m0DHUWk7gLKb_C4abTD4nMkU26ErE%3Dahow4zNMZbzPQ%40mail.g...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallelize correlated subqueries that execute within each worker
@ 2025-07-03 14:04 Andy Fan <[email protected]>
parent: James Coleman <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Andy Fan @ 2025-07-03 14:04 UTC (permalink / raw)
To: James Coleman <[email protected]>; +Cc: Tomas Vondra <[email protected]>; vignesh C <[email protected]>; Andres Freund <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers; Amit Kapila <[email protected]>; Tom Lane <[email protected]>
Hi James:
Very nice to find this topic, I am recently working on this topic [1] as
well without finding this topic has been discussed before. I just go
through this thread and find it goes with a different direction with mine.
would you mind to check my soluation to see is there any case I can't
cover? I suggested this because my soluation should be much easier than
yours. But I'm not suprised to know I'm miss some obvious keypoint.
>
>> One of the queries in in incremental_sort changed plans a little bit:
>>
>> explain (costs off) select distinct
>> unique1,
>> (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
>> from tenk1 t, generate_series(1, 1000);
>>
>> switched from
>>
>> Unique (cost=18582710.41..18747375.21 rows=10000 width=8)
>> -> Gather Merge (cost=18582710.41..18697375.21 rows=10000000 ...)
>> Workers Planned: 2
>> -> Sort (cost=18582710.39..18593127.06 rows=4166667 ...)
>> Sort Key: t.unique1, ((SubPlan 1))
>> ...
>>
>> to
>>
>> Unique (cost=18582710.41..18614268.91 rows=10000 ...)
>> -> Gather Merge (cost=18582710.41..18614168.91 rows=20000 ...)
>> Workers Planned: 2
>> -> Unique (cost=18582710.39..18613960.39 rows=10000 ...)
>> -> Sort (cost=18582710.39..18593127.06 ...)
>> Sort Key: t.unique1, ((SubPlan 1))
>> ...
>>
>> which probably makes sense, as the cost estimate decreases a bit.
>
> Off the cuff that seems fine. I'll read it over again when I send the
> updated series.
I had a detailed explaination for this plan change in [1] and I think
this could be amazing gain no matter which way we go finally.
[1] https://www.postgresql.org/message-id/[email protected]
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-07-03 14:04 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-24 17:33 [PATCH v7 2/2] Add a --outdated option to reindexdb Julien Rouhaud <[email protected]>
2022-01-23 01:25 Re: Parallelize correlated subqueries that execute within each worker James Coleman <[email protected]>
2022-03-22 00:48 ` Re: Parallelize correlated subqueries that execute within each worker Andres Freund <[email protected]>
2022-08-02 20:57 ` Re: Parallelize correlated subqueries that execute within each worker Jacob Champion <[email protected]>
2022-09-27 02:56 ` Re: Parallelize correlated subqueries that execute within each worker James Coleman <[email protected]>
2023-01-04 09:49 ` Re: Parallelize correlated subqueries that execute within each worker vignesh C <[email protected]>
2023-01-18 19:09 ` Re: Parallelize correlated subqueries that execute within each worker Tomas Vondra <[email protected]>
2023-01-19 02:34 ` Re: Parallelize correlated subqueries that execute within each worker James Coleman <[email protected]>
2025-07-03 14:04 ` Re: Parallelize correlated subqueries that execute within each worker Andy Fan <[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