public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v48 6/7] Remove the GUC stats_temp_directory 2+ messages / 2 participants [nested] [flat]
* [PATCH v48 6/7] Remove the GUC stats_temp_directory @ 2020-09-29 13:59 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Kyotaro Horiguchi @ 2020-09-29 13:59 UTC (permalink / raw) The new stats collection system doesn't need temporary directory, so just remove it. pg_stat_statements modified to use pg_stat directory to store its temporary files. As the result basebackup copies the pg_stat_statments' temporary file if exists. --- .../pg_stat_statements/pg_stat_statements.c | 13 ++++--- doc/src/sgml/backup.sgml | 2 -- doc/src/sgml/config.sgml | 19 ---------- doc/src/sgml/storage.sgml | 6 ---- src/backend/postmaster/pgstat.c | 10 ------ src/backend/replication/basebackup.c | 36 ------------------- src/backend/utils/misc/guc.c | 32 ----------------- src/backend/utils/misc/postgresql.conf.sample | 1 - src/bin/initdb/initdb.c | 1 - src/bin/pg_rewind/filemap.c | 7 ---- src/include/pgstat.h | 3 -- src/test/perl/PostgresNode.pm | 4 --- 12 files changed, 6 insertions(+), 128 deletions(-) diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 62cccbfa44..28279f97d5 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -89,14 +89,13 @@ PG_MODULE_MAGIC; #define PGSS_DUMP_FILE PGSTAT_STAT_PERMANENT_DIRECTORY "/pg_stat_statements.stat" /* - * Location of external query text file. We don't keep it in the core - * system's stats_temp_directory. The core system can safely use that GUC - * setting, because the statistics collector temp file paths are set only once - * as part of changing the GUC, but pg_stat_statements has no way of avoiding - * race conditions. Besides, we only expect modest, infrequent I/O for query - * strings, so placing the file on a faster filesystem is not compelling. + * Location of external query text file. We don't keep it in the core system's + * pg_stats. pg_stat_statements has no way of avoiding race conditions even if + * the directory were specified by a GUC. Besides, we only expect modest, + * infrequent I/O for query strings, so placing the file on a faster filesystem + * is not compelling. */ -#define PGSS_TEXT_FILE PG_STAT_TMP_DIR "/pgss_query_texts.stat" +#define PGSS_TEXT_FILE PGSTAT_STAT_PERMANENT_DIRECTORY "/pgss_query_texts.stat" /* Magic number identifying the stats file format */ static const uint32 PGSS_FILE_HEADER = 0x20201218; diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index c5557d5444..875769a57e 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -1155,8 +1155,6 @@ SELECT pg_stop_backup(); <filename>pg_snapshots/</filename>, <filename>pg_stat_tmp/</filename>, and <filename>pg_subtrans/</filename> (but not the directories themselves) can be omitted from the backup as they will be initialized on postmaster startup. - If <xref linkend="guc-stats-temp-directory"/> is set and is under the data - directory then the contents of that directory can also be omitted. </para> <para> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 753b1ab6f9..49372c52d4 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7488,25 +7488,6 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> - <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory"> - <term><varname>stats_temp_directory</varname> (<type>string</type>) - <indexterm> - <primary><varname>stats_temp_directory</varname> configuration parameter</primary> - </indexterm> - </term> - <listitem> - <para> - Sets the directory to store temporary statistics data in. This can be - a path relative to the data directory or an absolute path. The default - is <filename>pg_stat_tmp</filename>. Pointing this at a RAM-based - file system will decrease physical I/O requirements and can lead to - improved performance. - This parameter can only be set in the <filename>postgresql.conf</filename> - file or on the server command line. - </para> - </listitem> - </varlistentry> - </variablelist> </sect2> diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml index 3234adb639..6bac5e075e 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -120,12 +120,6 @@ Item subsystem</entry> </row> -<row> - <entry><filename>pg_stat_tmp</filename></entry> - <entry>Subdirectory containing temporary files for the statistics - subsystem</entry> -</row> - <row> <entry><filename>pg_subtrans</filename></entry> <entry>Subdirectory containing subtransaction status data</entry> diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 68867c92b2..f62f06880e 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -99,16 +99,6 @@ bool pgstat_track_counts = false; int pgstat_track_functions = TRACK_FUNC_OFF; int pgstat_track_activity_query_size = 1024; -/* ---------- - * Built from GUC parameter - * ---------- - */ -char *pgstat_stat_directory = NULL; - -/* No longer used, but will be removed with GUC */ -char *pgstat_stat_filename = NULL; -char *pgstat_stat_tmpname = NULL; - /* * WAL usage counters saved from pgWALUsage at the previous call to * pgstat_send_wal(). This is used to calculate how much WAL usage diff --git a/src/backend/replication/basebackup.c b/src/backend/replication/basebackup.c index fa7bdbcefa..f0d75f55bd 100644 --- a/src/backend/replication/basebackup.c +++ b/src/backend/replication/basebackup.c @@ -87,9 +87,6 @@ static int basebackup_read_file(int fd, char *buf, size_t nbytes, off_t offset, /* Was the backup currently in-progress initiated in recovery mode? */ static bool backup_started_in_recovery = false; -/* Relative path of temporary statistics directory */ -static char *statrelpath = NULL; - /* * Size of each block sent into the tar stream for larger files. */ @@ -152,13 +149,6 @@ struct exclude_list_item */ static const char *const excludeDirContents[] = { - /* - * Skip temporary statistics files. PG_STAT_TMP_DIR must be skipped even - * when stats_temp_directory is set because PGSS_TEXT_FILE is always - * created there. - */ - PG_STAT_TMP_DIR, - /* * It is generally not useful to backup the contents of this directory * even if the intention is to restore to another primary. See backup.sgml @@ -261,7 +251,6 @@ perform_base_backup(basebackup_options *opt) StringInfo labelfile; StringInfo tblspc_map_file; backup_manifest_info manifest; - int datadirpathlen; List *tablespaces = NIL; backup_total = 0; @@ -284,8 +273,6 @@ perform_base_backup(basebackup_options *opt) Assert(CurrentResourceOwner == NULL); CurrentResourceOwner = ResourceOwnerCreate(NULL, "base backup"); - datadirpathlen = strlen(DataDir); - backup_started_in_recovery = RecoveryInProgress(); labelfile = makeStringInfo(); @@ -314,18 +301,6 @@ perform_base_backup(basebackup_options *opt) tablespaceinfo *ti; int tblspc_streamed = 0; - /* - * Calculate the relative path of temporary statistics directory in - * order to skip the files which are located in that directory later. - */ - if (is_absolute_path(pgstat_stat_directory) && - strncmp(pgstat_stat_directory, DataDir, datadirpathlen) == 0) - statrelpath = psprintf("./%s", pgstat_stat_directory + datadirpathlen + 1); - else if (strncmp(pgstat_stat_directory, "./", 2) != 0) - statrelpath = psprintf("./%s", pgstat_stat_directory); - else - statrelpath = pgstat_stat_directory; - /* Add a node for the base directory at the end */ ti = palloc0(sizeof(tablespaceinfo)); ti->size = -1; @@ -1377,17 +1352,6 @@ sendDir(const char *path, int basepathlen, bool sizeonly, List *tablespaces, if (excludeFound) continue; - /* - * Exclude contents of directory specified by statrelpath if not set - * to the default (pg_stat_tmp) which is caught in the loop above. - */ - if (statrelpath != NULL && strcmp(pathbuf, statrelpath) == 0) - { - elog(DEBUG1, "contents of directory \"%s\" excluded from backup", statrelpath); - size += _tarWriteDir(pathbuf, basepathlen, &statbuf, sizeonly); - continue; - } - /* * We can skip pg_wal, the WAL segments need to be fetched from the * WAL archive anyway. But include it as an empty directory anyway, so diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index c93dc63d98..4f500dbd06 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -203,7 +203,6 @@ static bool check_autovacuum_work_mem(int *newval, void **extra, GucSource sourc static bool check_effective_io_concurrency(int *newval, void **extra, GucSource source); static bool check_maintenance_io_concurrency(int *newval, void **extra, GucSource source); static bool check_huge_page_size(int *newval, void **extra, GucSource source); -static void assign_pgstat_temp_directory(const char *newval, void *extra); static bool check_application_name(char **newval, void **extra, GucSource source); static void assign_application_name(const char *newval, void *extra); static bool check_cluster_name(char **newval, void **extra, GucSource source); @@ -560,8 +559,6 @@ char *HbaFileName; char *IdentFileName; char *external_pid_file; -char *pgstat_temp_directory; - char *application_name; int tcp_keepalives_idle; @@ -11782,35 +11779,6 @@ check_huge_page_size(int *newval, void **extra, GucSource source) return true; } -static void -assign_pgstat_temp_directory(const char *newval, void *extra) -{ - /* check_canonical_path already canonicalized newval for us */ - char *dname; - char *tname; - char *fname; - - /* directory */ - dname = guc_malloc(ERROR, strlen(newval) + 1); /* runtime dir */ - sprintf(dname, "%s", newval); - - /* global stats */ - tname = guc_malloc(ERROR, strlen(newval) + 12); /* /global.tmp */ - sprintf(tname, "%s/global.tmp", newval); - fname = guc_malloc(ERROR, strlen(newval) + 13); /* /global.stat */ - sprintf(fname, "%s/global.stat", newval); - - if (pgstat_stat_directory) - free(pgstat_stat_directory); - pgstat_stat_directory = dname; - if (pgstat_stat_tmpname) - free(pgstat_stat_tmpname); - pgstat_stat_tmpname = tname; - if (pgstat_stat_filename) - free(pgstat_stat_filename); - pgstat_stat_filename = fname; -} - static bool check_application_name(char **newval, void **extra, GucSource source) { diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 9640349a91..40a18bb248 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -588,7 +588,6 @@ #track_io_timing = off #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) -#stats_temp_directory = 'pg_stat_tmp' # - Monitoring - diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c index 3c1cf78b4f..07a00b8d0d 100644 --- a/src/bin/initdb/initdb.c +++ b/src/bin/initdb/initdb.c @@ -219,7 +219,6 @@ static const char *const subdirs[] = { "pg_replslot", "pg_tblspc", "pg_stat", - "pg_stat_tmp", "pg_xact", "pg_logical", "pg_logical/snapshots", diff --git a/src/bin/pg_rewind/filemap.c b/src/bin/pg_rewind/filemap.c index 2618b4c957..ab5cb51de7 100644 --- a/src/bin/pg_rewind/filemap.c +++ b/src/bin/pg_rewind/filemap.c @@ -87,13 +87,6 @@ struct exclude_list_item */ static const char *excludeDirContents[] = { - /* - * Skip temporary statistics files. PG_STAT_TMP_DIR must be skipped even - * when stats_temp_directory is set because PGSS_TEXT_FILE is always - * created there. - */ - "pg_stat_tmp", /* defined as PG_STAT_TMP_DIR */ - /* * It is generally not useful to backup the contents of this directory * even if the intention is to restore to another primary. See backup.sgml diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 222104b88e..ce4feaea3b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -33,9 +33,6 @@ #define PGSTAT_STAT_PERMANENT_FILENAME "pg_stat/saved_stats" #define PGSTAT_STAT_PERMANENT_TMPFILE "pg_stat/saved_stats.tmp" -/* Default directory to store temporary statistics data in */ -#define PG_STAT_TMP_DIR "pg_stat_tmp" - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm index 9667f7667e..dd41a43b4e 100644 --- a/src/test/perl/PostgresNode.pm +++ b/src/test/perl/PostgresNode.pm @@ -455,10 +455,6 @@ sub init print $conf TestLib::slurp_file($ENV{TEMP_CONFIG}) if defined $ENV{TEMP_CONFIG}; - # XXX Neutralize any stats_temp_directory in TEMP_CONFIG. Nodes running - # concurrently must not share a stats_temp_directory. - print $conf "stats_temp_directory = 'pg_stat_tmp'\n"; - if ($params{allows_streaming}) { if ($params{allows_streaming} eq "logical") -- 2.27.0 ----Next_Part(Fri_Mar__5_17_18_56_2021_497)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v48-0007-Exclude-pg_stat-directory-from-base-backup.patch" ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: A new strategy for pull-up correlated ANY_SUBLINK @ 2023-04-05 07:15 Andy Fan <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Andy Fan @ 2023-04-05 07:15 UTC (permalink / raw) To: Tom Lane <[email protected]>; vignesh C <[email protected]>; Richard Guo <[email protected]>; Andrey Lepikhov <[email protected]>; +Cc: pgsql-hackers Hi Tom: Sorry for the delayed response! I think my knowledge has been refreshed for this discussion. > One thing I'm not at all clear about is whether we need to restrict > the optimization so that it doesn't occur if the subquery contains > outer references falling outside available_rels. I think that that > case is covered by is_simple_subquery() deciding later to not pull up > the subquery based on LATERAL restrictions, but maybe that misses > something. > I think we need the restriction and that should be enough for this feature . Given the query Richard provided before: explain select * from tenk1 A where exists (select 1 from tenk2 B where A.hundred in (select C.hundred FROM tenk2 C WHERE c.odd = b.odd)); It first can be converted to the below format without any issue. SELECT * FROM tenk1 A SEMI JOIN tenk2 B on A.hundred in (select C.hundred FROM tenk2 C WHERE c.odd = b.odd); Then without the restriction, since we only pull the varnos from sublink->testexpr, then it is {A}, so it convert to SELECT * FROM (tenk1 A SEMI JOIN LATERAL (SELECT c.hundred FROM tenk2 C) ON c.odd = b.odd AND a.hundred = v.hundred) SEMI JOIN on tenk2 B ON TRUE; then the above query is NOT A VALID QUERY since: 1. The above query is *not* same as SELECT * FROM (tenk1 A SEMI JOIN tenk2 B) on true SEMI JOIN LATERAL (SELECT c.hundred FROM tenk2 C) v ON v.odd = b.odd; 2. The above query requires b.odd when B is not available. So it is right that an optimizer can't generate a plan for it. The fix would be to do the restriction before applying this optimization. I'm not sure pull-up-subquery can play any role here, IIUC, the bad thing happens before pull-up-subquery. I also write & analyze more test and found no issue by me 1. SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); ==> should not be pull-up to rarg of the left join since A.hundred is not available. 2. SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); ==> should not be pull-up to rarg of the left join since A.odd is not available. 3. SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); ==> should be pull-up to rarg of left join. 4. SELECT * FROM tenk1 A INNER JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); ==> pull-up as expected. 5. SELECT * FROM tenk1 A RIGHT JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); ==> should not be pull-up into larg of left join since b.odd is not available. About the existing test case changes because of this patch, they do requires on the sublink is planned to a subPlan, so I introduces the below changes to keep the original intention. Changes A in (SELECT A FROM ..) To (A, random() > 0) in (SELECT a, random() > 0 FROM ..); I'm also wondering whether the similar restriction in > convert_EXISTS_sublink_to_join could be removed similarly. > In this light it was a mistake for convert_EXISTS_sublink_to_join > to manage the pullup itself rather than doing it in the two-step > fashion that convert_ANY_sublink_to_join does it. > > Yes, it is true! I prefer to believe this deserves a separate patch. Any feedback is welcome! -- Best Regards Andy Fan Attachments: [application/x-patch] v3-0001-Pull-up-direct-correlated-ANY_SUBLINK-using-later.patch (14.4K, ../../CAKU4AWpp8aANvwU1ivjPtF3AakFjTf1jBm_OFmPSdCqizjBQLw@mail.gmail.com/3-v3-0001-Pull-up-direct-correlated-ANY_SUBLINK-using-later.patch) download | inline diff: From 3e2c8b46928a7f246ee7066b9ad160cf18a2e952 Mon Sep 17 00:00:00 2001 From: Andy Fan <[email protected]> Date: Wed, 5 Apr 2023 14:45:11 +0800 Subject: [PATCH v3] Pull up direct-correlated ANY_SUBLINK using lateral join. --- .../postgres_fdw/expected/postgres_fdw.out | 6 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 4 +- src/backend/optimizer/plan/subselect.c | 19 +++- src/test/regress/expected/join.out | 14 +-- src/test/regress/expected/subselect.out | 102 ++++++++++++++++++ src/test/regress/sql/join.sql | 8 +- src/test/regress/sql/subselect.sql | 32 ++++++ 7 files changed, 164 insertions(+), 21 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 04a3ef450cf..d43f74e49bf 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -11563,7 +11563,7 @@ CREATE FOREIGN TABLE foreign_tbl (b int) CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl) SERVER loopback OPTIONS (table_name 'base_tbl'); EXPLAIN (VERBOSE, COSTS OFF) -SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl); +SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl); QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on public.base_tbl @@ -11571,7 +11571,7 @@ SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl); Filter: (SubPlan 1) SubPlan 1 -> Result - Output: base_tbl.a + Output: base_tbl.a, (random() > '0'::double precision) -> Append -> Async Foreign Scan on public.foreign_tbl foreign_tbl_1 Remote SQL: SELECT NULL FROM public.base_tbl @@ -11579,7 +11579,7 @@ SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl); Remote SQL: SELECT NULL FROM public.base_tbl (11 rows) -SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl); +SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl); a --- 1 diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 4f3088c03ea..8d775fdcf57 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3816,8 +3816,8 @@ CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl) SERVER loopback OPTIONS (table_name 'base_tbl'); EXPLAIN (VERBOSE, COSTS OFF) -SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl); -SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl); +SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl); +SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl); -- Clean up DROP FOREIGN TABLE foreign_tbl CASCADE; diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 052263aea6d..4708a5363ac 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -1279,15 +1279,24 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink, List *subquery_vars; Node *quals; ParseState *pstate; + Relids sub_ref_outer_relids = NULL; + bool use_lateral = false; Assert(sublink->subLinkType == ANY_SUBLINK); /* - * The sub-select must not refer to any Vars of the parent query. (Vars of - * higher levels should be okay, though.) + * If the sub-select refers to any Vars of the parent query, we have to + * treat it as LATERAL. (Vars of higher levels don't matter here.) */ - if (contain_vars_of_level((Node *) subselect, 1)) - return NULL; + sub_ref_outer_relids = pull_varnos_of_level(NULL, (Node *) subselect, 1); + + if (!bms_is_empty(sub_ref_outer_relids)) + { + if (bms_is_subset(sub_ref_outer_relids, available_rels)) + use_lateral = true; + else + return NULL; + } /* * The test expression must contain some Vars of the parent query, else @@ -1324,7 +1333,7 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink, nsitem = addRangeTableEntryForSubquery(pstate, subselect, makeAlias("ANY_subquery", NIL), - false, + use_lateral, false); rte = nsitem->p_rte; parse->rtable = lappend(parse->rtable, rte); diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 5d59ed7890f..317d9240dfd 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4898,7 +4898,7 @@ reset enable_nestloop; explain (costs off) select a.unique1, b.unique2 from onek a left join onek b on a.unique1 = b.unique2 - where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1); QUERY PLAN ---------------------------------------------------------- Hash Join @@ -4914,7 +4914,7 @@ select a.unique1, b.unique2 select a.unique1, b.unique2 from onek a left join onek b on a.unique1 = b.unique2 - where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1); unique1 | unique2 ---------+--------- 123 | 123 @@ -6686,12 +6686,12 @@ select * from (values (0), (1)) v(id), lateral (select * from int8_tbl t1, lateral (select * from (select * from int8_tbl t2 - where q1 = any (select q2 from int8_tbl t3 + where (q1, random() > 0) = any (select q2, random() > 0 from int8_tbl t3 where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; - QUERY PLAN ----------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------- Nested Loop Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2 -> Seq Scan on public.int8_tbl t1 @@ -6708,7 +6708,7 @@ lateral (select * from int8_tbl t1, Filter: (SubPlan 3) SubPlan 3 -> Result - Output: t3.q2 + Output: t3.q2, (random() > '0'::double precision) One-Time Filter: $4 InitPlan 1 (returns $2) -> Result @@ -6725,7 +6725,7 @@ select * from (values (0), (1)) v(id), lateral (select * from int8_tbl t1, lateral (select * from (select * from int8_tbl t2 - where q1 = any (select q2 from int8_tbl t3 + where (q1, random() > 0) = any (select q2, random() > 0 from int8_tbl t3 where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 22af8fafa17..49325058c5d 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1926,3 +1926,105 @@ select * from x for update; Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3 (2 rows) +-- Pull-up the direct-correlated ANY_SUBLINK +explain (costs off) +select * from tenk1 A where hundred in (select hundred from tenk2 B where B.odd = A.odd); + QUERY PLAN +------------------------------------------------------------ + Hash Join + Hash Cond: ((a.odd = b.odd) AND (a.hundred = b.hundred)) + -> Seq Scan on tenk1 a + -> Hash + -> HashAggregate + Group Key: b.odd, b.hundred + -> Seq Scan on tenk2 b +(7 rows) + +explain (costs off) +select * from tenk1 A where exists +(select 1 from tenk2 B +where A.hundred in (select C.hundred FROM tenk2 C +WHERE c.odd = b.odd)); + QUERY PLAN +--------------------------------- + Nested Loop Semi Join + Join Filter: (SubPlan 1) + -> Seq Scan on tenk1 a + -> Materialize + -> Seq Scan on tenk2 b + SubPlan 1 + -> Seq Scan on tenk2 c + Filter: (odd = b.odd) +(8 rows) + +-- we should only try to pull up the sublink into RHS of a left join +-- but a.hundred is not avaiable. +explain (costs off) +SELECT * FROM tenk1 A LEFT JOIN tenk2 B +ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); + QUERY PLAN +--------------------------------- + Nested Loop Left Join + Join Filter: (SubPlan 1) + -> Seq Scan on tenk1 a + -> Materialize + -> Seq Scan on tenk2 b + SubPlan 1 + -> Seq Scan on tenk2 c + Filter: (odd = b.odd) +(8 rows) + +-- we should only try to pull up the sublink into RHS of a left join +-- but a.odd is not avaiable for this. +explain (costs off) +SELECT * FROM tenk1 A LEFT JOIN tenk2 B +ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); + QUERY PLAN +--------------------------------- + Nested Loop Left Join + Join Filter: (SubPlan 1) + -> Seq Scan on tenk1 a + -> Materialize + -> Seq Scan on tenk2 b + SubPlan 1 + -> Seq Scan on tenk2 c + Filter: (odd = a.odd) +(8 rows) + +-- should be able to pull up since all the references is available +explain (costs off) +SELECT * FROM tenk1 A LEFT JOIN tenk2 B +ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); + QUERY PLAN +------------------------------------------------------------------------ + Nested Loop Left Join + -> Seq Scan on tenk1 a + -> Materialize + -> Hash Join + Hash Cond: ((b.odd = c.odd) AND (b.hundred = c.hundred)) + -> Seq Scan on tenk2 b + -> Hash + -> HashAggregate + Group Key: c.odd, c.hundred + -> Seq Scan on tenk2 c +(10 rows) + +-- we can pull up the sublink into the inner JoinExpr. +explain (costs off) +SELECT * FROM tenk1 A INNER JOIN tenk2 B +ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); + QUERY PLAN +------------------------------------------------- + Hash Join + Hash Cond: (c.odd = b.odd) + -> Hash Join + Hash Cond: (a.hundred = c.hundred) + -> Seq Scan on tenk1 a + -> Hash + -> HashAggregate + Group Key: c.odd, c.hundred + -> Seq Scan on tenk2 c + -> Hash + -> Seq Scan on tenk2 b +(11 rows) + diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index a630f58b571..e91d25c0915 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1721,11 +1721,11 @@ reset enable_nestloop; explain (costs off) select a.unique1, b.unique2 from onek a left join onek b on a.unique1 = b.unique2 - where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1); select a.unique1, b.unique2 from onek a left join onek b on a.unique1 = b.unique2 - where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1); -- -- test full-join strength reduction @@ -2373,7 +2373,7 @@ select * from (values (0), (1)) v(id), lateral (select * from int8_tbl t1, lateral (select * from (select * from int8_tbl t2 - where q1 = any (select q2 from int8_tbl t3 + where (q1, random() > 0) = any (select q2, random() > 0 from int8_tbl t3 where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; @@ -2382,7 +2382,7 @@ select * from (values (0), (1)) v(id), lateral (select * from int8_tbl t1, lateral (select * from (select * from int8_tbl t2 - where q1 = any (select q2 from int8_tbl t3 + where (q1, random() > 0) = any (select q2, random() > 0 from int8_tbl t3 where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 40276708c99..b2dd094983d 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -968,3 +968,35 @@ select * from (with x as (select 2 as y) select * from x) ss; explain (verbose, costs off) with x as (select * from subselect_tbl) select * from x for update; + +-- Pull-up the direct-correlated ANY_SUBLINK +explain (costs off) +select * from tenk1 A where hundred in (select hundred from tenk2 B where B.odd = A.odd); + +explain (costs off) +select * from tenk1 A where exists +(select 1 from tenk2 B +where A.hundred in (select C.hundred FROM tenk2 C +WHERE c.odd = b.odd)); + +-- we should only try to pull up the sublink into RHS of a left join +-- but a.hundred is not avaiable. +explain (costs off) +SELECT * FROM tenk1 A LEFT JOIN tenk2 B +ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); + +-- we should only try to pull up the sublink into RHS of a left join +-- but a.odd is not avaiable for this. +explain (costs off) +SELECT * FROM tenk1 A LEFT JOIN tenk2 B +ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); + +-- should be able to pull up since all the references is available +explain (costs off) +SELECT * FROM tenk1 A LEFT JOIN tenk2 B +ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); + +-- we can pull up the sublink into the inner JoinExpr. +explain (costs off) +SELECT * FROM tenk1 A INNER JOIN tenk2 B +ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); -- 2.21.0 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-04-05 07:15 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-09-29 13:59 [PATCH v48 6/7] Remove the GUC stats_temp_directory Kyotaro Horiguchi <[email protected]> 2023-04-05 07:15 Re: A new strategy for pull-up correlated ANY_SUBLINK 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