agora inbox for [email protected]help / color / mirror / Atom feed
file cloning in pg_upgrade and CREATE DATABASE 21+ messages / 7 participants [nested] [flat]
* file cloning in pg_upgrade and CREATE DATABASE @ 2018-02-21 03:00 Peter Eisentraut <[email protected]> 0 siblings, 3 replies; 21+ messages in thread From: Peter Eisentraut @ 2018-02-21 03:00 UTC (permalink / raw) To: pgsql-hackers Here is another attempt at implementing file cloning for pg_upgrade and CREATE DATABASE. The idea is to take advantage of file systems that can make copy-on-write clones, which would make the copy run much faster. For pg_upgrade, this will give the performance of --link mode without the associated drawbacks. There have been patches proposed previously [0][1]. The concerns there were mainly that they required a Linux-specific ioctl() call and only worked for Btrfs. Some new things have happened since then: - XFS has (optional) reflink support. This file system is probably more widely used than Btrfs. - Linux and glibc have a proper function to do this now. - APFS on macOS supports file cloning. So altogether this feature will be more widely usable and less ugly to implement. Note, however, that you will currently need literally the latest glibc release, so it probably won't be accessible right now unless you are using Fedora 28 for example. (This is the copy_file_range() function that had us recently rename the same function in pg_rewind.) Some example measurements: 6 GB database, pg_upgrade unpatched 30 seconds, patched 3 seconds (XFS and APFS) similar for a CREATE DATABASE from a large template Even if you don't have a file system with cloning support, the special library calls make copying faster. For example, on APFS, in this example, an unpatched CREATE DATABASE takes 30 seconds, with the library call (but without cloning) it takes 10 seconds. For amusement/bewilderment, without the recent flush optimization on APFS, this takes 2 minutes 30 seconds. I suppose this optimization will now actually obsolete, since macOS will no longer hit that code. [0]: https://www.postgresql.org/message-id/flat/513C0E7C.5080606%40socialserve.com [1]: https://www.postgresql.org/message-id/flat/20140213030731.GE4831%40momjian.us -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services From 56b5b574f6d900d5eb4932be499cf3bae0e7ba86 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Tue, 20 Feb 2018 10:41:16 -0500 Subject: [PATCH] Use file cloning in pg_upgrade and CREATE DATABASE For file copying in pg_upgrade and CREATE DATABASE, use special file cloning calls if available. This makes the copying faster and more space efficient. For pg_upgrade, this achieves speed similar to --link mode without the associated drawbacks. On Linux, use copy_file_range(). This supports file cloning automatically on Btrfs and XFS (if formatted with reflink support). On macOS, use copyfile(), which supports file cloning on APFS. Even on file systems without cloning/reflink support, this is faster than the existing code, because it avoids copying the file contents out of kernel space and allows the OS to apply other optimizations. --- configure | 2 +- configure.in | 2 +- doc/src/sgml/ref/pgupgrade.sgml | 11 ++++++++ src/backend/storage/file/copydir.c | 55 +++++++++++++++++++++++++++++++++----- src/bin/pg_upgrade/file.c | 37 ++++++++++++++++++++++++- src/include/pg_config.h.in | 6 +++++ 6 files changed, 104 insertions(+), 9 deletions(-) diff --git a/configure b/configure index 7dcca506f8..eb8b321723 100755 --- a/configure +++ b/configure @@ -13079,7 +13079,7 @@ fi LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -for ac_func in cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l +for ac_func in cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l do : as_ac_var=`$as_echo "ac_cv_func_$ac_func" | $as_tr_sh` ac_fn_c_check_func "$LINENO" "$ac_func" "$as_ac_var" diff --git a/configure.in b/configure.in index 4d26034579..dfe3507b25 100644 --- a/configure.in +++ b/configure.in @@ -1425,7 +1425,7 @@ PGAC_FUNC_WCSTOMBS_L LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -AC_CHECK_FUNCS([cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) +AC_CHECK_FUNCS([cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) AC_REPLACE_FUNCS(fseeko) case $host_os in diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index 6dafb404a1..3873e71dd1 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -737,6 +737,17 @@ <title>Notes</title> is down. </para> + <para> + In PostgreSQL 11 and later, <application>pg_upgrade</application> + automatically uses efficient file cloning (also known as + <quote>reflinks</quote>) on some operating systems and file systems. This + can result in near-instantaneous copying of the data files, giving the + speed advantages of <option>-k</option>/<option>--link</option> while + leaving the old cluster untouched. At present, this is supported on Linux + (kernel 4.5 or later, glibc 2.27 or later) with Btrfs and XFS (on file + systems created with reflink support, which is not the default for XFS at + this writing), and on macOS with APFS. + </para> </refsect1> <refsect1> diff --git a/src/backend/storage/file/copydir.c b/src/backend/storage/file/copydir.c index ca6342db0d..cd6398d69a 100644 --- a/src/backend/storage/file/copydir.c +++ b/src/backend/storage/file/copydir.c @@ -21,6 +21,9 @@ #include <fcntl.h> #include <unistd.h> #include <sys/stat.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #include "storage/copydir.h" #include "storage/fd.h" @@ -74,7 +77,22 @@ copydir(char *fromdir, char *todir, bool recurse) copydir(fromfile, tofile, true); } else if (S_ISREG(fst.st_mode)) + { +#ifdef HAVE_COPYFILE + if (copyfile(fromfile, tofile, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", fromfile, tofile))); +#else copy_file(fromfile, tofile); +#endif + } } FreeDir(xldir); @@ -126,12 +144,17 @@ copydir(char *fromdir, char *todir, bool recurse) void copy_file(char *fromfile, char *tofile) { - char *buffer; int srcfd; int dstfd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else + char *buffer; int nbytes; off_t offset; off_t flush_offset; +#endif /* Size of copy buffer (read and write requests) */ #define COPY_BUF_SIZE (8 * BLCKSZ) @@ -148,9 +171,6 @@ copy_file(char *fromfile, char *tofile) #define FLUSH_DISTANCE (1024 * 1024) #endif - /* Use palloc to ensure we get a maxaligned buffer */ - buffer = palloc(COPY_BUF_SIZE); - /* * Open the files */ @@ -166,6 +186,28 @@ copy_file(char *fromfile, char *tofile) (errcode_for_file_access(), errmsg("could not create file \"%s\": %m", tofile))); +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(srcfd, &stat) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not stat file \"%s\": %m", fromfile))); + + len = stat.st_size; + + do { + ssize_t ret = copy_file_range(srcfd, NULL, dstfd, NULL, len, 0); + if (ret < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", + fromfile, tofile))); + + len -= ret; + } while (len > 0); +#else + /* Use palloc to ensure we get a maxaligned buffer */ + buffer = palloc(COPY_BUF_SIZE); + /* * Do the data copying. */ @@ -213,12 +255,13 @@ copy_file(char *fromfile, char *tofile) if (offset > flush_offset) pg_flush_data(dstfd, flush_offset, offset - flush_offset); + pfree(buffer); +#endif + if (CloseTransientFile(dstfd)) ereport(ERROR, (errcode_for_file_access(), errmsg("could not close file \"%s\": %m", tofile))); CloseTransientFile(srcfd); - - pfree(buffer); } diff --git a/src/bin/pg_upgrade/file.c b/src/bin/pg_upgrade/file.c index f38bfacf02..f05fd9db9c 100644 --- a/src/bin/pg_upgrade/file.c +++ b/src/bin/pg_upgrade/file.c @@ -17,6 +17,9 @@ #include <sys/stat.h> #include <fcntl.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #ifdef WIN32 @@ -34,10 +37,25 @@ void copyFile(const char *src, const char *dst, const char *schemaName, const char *relName) { -#ifndef WIN32 +#ifdef HAVE_COPYFILE + if (copyfile(src, dst, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ) < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); +#elif !defined(WIN32) int src_fd; int dest_fd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else char *buffer; +#endif if ((src_fd = open(src, O_RDONLY | PG_BINARY, 0)) < 0) pg_fatal("error while copying relation \"%s.%s\": could not open file \"%s\": %s\n", @@ -48,6 +66,22 @@ copyFile(const char *src, const char *dst, pg_fatal("error while copying relation \"%s.%s\": could not create file \"%s\": %s\n", schemaName, relName, dst, strerror(errno)); +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(src_fd, &stat) < 0) + pg_fatal("could not stat file \"%s\": %s", + src, strerror(errno)); + + len = stat.st_size; + + do { + ssize_t ret = copy_file_range(src_fd, NULL, dest_fd, NULL, len, 0); + if (ret < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); + + len -= ret; + } while (len > 0); +#else /* copy in fairly large chunks for best efficiency */ #define COPY_BUF_SIZE (50 * BLCKSZ) @@ -77,6 +111,7 @@ copyFile(const char *src, const char *dst, } pg_free(buffer); +#endif close(src_fd); close(dest_fd); diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in index f98f773ff0..38e88e0395 100644 --- a/src/include/pg_config.h.in +++ b/src/include/pg_config.h.in @@ -114,6 +114,12 @@ /* Define to 1 if your compiler handles computed gotos. */ #undef HAVE_COMPUTED_GOTO +/* Define to 1 if you have the `copyfile' function. */ +#undef HAVE_COPYFILE + +/* Define to 1 if you have the `copy_file_range' function. */ +#undef HAVE_COPY_FILE_RANGE + /* Define to 1 if you have the <crtdefs.h> header file. */ #undef HAVE_CRTDEFS_H base-commit: 9a44a26b65d3d36867267624b76d3dea3dc4f6f6 -- 2.16.2 Attachments: [text/plain] 0001-Use-file-cloning-in-pg_upgrade-and-CREATE-DATABASE.patch (8.8K, ../../[email protected]/2-0001-Use-file-cloning-in-pg_upgrade-and-CREATE-DATABASE.patch) download | inline diff: From 56b5b574f6d900d5eb4932be499cf3bae0e7ba86 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Tue, 20 Feb 2018 10:41:16 -0500 Subject: [PATCH] Use file cloning in pg_upgrade and CREATE DATABASE For file copying in pg_upgrade and CREATE DATABASE, use special file cloning calls if available. This makes the copying faster and more space efficient. For pg_upgrade, this achieves speed similar to --link mode without the associated drawbacks. On Linux, use copy_file_range(). This supports file cloning automatically on Btrfs and XFS (if formatted with reflink support). On macOS, use copyfile(), which supports file cloning on APFS. Even on file systems without cloning/reflink support, this is faster than the existing code, because it avoids copying the file contents out of kernel space and allows the OS to apply other optimizations. --- configure | 2 +- configure.in | 2 +- doc/src/sgml/ref/pgupgrade.sgml | 11 ++++++++ src/backend/storage/file/copydir.c | 55 +++++++++++++++++++++++++++++++++----- src/bin/pg_upgrade/file.c | 37 ++++++++++++++++++++++++- src/include/pg_config.h.in | 6 +++++ 6 files changed, 104 insertions(+), 9 deletions(-) diff --git a/configure b/configure index 7dcca506f8..eb8b321723 100755 --- a/configure +++ b/configure @@ -13079,7 +13079,7 @@ fi LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -for ac_func in cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l +for ac_func in cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l do : as_ac_var=`$as_echo "ac_cv_func_$ac_func" | $as_tr_sh` ac_fn_c_check_func "$LINENO" "$ac_func" "$as_ac_var" diff --git a/configure.in b/configure.in index 4d26034579..dfe3507b25 100644 --- a/configure.in +++ b/configure.in @@ -1425,7 +1425,7 @@ PGAC_FUNC_WCSTOMBS_L LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -AC_CHECK_FUNCS([cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) +AC_CHECK_FUNCS([cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) AC_REPLACE_FUNCS(fseeko) case $host_os in diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index 6dafb404a1..3873e71dd1 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -737,6 +737,17 @@ <title>Notes</title> is down. </para> + <para> + In PostgreSQL 11 and later, <application>pg_upgrade</application> + automatically uses efficient file cloning (also known as + <quote>reflinks</quote>) on some operating systems and file systems. This + can result in near-instantaneous copying of the data files, giving the + speed advantages of <option>-k</option>/<option>--link</option> while + leaving the old cluster untouched. At present, this is supported on Linux + (kernel 4.5 or later, glibc 2.27 or later) with Btrfs and XFS (on file + systems created with reflink support, which is not the default for XFS at + this writing), and on macOS with APFS. + </para> </refsect1> <refsect1> diff --git a/src/backend/storage/file/copydir.c b/src/backend/storage/file/copydir.c index ca6342db0d..cd6398d69a 100644 --- a/src/backend/storage/file/copydir.c +++ b/src/backend/storage/file/copydir.c @@ -21,6 +21,9 @@ #include <fcntl.h> #include <unistd.h> #include <sys/stat.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #include "storage/copydir.h" #include "storage/fd.h" @@ -74,7 +77,22 @@ copydir(char *fromdir, char *todir, bool recurse) copydir(fromfile, tofile, true); } else if (S_ISREG(fst.st_mode)) + { +#ifdef HAVE_COPYFILE + if (copyfile(fromfile, tofile, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", fromfile, tofile))); +#else copy_file(fromfile, tofile); +#endif + } } FreeDir(xldir); @@ -126,12 +144,17 @@ copydir(char *fromdir, char *todir, bool recurse) void copy_file(char *fromfile, char *tofile) { - char *buffer; int srcfd; int dstfd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else + char *buffer; int nbytes; off_t offset; off_t flush_offset; +#endif /* Size of copy buffer (read and write requests) */ #define COPY_BUF_SIZE (8 * BLCKSZ) @@ -148,9 +171,6 @@ copy_file(char *fromfile, char *tofile) #define FLUSH_DISTANCE (1024 * 1024) #endif - /* Use palloc to ensure we get a maxaligned buffer */ - buffer = palloc(COPY_BUF_SIZE); - /* * Open the files */ @@ -166,6 +186,28 @@ copy_file(char *fromfile, char *tofile) (errcode_for_file_access(), errmsg("could not create file \"%s\": %m", tofile))); +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(srcfd, &stat) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not stat file \"%s\": %m", fromfile))); + + len = stat.st_size; + + do { + ssize_t ret = copy_file_range(srcfd, NULL, dstfd, NULL, len, 0); + if (ret < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", + fromfile, tofile))); + + len -= ret; + } while (len > 0); +#else + /* Use palloc to ensure we get a maxaligned buffer */ + buffer = palloc(COPY_BUF_SIZE); + /* * Do the data copying. */ @@ -213,12 +255,13 @@ copy_file(char *fromfile, char *tofile) if (offset > flush_offset) pg_flush_data(dstfd, flush_offset, offset - flush_offset); + pfree(buffer); +#endif + if (CloseTransientFile(dstfd)) ereport(ERROR, (errcode_for_file_access(), errmsg("could not close file \"%s\": %m", tofile))); CloseTransientFile(srcfd); - - pfree(buffer); } diff --git a/src/bin/pg_upgrade/file.c b/src/bin/pg_upgrade/file.c index f38bfacf02..f05fd9db9c 100644 --- a/src/bin/pg_upgrade/file.c +++ b/src/bin/pg_upgrade/file.c @@ -17,6 +17,9 @@ #include <sys/stat.h> #include <fcntl.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #ifdef WIN32 @@ -34,10 +37,25 @@ void copyFile(const char *src, const char *dst, const char *schemaName, const char *relName) { -#ifndef WIN32 +#ifdef HAVE_COPYFILE + if (copyfile(src, dst, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ) < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); +#elif !defined(WIN32) int src_fd; int dest_fd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else char *buffer; +#endif if ((src_fd = open(src, O_RDONLY | PG_BINARY, 0)) < 0) pg_fatal("error while copying relation \"%s.%s\": could not open file \"%s\": %s\n", @@ -48,6 +66,22 @@ copyFile(const char *src, const char *dst, pg_fatal("error while copying relation \"%s.%s\": could not create file \"%s\": %s\n", schemaName, relName, dst, strerror(errno)); +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(src_fd, &stat) < 0) + pg_fatal("could not stat file \"%s\": %s", + src, strerror(errno)); + + len = stat.st_size; + + do { + ssize_t ret = copy_file_range(src_fd, NULL, dest_fd, NULL, len, 0); + if (ret < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); + + len -= ret; + } while (len > 0); +#else /* copy in fairly large chunks for best efficiency */ #define COPY_BUF_SIZE (50 * BLCKSZ) @@ -77,6 +111,7 @@ copyFile(const char *src, const char *dst, } pg_free(buffer); +#endif close(src_fd); close(dest_fd); diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in index f98f773ff0..38e88e0395 100644 --- a/src/include/pg_config.h.in +++ b/src/include/pg_config.h.in @@ -114,6 +114,12 @@ /* Define to 1 if your compiler handles computed gotos. */ #undef HAVE_COMPUTED_GOTO +/* Define to 1 if you have the `copyfile' function. */ +#undef HAVE_COPYFILE + +/* Define to 1 if you have the `copy_file_range' function. */ +#undef HAVE_COPY_FILE_RANGE + /* Define to 1 if you have the <crtdefs.h> header file. */ #undef HAVE_CRTDEFS_H base-commit: 9a44a26b65d3d36867267624b76d3dea3dc4f6f6 -- 2.16.2 ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-02-21 16:33 Robert Haas <[email protected]> parent: Peter Eisentraut <[email protected]> 2 siblings, 0 replies; 21+ messages in thread From: Robert Haas @ 2018-02-21 16:33 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On Tue, Feb 20, 2018 at 10:00 PM, Peter Eisentraut <[email protected]> wrote: > Some example measurements: > > 6 GB database, pg_upgrade unpatched 30 seconds, patched 3 seconds (XFS > and APFS) > > similar for a CREATE DATABASE from a large template > > Even if you don't have a file system with cloning support, the special > library calls make copying faster. For example, on APFS, in this > example, an unpatched CREATE DATABASE takes 30 seconds, with the library > call (but without cloning) it takes 10 seconds. Nice results. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-02-21 23:57 Tomas Vondra <[email protected]> parent: Peter Eisentraut <[email protected]> 2 siblings, 1 reply; 21+ messages in thread From: Tomas Vondra @ 2018-02-21 23:57 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; pgsql-hackers On 02/21/2018 04:00 AM, Peter Eisentraut wrote: > ... > > Some example measurements: > > 6 GB database, pg_upgrade unpatched 30 seconds, patched 3 seconds (XFS > and APFS) > > similar for a CREATE DATABASE from a large template > Nice improvement, of course. How does that affect performance on the cloned database? If I understand this correctly, it essentially enables CoW on the files, so what's the overhead on that? It'd be unfortunate to speed up CREATE DATABASE only to get degraded performance later. In any case, I find this interesting mainly for pg_upgrade use case. On running systems I think the main issue with CREATE DATABASE is that it forces a checkpoint. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-02-22 22:49 Peter Eisentraut <[email protected]> parent: Tomas Vondra <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Peter Eisentraut @ 2018-02-22 22:49 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; pgsql-hackers On 2/21/18 18:57, Tomas Vondra wrote: > Nice improvement, of course. How does that affect performance on the > cloned database? If I understand this correctly, it essentially enables > CoW on the files, so what's the overhead on that? It'd be unfortunate to > speed up CREATE DATABASE only to get degraded performance later. I ran a little test (on APFS and XFS): Create a large (unlogged) table, copy the database, then delete everything from the table in the copy. That should need to CoW all the blocks. It has about the same performance with cloning, possibly slightly faster. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-19 07:06 Michael Paquier <[email protected]> parent: Peter Eisentraut <[email protected]> 2 siblings, 1 reply; 21+ messages in thread From: Michael Paquier @ 2018-03-19 07:06 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On Tue, Feb 20, 2018 at 10:00:04PM -0500, Peter Eisentraut wrote: > Some new things have happened since then: > > - XFS has (optional) reflink support. This file system is probably more > widely used than Btrfs. Btrfs is still in development, there are I think no many people who would use it in production. > - Linux and glibc have a proper function to do this now. > > - APFS on macOS supports file cloning. So copyfile() is only part of macos? I am not able to find references in FreeBSD, NetBSD or OpenBSD, but I may be missing something. > So altogether this feature will be more widely usable and less ugly to > implement. Note, however, that you will currently need literally the > latest glibc release, so it probably won't be accessible right now > unless you are using Fedora 28 for example. (This is the > copy_file_range() function that had us recently rename the same function > in pg_rewind.) For reference, Debian SID is using glibc 2.27. ArchLinux is still on 2.26. > Some example measurements: > > 6 GB database, pg_upgrade unpatched 30 seconds, patched 3 seconds (XFS > and APFS) Interesting. I'll try to test that on an XFS partition and see if I can see a difference. For now I have just read through the patch. +#ifdef HAVE_COPYFILE + if (copyfile(fromfile, tofile, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", fromfile, tofile))); +#else copy_file(fromfile, tofile); +#endif Any backend-side callers of copy_file() would not benefit from copyfile() on OSX. Shouldn't all that handling be inside copy_file(), similarly to what your patch actually does for pg_upgrade? I think that you should also consider fcopyfile() instead of copyfile() as it works directly on the file descriptors and share the same error handling as the others. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-19 07:14 Michael Paquier <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 21+ messages in thread From: Michael Paquier @ 2018-03-19 07:14 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On Mon, Mar 19, 2018 at 04:06:36PM +0900, Michael Paquier wrote: > Any backend-side callers of copy_file() would not benefit from > copyfile() on OSX. Shouldn't all that handling be inside copy_file(), > similarly to what your patch actually does for pg_upgrade? I think that > you should also consider fcopyfile() instead of copyfile() as it works > directly on the file descriptors and share the same error handling as > the others. Two other things I have noticed as well: 1) src/bin/pg_rewind/copy_fetch.c could benefit from similar speed-ups I think when copying data from source to target using the local mode of pg_rewind. This could really improve cases where new relations are added after a promotion. 2) XLogFileCopy() uses a copy logic as well. For large segments things could be improved, however we need to be careful about filling in the end of segments with zeros. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-20 02:58 Michael Paquier <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 21+ messages in thread From: Michael Paquier @ 2018-03-20 02:58 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On Mon, Mar 19, 2018 at 04:14:15PM +0900, Michael Paquier wrote: > Two other things I have noticed as well: > 1) src/bin/pg_rewind/copy_fetch.c could benefit from similar speed-ups I > think when copying data from source to target using the local mode of > pg_rewind. This could really improve cases where new relations are > added after a promotion. > 2) XLogFileCopy() uses a copy logic as well. For large segments things > could be improved, however we need to be careful about filling in the > end of segments with zeros. I have been thinking about this patch over the night, and here is a list of bullet points which would be nice to tackle: - Remove the current diff in copydir. - Extend copy_file so as it is able to use fcopyfile. - Move the work done in pg_upgrade into a common API which can as well be used by pg_rewind as well. One place would be to have a frontend-only API in src/common which does the leg work. I would recommend working only on file descriptors as well for consistency with copy_file_range. - Add proper wait events for the backend calls. Those are missing for copy_file_range and copyfile. - For XLogFileCopy, the problem may be trickier as the tail of a segment is filled with zeroes, so dropping it from the first version of the patch sounds wiser. Patch is switched as waiting on author, I have set myself as a reviewer. Thanks, -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-20 14:55 Peter Eisentraut <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 2 replies; 21+ messages in thread From: Peter Eisentraut @ 2018-03-20 14:55 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-hackers On 3/19/18 22:58, Michael Paquier wrote: > I have been thinking about this patch over the night, and here is a list > of bullet points which would be nice to tackle: > - Remove the current diff in copydir. done > - Extend copy_file so as it is able to use fcopyfile. fcopyfile() does not support cloning. (This is not documented.) > - Move the work done in pg_upgrade into a common API which can as well > be used by pg_rewind as well. One place would be to have a > frontend-only API in src/common which does the leg work. I would > recommend working only on file descriptors as well for consistency with > copy_file_range. pg_upgrade copies files, whereas pg_rewind needs to copy file ranges. So I don't think this is going to be a good match. We could add support for using Linux copy_file_range() in pg_rewind, but that would work a bit differently. I also don't have a good sense of how to test the performance of that. Another thing to think about is that we go through some trouble to initialize new WAL files so that the disk space is fully allocated. If we used file cloning calls in pg_rewind, that would potentially invalidate some of that. At least, we'd have to think through this more carefully. > - Add proper wait events for the backend calls. Those are missing for > copy_file_range and copyfile. done > - For XLogFileCopy, the problem may be trickier as the tail of a segment > is filled with zeroes, so dropping it from the first version of the > patch sounds wiser. Seems like a possible follow-on project. But see also under pg_rewind above. Another oddity is that pg_upgrade uses CopyFile() on Windows, but the backend does not. The Git log shows that the backend used to use CopyFile(), but that was then removed when the generic code was added, but when pg_upgrade was imported, it came with the CopyFile() call. I suspect the CopyFile() call can be quite a bit faster, so we should consider adding it back in. Or if not, remove it from pg_upgrade. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services From bd8fe105f6b1c64098e344c4a7d0fc9c94d2e31d Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Tue, 20 Mar 2018 10:21:47 -0400 Subject: [PATCH v2] Use file cloning in pg_upgrade and CREATE DATABASE For file copying in pg_upgrade and CREATE DATABASE, use special file cloning calls if available. This makes the copying faster and more space efficient. For pg_upgrade, this achieves speed similar to --link mode without the associated drawbacks. Other backend users of copydir.c will also take advantage of these changes, but the performance improvement will probably not be as noticeable there. On Linux, use copy_file_range(). This supports file cloning automatically on Btrfs and XFS (if formatted with reflink support). On macOS, use copyfile(), which supports file cloning on APFS. Even on file systems without cloning/reflink support, this is faster than the existing code, because it avoids copying the file contents out of kernel space and allows the OS to apply other optimizations. --- configure | 2 +- configure.in | 2 +- doc/src/sgml/monitoring.sgml | 8 +++- doc/src/sgml/ref/pgupgrade.sgml | 11 +++++ src/backend/postmaster/pgstat.c | 3 ++ src/backend/storage/file/copydir.c | 84 ++++++++++++++++++++++++++++++-------- src/backend/storage/file/reinit.c | 3 +- src/bin/pg_upgrade/file.c | 56 +++++++++++++++++++------ src/include/pg_config.h.in | 6 +++ src/include/pgstat.h | 1 + 10 files changed, 141 insertions(+), 35 deletions(-) diff --git a/configure b/configure index 3943711283..f27c78f63a 100755 --- a/configure +++ b/configure @@ -13085,7 +13085,7 @@ fi LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -for ac_func in cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l +for ac_func in cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l do : as_ac_var=`$as_echo "ac_cv_func_$ac_func" | $as_tr_sh` ac_fn_c_check_func "$LINENO" "$ac_func" "$as_ac_var" diff --git a/configure.in b/configure.in index 1babdbb755..7eb8673753 100644 --- a/configure.in +++ b/configure.in @@ -1428,7 +1428,7 @@ PGAC_FUNC_WCSTOMBS_L LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -AC_CHECK_FUNCS([cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) +AC_CHECK_FUNCS([cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) AC_REPLACE_FUNCS(fseeko) case $host_os in diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 3bc4de57d5..02029e81bc 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1418,7 +1418,7 @@ <title><structname>wait_event</structname> Description</title> <entry>Waiting to apply WAL at recovery because it is delayed.</entry> </row> <row> - <entry morerows="65"><literal>IO</literal></entry> + <entry morerows="66"><literal>IO</literal></entry> <entry><literal>BufFileRead</literal></entry> <entry>Waiting for a read from a buffered file.</entry> </row> @@ -1446,6 +1446,12 @@ <title><structname>wait_event</structname> Description</title> <entry><literal>ControlFileWriteUpdate</literal></entry> <entry>Waiting for a write to update the control file.</entry> </row> + <row> + <entry><literal>CopyFileCopy</literal></entry> + <entry>Waiting for a file copy operation (if the copying is done by + an operating system call rather than as separate read and write + operations).</entry> + </row> <row> <entry><literal>CopyFileRead</literal></entry> <entry>Waiting for a read during a file copy operation.</entry> diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index 6dafb404a1..3873e71dd1 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -737,6 +737,17 @@ <title>Notes</title> is down. </para> + <para> + In PostgreSQL 11 and later, <application>pg_upgrade</application> + automatically uses efficient file cloning (also known as + <quote>reflinks</quote>) on some operating systems and file systems. This + can result in near-instantaneous copying of the data files, giving the + speed advantages of <option>-k</option>/<option>--link</option> while + leaving the old cluster untouched. At present, this is supported on Linux + (kernel 4.5 or later, glibc 2.27 or later) with Btrfs and XFS (on file + systems created with reflink support, which is not the default for XFS at + this writing), and on macOS with APFS. + </para> </refsect1> <refsect1> diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 96ba216387..4feb3a5289 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -3744,6 +3744,9 @@ pgstat_get_wait_io(WaitEventIO w) case WAIT_EVENT_CONTROL_FILE_WRITE_UPDATE: event_name = "ControlFileWriteUpdate"; break; + case WAIT_EVENT_COPY_FILE_COPY: + event_name = "CopyFileCopy"; + break; case WAIT_EVENT_COPY_FILE_READ: event_name = "CopyFileRead"; break; diff --git a/src/backend/storage/file/copydir.c b/src/backend/storage/file/copydir.c index ca6342db0d..5aa9742b51 100644 --- a/src/backend/storage/file/copydir.c +++ b/src/backend/storage/file/copydir.c @@ -21,6 +21,9 @@ #include <fcntl.h> #include <unistd.h> #include <sys/stat.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #include "storage/copydir.h" #include "storage/fd.h" @@ -126,13 +129,71 @@ copydir(char *fromdir, char *todir, bool recurse) void copy_file(char *fromfile, char *tofile) { - char *buffer; +#ifdef HAVE_COPYFILE + int ret; + + pgstat_report_wait_start(WAIT_EVENT_COPY_FILE_COPY); + ret = copyfile(fromfile, tofile, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ); + pgstat_report_wait_end(); + if (ret < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", fromfile, tofile))); +#else int srcfd; int dstfd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else + char *buffer; int nbytes; off_t offset; off_t flush_offset; +#endif + + /* + * Open the files + */ + srcfd = OpenTransientFile(fromfile, O_RDONLY | PG_BINARY); + if (srcfd < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not open file \"%s\": %m", fromfile))); + + dstfd = OpenTransientFile(tofile, O_RDWR | O_CREAT | O_EXCL | PG_BINARY); + if (dstfd < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not create file \"%s\": %m", tofile))); + +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(srcfd, &stat) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not stat file \"%s\": %m", fromfile))); + + len = stat.st_size; + do { + pgstat_report_wait_start(WAIT_EVENT_COPY_FILE_COPY); + ssize_t ret = copy_file_range(srcfd, NULL, dstfd, NULL, len, 0); + pgstat_report_wait_end(); + if (ret < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", + fromfile, tofile))); + + len -= ret; + } while (len > 0); +#else /* Size of copy buffer (read and write requests) */ #define COPY_BUF_SIZE (8 * BLCKSZ) @@ -151,21 +212,6 @@ copy_file(char *fromfile, char *tofile) /* Use palloc to ensure we get a maxaligned buffer */ buffer = palloc(COPY_BUF_SIZE); - /* - * Open the files - */ - srcfd = OpenTransientFile(fromfile, O_RDONLY | PG_BINARY); - if (srcfd < 0) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not open file \"%s\": %m", fromfile))); - - dstfd = OpenTransientFile(tofile, O_RDWR | O_CREAT | O_EXCL | PG_BINARY); - if (dstfd < 0) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not create file \"%s\": %m", tofile))); - /* * Do the data copying. */ @@ -213,12 +259,14 @@ copy_file(char *fromfile, char *tofile) if (offset > flush_offset) pg_flush_data(dstfd, flush_offset, offset - flush_offset); + pfree(buffer); +#endif + if (CloseTransientFile(dstfd)) ereport(ERROR, (errcode_for_file_access(), errmsg("could not close file \"%s\": %m", tofile))); CloseTransientFile(srcfd); - - pfree(buffer); +#endif } diff --git a/src/backend/storage/file/reinit.c b/src/backend/storage/file/reinit.c index 92363ae6ad..2614b27307 100644 --- a/src/backend/storage/file/reinit.c +++ b/src/backend/storage/file/reinit.c @@ -314,8 +314,7 @@ ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, int op) FreeDir(dbspace_dir); /* - * copy_file() above has already called pg_flush_data() on the files - * it created. Now we need to fsync those files, because a checkpoint + * Now we need to fsync the copied files, because a checkpoint * won't do it for us while we're in recovery. We do this in a * separate pass to allow the kernel to perform all the flushes * (especially the metadata ones) at once. diff --git a/src/bin/pg_upgrade/file.c b/src/bin/pg_upgrade/file.c index f38bfacf02..4354b64b50 100644 --- a/src/bin/pg_upgrade/file.c +++ b/src/bin/pg_upgrade/file.c @@ -17,6 +17,9 @@ #include <sys/stat.h> #include <fcntl.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #ifdef WIN32 @@ -34,10 +37,32 @@ void copyFile(const char *src, const char *dst, const char *schemaName, const char *relName) { -#ifndef WIN32 +#if defined(HAVE_COPYFILE) + if (copyfile(src, dst, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ) < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); +#elif defined(WIN32) + if (CopyFile(src, dst, true) == 0) + { + _dosmaperr(GetLastError()); + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); + } +#else int src_fd; int dest_fd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else char *buffer; +#endif if ((src_fd = open(src, O_RDONLY | PG_BINARY, 0)) < 0) pg_fatal("error while copying relation \"%s.%s\": could not open file \"%s\": %s\n", @@ -48,6 +73,22 @@ copyFile(const char *src, const char *dst, pg_fatal("error while copying relation \"%s.%s\": could not create file \"%s\": %s\n", schemaName, relName, dst, strerror(errno)); +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(src_fd, &stat) < 0) + pg_fatal("could not stat file \"%s\": %s", + src, strerror(errno)); + + len = stat.st_size; + + do { + ssize_t ret = copy_file_range(src_fd, NULL, dest_fd, NULL, len, 0); + if (ret < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); + + len -= ret; + } while (len > 0); +#else /* copy in fairly large chunks for best efficiency */ #define COPY_BUF_SIZE (50 * BLCKSZ) @@ -77,19 +118,10 @@ copyFile(const char *src, const char *dst, } pg_free(buffer); +#endif close(src_fd); close(dest_fd); - -#else /* WIN32 */ - - if (CopyFile(src, dst, true) == 0) - { - _dosmaperr(GetLastError()); - pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", - schemaName, relName, src, dst, strerror(errno)); - } - -#endif /* WIN32 */ +#endif } diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in index f98f773ff0..38e88e0395 100644 --- a/src/include/pg_config.h.in +++ b/src/include/pg_config.h.in @@ -114,6 +114,12 @@ /* Define to 1 if your compiler handles computed gotos. */ #undef HAVE_COMPUTED_GOTO +/* Define to 1 if you have the `copyfile' function. */ +#undef HAVE_COPYFILE + +/* Define to 1 if you have the `copy_file_range' function. */ +#undef HAVE_COPY_FILE_RANGE + /* Define to 1 if you have the <crtdefs.h> header file. */ #undef HAVE_CRTDEFS_H diff --git a/src/include/pgstat.h b/src/include/pgstat.h index be2f59239b..934bce0fa9 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -863,6 +863,7 @@ typedef enum WAIT_EVENT_CONTROL_FILE_SYNC_UPDATE, WAIT_EVENT_CONTROL_FILE_WRITE, WAIT_EVENT_CONTROL_FILE_WRITE_UPDATE, + WAIT_EVENT_COPY_FILE_COPY, WAIT_EVENT_COPY_FILE_READ, WAIT_EVENT_COPY_FILE_WRITE, WAIT_EVENT_DATA_FILE_EXTEND, base-commit: 13c7c65ec900a30bcddcb27f5fd138dcdbc2ca2e -- 2.16.2 Attachments: [text/plain] v2-0001-Use-file-cloning-in-pg_upgrade-and-CREATE-DATABAS.patch (13.0K, ../../[email protected]/2-v2-0001-Use-file-cloning-in-pg_upgrade-and-CREATE-DATABAS.patch) download | inline diff: From bd8fe105f6b1c64098e344c4a7d0fc9c94d2e31d Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Tue, 20 Mar 2018 10:21:47 -0400 Subject: [PATCH v2] Use file cloning in pg_upgrade and CREATE DATABASE For file copying in pg_upgrade and CREATE DATABASE, use special file cloning calls if available. This makes the copying faster and more space efficient. For pg_upgrade, this achieves speed similar to --link mode without the associated drawbacks. Other backend users of copydir.c will also take advantage of these changes, but the performance improvement will probably not be as noticeable there. On Linux, use copy_file_range(). This supports file cloning automatically on Btrfs and XFS (if formatted with reflink support). On macOS, use copyfile(), which supports file cloning on APFS. Even on file systems without cloning/reflink support, this is faster than the existing code, because it avoids copying the file contents out of kernel space and allows the OS to apply other optimizations. --- configure | 2 +- configure.in | 2 +- doc/src/sgml/monitoring.sgml | 8 +++- doc/src/sgml/ref/pgupgrade.sgml | 11 +++++ src/backend/postmaster/pgstat.c | 3 ++ src/backend/storage/file/copydir.c | 84 ++++++++++++++++++++++++++++++-------- src/backend/storage/file/reinit.c | 3 +- src/bin/pg_upgrade/file.c | 56 +++++++++++++++++++------ src/include/pg_config.h.in | 6 +++ src/include/pgstat.h | 1 + 10 files changed, 141 insertions(+), 35 deletions(-) diff --git a/configure b/configure index 3943711283..f27c78f63a 100755 --- a/configure +++ b/configure @@ -13085,7 +13085,7 @@ fi LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -for ac_func in cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l +for ac_func in cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l do : as_ac_var=`$as_echo "ac_cv_func_$ac_func" | $as_tr_sh` ac_fn_c_check_func "$LINENO" "$ac_func" "$as_ac_var" diff --git a/configure.in b/configure.in index 1babdbb755..7eb8673753 100644 --- a/configure.in +++ b/configure.in @@ -1428,7 +1428,7 @@ PGAC_FUNC_WCSTOMBS_L LIBS_including_readline="$LIBS" LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -AC_CHECK_FUNCS([cbrt clock_gettime dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) +AC_CHECK_FUNCS([cbrt clock_gettime copy_file_range copyfile dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fallocate pstat pthread_is_threaded_np readlink setproctitle setsid shm_open symlink sync_file_range utime utimes wcstombs_l]) AC_REPLACE_FUNCS(fseeko) case $host_os in diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 3bc4de57d5..02029e81bc 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1418,7 +1418,7 @@ <title><structname>wait_event</structname> Description</title> <entry>Waiting to apply WAL at recovery because it is delayed.</entry> </row> <row> - <entry morerows="65"><literal>IO</literal></entry> + <entry morerows="66"><literal>IO</literal></entry> <entry><literal>BufFileRead</literal></entry> <entry>Waiting for a read from a buffered file.</entry> </row> @@ -1446,6 +1446,12 @@ <title><structname>wait_event</structname> Description</title> <entry><literal>ControlFileWriteUpdate</literal></entry> <entry>Waiting for a write to update the control file.</entry> </row> + <row> + <entry><literal>CopyFileCopy</literal></entry> + <entry>Waiting for a file copy operation (if the copying is done by + an operating system call rather than as separate read and write + operations).</entry> + </row> <row> <entry><literal>CopyFileRead</literal></entry> <entry>Waiting for a read during a file copy operation.</entry> diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index 6dafb404a1..3873e71dd1 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -737,6 +737,17 @@ <title>Notes</title> is down. </para> + <para> + In PostgreSQL 11 and later, <application>pg_upgrade</application> + automatically uses efficient file cloning (also known as + <quote>reflinks</quote>) on some operating systems and file systems. This + can result in near-instantaneous copying of the data files, giving the + speed advantages of <option>-k</option>/<option>--link</option> while + leaving the old cluster untouched. At present, this is supported on Linux + (kernel 4.5 or later, glibc 2.27 or later) with Btrfs and XFS (on file + systems created with reflink support, which is not the default for XFS at + this writing), and on macOS with APFS. + </para> </refsect1> <refsect1> diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 96ba216387..4feb3a5289 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -3744,6 +3744,9 @@ pgstat_get_wait_io(WaitEventIO w) case WAIT_EVENT_CONTROL_FILE_WRITE_UPDATE: event_name = "ControlFileWriteUpdate"; break; + case WAIT_EVENT_COPY_FILE_COPY: + event_name = "CopyFileCopy"; + break; case WAIT_EVENT_COPY_FILE_READ: event_name = "CopyFileRead"; break; diff --git a/src/backend/storage/file/copydir.c b/src/backend/storage/file/copydir.c index ca6342db0d..5aa9742b51 100644 --- a/src/backend/storage/file/copydir.c +++ b/src/backend/storage/file/copydir.c @@ -21,6 +21,9 @@ #include <fcntl.h> #include <unistd.h> #include <sys/stat.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #include "storage/copydir.h" #include "storage/fd.h" @@ -126,13 +129,71 @@ copydir(char *fromdir, char *todir, bool recurse) void copy_file(char *fromfile, char *tofile) { - char *buffer; +#ifdef HAVE_COPYFILE + int ret; + + pgstat_report_wait_start(WAIT_EVENT_COPY_FILE_COPY); + ret = copyfile(fromfile, tofile, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ); + pgstat_report_wait_end(); + if (ret < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", fromfile, tofile))); +#else int srcfd; int dstfd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else + char *buffer; int nbytes; off_t offset; off_t flush_offset; +#endif + + /* + * Open the files + */ + srcfd = OpenTransientFile(fromfile, O_RDONLY | PG_BINARY); + if (srcfd < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not open file \"%s\": %m", fromfile))); + + dstfd = OpenTransientFile(tofile, O_RDWR | O_CREAT | O_EXCL | PG_BINARY); + if (dstfd < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not create file \"%s\": %m", tofile))); + +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(srcfd, &stat) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not stat file \"%s\": %m", fromfile))); + + len = stat.st_size; + do { + pgstat_report_wait_start(WAIT_EVENT_COPY_FILE_COPY); + ssize_t ret = copy_file_range(srcfd, NULL, dstfd, NULL, len, 0); + pgstat_report_wait_end(); + if (ret < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not copy file \"%s\" to \"%s\": %m", + fromfile, tofile))); + + len -= ret; + } while (len > 0); +#else /* Size of copy buffer (read and write requests) */ #define COPY_BUF_SIZE (8 * BLCKSZ) @@ -151,21 +212,6 @@ copy_file(char *fromfile, char *tofile) /* Use palloc to ensure we get a maxaligned buffer */ buffer = palloc(COPY_BUF_SIZE); - /* - * Open the files - */ - srcfd = OpenTransientFile(fromfile, O_RDONLY | PG_BINARY); - if (srcfd < 0) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not open file \"%s\": %m", fromfile))); - - dstfd = OpenTransientFile(tofile, O_RDWR | O_CREAT | O_EXCL | PG_BINARY); - if (dstfd < 0) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not create file \"%s\": %m", tofile))); - /* * Do the data copying. */ @@ -213,12 +259,14 @@ copy_file(char *fromfile, char *tofile) if (offset > flush_offset) pg_flush_data(dstfd, flush_offset, offset - flush_offset); + pfree(buffer); +#endif + if (CloseTransientFile(dstfd)) ereport(ERROR, (errcode_for_file_access(), errmsg("could not close file \"%s\": %m", tofile))); CloseTransientFile(srcfd); - - pfree(buffer); +#endif } diff --git a/src/backend/storage/file/reinit.c b/src/backend/storage/file/reinit.c index 92363ae6ad..2614b27307 100644 --- a/src/backend/storage/file/reinit.c +++ b/src/backend/storage/file/reinit.c @@ -314,8 +314,7 @@ ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, int op) FreeDir(dbspace_dir); /* - * copy_file() above has already called pg_flush_data() on the files - * it created. Now we need to fsync those files, because a checkpoint + * Now we need to fsync the copied files, because a checkpoint * won't do it for us while we're in recovery. We do this in a * separate pass to allow the kernel to perform all the flushes * (especially the metadata ones) at once. diff --git a/src/bin/pg_upgrade/file.c b/src/bin/pg_upgrade/file.c index f38bfacf02..4354b64b50 100644 --- a/src/bin/pg_upgrade/file.c +++ b/src/bin/pg_upgrade/file.c @@ -17,6 +17,9 @@ #include <sys/stat.h> #include <fcntl.h> +#ifdef HAVE_COPYFILE +#include <copyfile.h> +#endif #ifdef WIN32 @@ -34,10 +37,32 @@ void copyFile(const char *src, const char *dst, const char *schemaName, const char *relName) { -#ifndef WIN32 +#if defined(HAVE_COPYFILE) + if (copyfile(src, dst, NULL, +#ifdef COPYFILE_CLONE + COPYFILE_CLONE +#else + COPYFILE_DATA +#endif + ) < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); +#elif defined(WIN32) + if (CopyFile(src, dst, true) == 0) + { + _dosmaperr(GetLastError()); + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); + } +#else int src_fd; int dest_fd; +#ifdef HAVE_COPY_FILE_RANGE + struct stat stat; + size_t len; +#else char *buffer; +#endif if ((src_fd = open(src, O_RDONLY | PG_BINARY, 0)) < 0) pg_fatal("error while copying relation \"%s.%s\": could not open file \"%s\": %s\n", @@ -48,6 +73,22 @@ copyFile(const char *src, const char *dst, pg_fatal("error while copying relation \"%s.%s\": could not create file \"%s\": %s\n", schemaName, relName, dst, strerror(errno)); +#ifdef HAVE_COPY_FILE_RANGE + if (fstat(src_fd, &stat) < 0) + pg_fatal("could not stat file \"%s\": %s", + src, strerror(errno)); + + len = stat.st_size; + + do { + ssize_t ret = copy_file_range(src_fd, NULL, dest_fd, NULL, len, 0); + if (ret < 0) + pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", + schemaName, relName, src, dst, strerror(errno)); + + len -= ret; + } while (len > 0); +#else /* copy in fairly large chunks for best efficiency */ #define COPY_BUF_SIZE (50 * BLCKSZ) @@ -77,19 +118,10 @@ copyFile(const char *src, const char *dst, } pg_free(buffer); +#endif close(src_fd); close(dest_fd); - -#else /* WIN32 */ - - if (CopyFile(src, dst, true) == 0) - { - _dosmaperr(GetLastError()); - pg_fatal("error while copying relation \"%s.%s\" (\"%s\" to \"%s\"): %s\n", - schemaName, relName, src, dst, strerror(errno)); - } - -#endif /* WIN32 */ +#endif } diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in index f98f773ff0..38e88e0395 100644 --- a/src/include/pg_config.h.in +++ b/src/include/pg_config.h.in @@ -114,6 +114,12 @@ /* Define to 1 if your compiler handles computed gotos. */ #undef HAVE_COMPUTED_GOTO +/* Define to 1 if you have the `copyfile' function. */ +#undef HAVE_COPYFILE + +/* Define to 1 if you have the `copy_file_range' function. */ +#undef HAVE_COPY_FILE_RANGE + /* Define to 1 if you have the <crtdefs.h> header file. */ #undef HAVE_CRTDEFS_H diff --git a/src/include/pgstat.h b/src/include/pgstat.h index be2f59239b..934bce0fa9 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -863,6 +863,7 @@ typedef enum WAIT_EVENT_CONTROL_FILE_SYNC_UPDATE, WAIT_EVENT_CONTROL_FILE_WRITE, WAIT_EVENT_CONTROL_FILE_WRITE_UPDATE, + WAIT_EVENT_COPY_FILE_COPY, WAIT_EVENT_COPY_FILE_READ, WAIT_EVENT_COPY_FILE_WRITE, WAIT_EVENT_DATA_FILE_EXTEND, base-commit: 13c7c65ec900a30bcddcb27f5fd138dcdbc2ca2e -- 2.16.2 ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-22 02:38 Michael Paquier <[email protected]> parent: Peter Eisentraut <[email protected]> 1 sibling, 1 reply; 21+ messages in thread From: Michael Paquier @ 2018-03-22 02:38 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On Tue, Mar 20, 2018 at 10:55:04AM -0400, Peter Eisentraut wrote: > On 3/19/18 22:58, Michael Paquier wrote: >> - Extend copy_file so as it is able to use fcopyfile. > > fcopyfile() does not support cloning. (This is not documented.) You are right. I have been reading the documentation here to get an idea as I don't have a macos system at hand: https://www.unix.com/man-page/osx/3/fcopyfile/ However I have bumped into that: http://www.openradar.me/30706426 Future versions will be visibly fixed. >> - Move the work done in pg_upgrade into a common API which can as well >> be used by pg_rewind as well. One place would be to have a >> frontend-only API in src/common which does the leg work. I would >> recommend working only on file descriptors as well for consistency with >> copy_file_range. > > pg_upgrade copies files, whereas pg_rewind needs to copy file ranges. > So I don't think this is going to be a good match. > > We could add support for using Linux copy_file_range() in pg_rewind, but > that would work a bit differently. I also don't have a good sense of > how to test the performance of that. One simple way to test that would be to limit the time it takes to scan the WAL segments on the target so as the filemap is computed quickly, and create many, say gigabyte-size relations on the promoted source which will need to be copied from the source to the target. > Another thing to think about is that we go through some trouble to > initialize new WAL files so that the disk space is fully allocated. If > we used file cloning calls in pg_rewind, that would potentially > invalidate some of that. At least, we'd have to think through this more > carefully. Agreed. Let's keep in mind such things but come with a sane, first cut of this patch based on the time remaining in this commit fest. >> - Add proper wait events for the backend calls. Those are missing for >> copy_file_range and copyfile. > > done + <entry><literal>CopyFileCopy</literal></entry> + <entry>Waiting for a file copy operation (if the copying is done by + an operating system call rather than as separate read and write + operations).</entry> CopyFileCopy is... Redundant. Perhaps CopyFileSystem or CopyFileRange? >> - For XLogFileCopy, the problem may be trickier as the tail of a segment >> is filled with zeroes, so dropping it from the first version of the >> patch sounds wiser. > > Seems like a possible follow-on project. But see also under pg_rewind > above. No objections to do that in the future for both. > Another oddity is that pg_upgrade uses CopyFile() on Windows, but the > backend does not. The Git log shows that the backend used to use > CopyFile(), but that was then removed when the generic code was added, > but when pg_upgrade was imported, it came with the CopyFile() call. You mean 558730ac, right? > I suspect the CopyFile() call can be quite a bit faster, so we should > consider adding it back in. Or if not, remove it from pg_upgrade. Hm. The proposed patch also removes an important property of what happens now in copy_file: the copied files are periodically synced to avoid spamming the cache, so for some loads wouldn't this cause a performance regression? At least on Linux it is possible to rely on sync_file_range which is called via pg_flush_data, so it seems to me that we ought to roughly keep the loop working on FLUSH_DISTANCE, and replace the calls of read/write by copy_file_range. copyfile is only able to do a complete file copy, so we would also lose this property as well on Linux. Even for Windows using CopyFile would be a step backwards for the backend. pg_upgrade is different though as it copies files fully, so using both copyfile and copy_file_range makes sense. At the end, it seems to me that using copy_file_range has some values as you save a set of read/write calls, but copyfile comes with its limitations, which I think will cause side issues, so I would recommend dropping it from a first cut of the patch for the backend. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-23 17:16 Bruce Momjian <[email protected]> parent: Peter Eisentraut <[email protected]> 1 sibling, 1 reply; 21+ messages in thread From: Bruce Momjian @ 2018-03-23 17:16 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers I think this documentation change: + leaving the old cluster untouched. At present, this is supported on Linux --------- would be better by changing "untouched" to "unmodified". Also, it would be nice if users could easily know if pg_upgrade is going to use COW or not because it might affect whether they choose --link or not. Right now it seems unclear how a user would know. Can we have pg_upgrade --check perhaps output something. Can we also have the pg_upgrade status display indicate that too, e.g. change Copying user relation files to Copying (copy-on-write) user relation files -- Bruce Momjian <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-26 01:33 Peter Eisentraut <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 21+ messages in thread From: Peter Eisentraut @ 2018-03-26 01:33 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-hackers On 3/21/18 22:38, Michael Paquier wrote: > At least on Linux it is possible to rely on sync_file_range which is > called via pg_flush_data, so it seems to me that we ought to roughly > keep the loop working on FLUSH_DISTANCE, and replace the calls of > read/write by copy_file_range. copyfile is only able to do a complete > file copy, so we would also lose this property as well on Linux. I have shown earlier in the thread that copy_file_range in one go is still better than doing it in pieces. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-26 01:34 Peter Eisentraut <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Peter Eisentraut @ 2018-03-26 01:34 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers On 3/23/18 13:16, Bruce Momjian wrote: > Also, it would be nice if users could easily know if pg_upgrade is going > to use COW or not because it might affect whether they choose --link or > not. Right now it seems unclear how a user would know. Can we have > pg_upgrade --check perhaps output something. Can we also have the > pg_upgrade status display indicate that too, e.g. change > > Copying user relation files > > to > > Copying (copy-on-write) user relation files That would be nice, but we don't have a way to tell that, AFAICT. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-26 06:15 Michael Paquier <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 21+ messages in thread From: Michael Paquier @ 2018-03-26 06:15 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On Sun, Mar 25, 2018 at 09:33:38PM -0400, Peter Eisentraut wrote: > On 3/21/18 22:38, Michael Paquier wrote: >> At least on Linux it is possible to rely on sync_file_range which is >> called via pg_flush_data, so it seems to me that we ought to roughly >> keep the loop working on FLUSH_DISTANCE, and replace the calls of >> read/write by copy_file_range. copyfile is only able to do a complete >> file copy, so we would also lose this property as well on Linux. > > I have shown earlier in the thread that copy_file_range in one go is > still better than doing it in pieces. f8c183a has introduced the optimization that your patch is removing, which was discussed on this thread: https://www.postgresql.org/message-id/flat/4B78906A.7020309%40mark.mielke.cc I am not much into the internals of copy_file_range, but isn't there a risk to have a large range of blocks copied to discard potentially useful blocks from the OS cache? That's what this patch makes me worry about. Performance is good, but on a system where the OS cache is heavily used for a set of hot blocks this could cause performance side effects that I think we canot neglect. Another thing is that 71d6d07 allowed a couple of database commands to be more sensitive to interruptions. With large databases used as a base template it seems to me that this would cause the interruptions to be less responsive. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-03-30 20:15 Peter Eisentraut <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 21+ messages in thread From: Peter Eisentraut @ 2018-03-30 20:15 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-hackers On 3/26/18 02:15, Michael Paquier wrote: > f8c183a has introduced the optimization that your patch is removing, > which was discussed on this thread: > https://www.postgresql.org/message-id/flat/4B78906A.7020309%40mark.mielke.cc Note that that thread is from 2010 and talks about creation of a database from the standard template being too slow on spinning rust, because we fsync too often. I think we have moved well past that problem size. I have run some more tests on both macOS and Linux with ext4, and my results are that the bigger the flush distance, the better. Before we made the adjustments for APFS, we had a flush size of 64kB, now it's 1MB and 32MB on macOS. In my tests, I see 256MB as the best across both platforms, and not flushing early at all is only minimally worse. You can measure this to death, and this obviously doesn't apply equally on all systems and configurations, but clearly some of the old assumptions from 8 years ago are no longer applicable. > I am not much into the internals of copy_file_range, but isn't there a > risk to have a large range of blocks copied to discard potentially > useful blocks from the OS cache? That's what this patch makes me worry > about. Performance is good, but on a system where the OS cache is > heavily used for a set of hot blocks this could cause performance side > effects that I think we canot neglect. How would we go about assessing that? It's possible, but if copy_file_range() really blows away all your in-use cache, that would be surprising. > Another thing is that 71d6d07 allowed a couple of database commands to > be more sensitive to interruptions. With large databases used as a base > template it seems to me that this would cause the interruptions to be > less responsive. The maximum file size that we copy is 1GB and that nowadays takes maybe 10 seconds. I think that would be an acceptable response time. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: file cloning in pg_upgrade and CREATE DATABASE @ 2018-04-05 20:55 Peter Eisentraut <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Peter Eisentraut @ 2018-04-05 20:55 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-hackers I think we have raised a number of interesting issues here which require more deeper consideration. So I suggest to set this patch to Returned with feedback. Btw., I just learned that copy_file_range() only works on files on the same device. So more arrangements will need to be made for that. > I have run some more tests on both macOS and Linux with ext4, and my> results are that the bigger the flush distance, the better. Before we> made the adjustments for APFS, we had a flush size of 64kB, now it's 1MB> and 32MB on macOS. In my tests, I see 256MB as the best across both> platforms, and not flushing early at all is only minimally worse. Based on this, I suggest that we set the flush distance to 32MB on all platforms. Not only is it faster, it avoids having different settings on some platforms. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 5/7] f!Rows removed by filter @ 2021-11-16 17:22 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Justin Pryzby @ 2021-11-16 17:22 UTC (permalink / raw) This cleans one more kludge in partition_prune, but drags in 2 more files... --- .../postgres_fdw/expected/postgres_fdw.out | 6 ++-- src/backend/commands/explain.c | 36 +++++++++---------- src/test/regress/expected/memoize.out | 9 ++--- src/test/regress/expected/partition_prune.out | 29 +++++---------- src/test/regress/expected/select_parallel.out | 4 +-- src/test/regress/sql/partition_prune.sql | 1 - 6 files changed, 32 insertions(+), 53 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 7d6f7d9e3df..e8e83ca5ad5 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10496,13 +10496,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Nested Loop (actual rows=1 loops=1) -> Seq Scan on local_tbl (actual rows=1 loops=1) Filter: (c = 'bar'::text) - Rows Removed by Filter: 1 -> Append (actual rows=1 loops=1) -> Async Foreign Scan on async_p1 async_pt_1 (never executed) -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1) -> Seq Scan on async_p3 async_pt_3 (never executed) Filter: (local_tbl.a = a) -(9 rows) +(8 rows) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; a | b | c | a | b | c @@ -10636,8 +10635,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (b === 505) -> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1) Filter: (b === 505) - Rows Removed by Filter: 101 -(9 rows) +(8 rows) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; a | b | c diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 8b4ff9624d4..a0212e7d241 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1765,7 +1765,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexScan *) plan)->indexorderbyorig, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1778,7 +1778,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexOnlyScan *) plan)->indexorderby, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze && es->machine) @@ -1796,7 +1796,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Index Recheck", 2, planstate, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze) @@ -1814,7 +1814,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_WorkTableScan: case T_SubqueryScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1823,7 +1823,7 @@ ExplainNode(PlanState *planstate, List *ancestors, Gather *gather = (Gather *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1851,7 +1851,7 @@ ExplainNode(PlanState *planstate, List *ancestors, GatherMerge *gm = (GatherMerge *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1889,7 +1889,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1903,7 +1903,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1919,7 +1919,7 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_orclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } @@ -1936,14 +1936,14 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_andclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } break; case T_ForeignScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_foreignscan_info((ForeignScanState *) planstate, es); @@ -1953,7 +1953,7 @@ ExplainNode(PlanState *planstate, List *ancestors, CustomScanState *css = (CustomScanState *) planstate; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (css->methods->ExplainCustomScan) @@ -1967,7 +1967,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1980,7 +1980,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1993,7 +1993,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2001,14 +2001,14 @@ ExplainNode(PlanState *planstate, List *ancestors, show_agg_keys(castNode(AggState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); show_hashagg_info((AggState *) planstate, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; case T_Group: show_group_keys(castNode(GroupState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -2030,7 +2030,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_upper_qual((List *) ((Result *) plan)->resconstantqual, "One-Time Filter", planstate, ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index fbe6bed4330..3e521f3487e 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t2.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t1.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N) Index Cond: (unique1 = t1.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -102,14 +100,13 @@ WHERE t2.unique1 < 1200;', true); -> Nested Loop (actual rows=1200 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N) Filter: (unique1 < 1200) - Rows Removed by Filter: 8800 -> Memoize (actual rows=1 loops=N) Cache Key: t2.thousand Cache Mode: logical Hits: N Misses: N Evictions: N Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.thousand) -(11 rows) +(10 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index cabadd48b81..3576e65bc29 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh Append (actual rows=0 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -(13 rows) +(9 rows) rollback; drop table list_part; @@ -1957,7 +1953,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; @@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) delete from lprt_a where a = 1; select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); @@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) Index Cond: (a = a.a) @@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) reset enable_hashjoin; reset enable_mergejoin; @@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1); Filter: ((a = $1) AND (b = $0)) -> Seq Scan on xy_1 (actual rows=0 loops=1) Filter: ((x = $1) AND (y = $0)) - Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = $0)) -(19 rows) +(18 rows) -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: value - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) Filter: (a = $0) -(9 rows) +(8 rows) explain (analyze, costs off, summary off, timing off) select * from boolp where a = (select value from boolvalues where not value); @@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: (NOT value) - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = $0) -(9 rows) +(8 rows) drop table boolp; -- @@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15); Subplans Removed: 1 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) +(7 rows) execute mt_q1(15); a @@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25); Subplans Removed: 2 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) +(5 rows) execute mt_q1(25); a diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 9f36c627419..02176d7e2f6 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off) -> Nested Loop (actual rows=98000 loops=1) -> Seq Scan on tenk2 (actual rows=10 loops=1) Filter: (thousand = 0) - Rows Removed by Filter: 9990 -> Gather (actual rows=9800 loops=10) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) Filter: (hundred > 1) - Rows Removed by Filter: 40 -(11 rows) +(9 rows) alter table tenk2 reset (parallel_workers); reset work_mem; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d70bd8610cb..e3938bea9c0 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -463,7 +463,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; -- 2.17.1 --ELVYuRnMxQ5nnKRy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0006-f-Workers-Launched.patch" ^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 05/11] f!Rows removed by filter @ 2021-11-16 17:22 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Justin Pryzby @ 2021-11-16 17:22 UTC (permalink / raw) This cleans one more kludge in partition_prune, but drags in 2 more files... --- .../postgres_fdw/expected/postgres_fdw.out | 6 ++-- src/backend/commands/explain.c | 36 +++++++++---------- src/test/regress/expected/memoize.out | 9 ++--- src/test/regress/expected/partition_prune.out | 29 +++++---------- src/test/regress/expected/select_parallel.out | 4 +-- src/test/regress/sql/partition_prune.sql | 1 - 6 files changed, 32 insertions(+), 53 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 5196e4797a..43f0b98432 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10493,13 +10493,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Nested Loop (actual rows=1 loops=1) -> Seq Scan on local_tbl (actual rows=1 loops=1) Filter: (c = 'bar'::text) - Rows Removed by Filter: 1 -> Append (actual rows=1 loops=1) -> Async Foreign Scan on async_p1 async_pt_1 (never executed) -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1) -> Seq Scan on async_p3 async_pt_3 (never executed) Filter: (local_tbl.a = a) -(9 rows) +(8 rows) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; a | b | c | a | b | c @@ -10633,8 +10632,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (b === 505) -> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1) Filter: (b === 505) - Rows Removed by Filter: 101 -(9 rows) +(8 rows) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; a | b | c diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 833daac940..9ef21d1044 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1765,7 +1765,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexScan *) plan)->indexorderbyorig, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1778,7 +1778,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexOnlyScan *) plan)->indexorderby, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze && es->machine) @@ -1796,7 +1796,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Index Recheck", 2, planstate, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze) @@ -1814,7 +1814,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_WorkTableScan: case T_SubqueryScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1823,7 +1823,7 @@ ExplainNode(PlanState *planstate, List *ancestors, Gather *gather = (Gather *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1851,7 +1851,7 @@ ExplainNode(PlanState *planstate, List *ancestors, GatherMerge *gm = (GatherMerge *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1889,7 +1889,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1903,7 +1903,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1919,7 +1919,7 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_orclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } @@ -1936,14 +1936,14 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_andclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } break; case T_ForeignScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_foreignscan_info((ForeignScanState *) planstate, es); @@ -1953,7 +1953,7 @@ ExplainNode(PlanState *planstate, List *ancestors, CustomScanState *css = (CustomScanState *) planstate; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (css->methods->ExplainCustomScan) @@ -1967,7 +1967,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1980,7 +1980,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1993,7 +1993,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2001,14 +2001,14 @@ ExplainNode(PlanState *planstate, List *ancestors, show_agg_keys(castNode(AggState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); show_hashagg_info((AggState *) planstate, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; case T_Group: show_group_keys(castNode(GroupState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -2030,7 +2030,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_upper_qual((List *) ((Result *) plan)->resconstantqual, "One-Time Filter", planstate, ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index fbe6bed433..3e521f3487 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t2.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t1.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N) Index Cond: (unique1 = t1.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -102,14 +100,13 @@ WHERE t2.unique1 < 1200;', true); -> Nested Loop (actual rows=1200 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N) Filter: (unique1 < 1200) - Rows Removed by Filter: 8800 -> Memoize (actual rows=1 loops=N) Cache Key: t2.thousand Cache Mode: logical Hits: N Misses: N Evictions: N Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.thousand) -(11 rows) +(10 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index cabadd48b8..3576e65bc2 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh Append (actual rows=0 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -(13 rows) +(9 rows) rollback; drop table list_part; @@ -1957,7 +1953,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; @@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) delete from lprt_a where a = 1; select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); @@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) Index Cond: (a = a.a) @@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) reset enable_hashjoin; reset enable_mergejoin; @@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1); Filter: ((a = $1) AND (b = $0)) -> Seq Scan on xy_1 (actual rows=0 loops=1) Filter: ((x = $1) AND (y = $0)) - Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = $0)) -(19 rows) +(18 rows) -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: value - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) Filter: (a = $0) -(9 rows) +(8 rows) explain (analyze, costs off, summary off, timing off) select * from boolp where a = (select value from boolvalues where not value); @@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: (NOT value) - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = $0) -(9 rows) +(8 rows) drop table boolp; -- @@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15); Subplans Removed: 1 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) +(7 rows) execute mt_q1(15); a @@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25); Subplans Removed: 2 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) +(5 rows) execute mt_q1(25); a diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 9f36c62741..02176d7e2f 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off) -> Nested Loop (actual rows=98000 loops=1) -> Seq Scan on tenk2 (actual rows=10 loops=1) Filter: (thousand = 0) - Rows Removed by Filter: 9990 -> Gather (actual rows=9800 loops=10) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) Filter: (hundred > 1) - Rows Removed by Filter: 40 -(11 rows) +(9 rows) alter table tenk2 reset (parallel_workers); reset work_mem; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d70bd8610c..e3938bea9c 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -463,7 +463,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; -- 2.17.0 --MOLI6A2k/lnHENyK-- ^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 5/6] f!Rows removed by filter @ 2021-11-16 17:22 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Justin Pryzby @ 2021-11-16 17:22 UTC (permalink / raw) This cleans one more kludge in partition_prune, but drags in 2 more files... --- .../postgres_fdw/expected/postgres_fdw.out | 6 ++-- src/backend/commands/explain.c | 36 +++++++++---------- src/test/regress/expected/memoize.out | 9 ++--- src/test/regress/expected/partition_prune.out | 29 +++++---------- src/test/regress/expected/select_parallel.out | 4 +-- src/test/regress/sql/partition_prune.sql | 1 - 6 files changed, 32 insertions(+), 53 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f210f911880..1d03796c543 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10496,13 +10496,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Nested Loop (actual rows=1 loops=1) -> Seq Scan on local_tbl (actual rows=1 loops=1) Filter: (c = 'bar'::text) - Rows Removed by Filter: 1 -> Append (actual rows=1 loops=1) -> Async Foreign Scan on async_p1 async_pt_1 (never executed) -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1) -> Seq Scan on async_p3 async_pt_3 (never executed) Filter: (local_tbl.a = a) -(9 rows) +(8 rows) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; a | b | c | a | b | c @@ -10636,8 +10635,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (b === 505) -> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1) Filter: (b === 505) - Rows Removed by Filter: 101 -(9 rows) +(8 rows) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; a | b | c diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index f72f9fabf8b..1e02a5f81d9 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1774,7 +1774,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexScan *) plan)->indexorderbyorig, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1787,7 +1787,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexOnlyScan *) plan)->indexorderby, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze && es->machine) @@ -1805,7 +1805,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Index Recheck", 2, planstate, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze) @@ -1823,7 +1823,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_WorkTableScan: case T_SubqueryScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1832,7 +1832,7 @@ ExplainNode(PlanState *planstate, List *ancestors, Gather *gather = (Gather *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1860,7 +1860,7 @@ ExplainNode(PlanState *planstate, List *ancestors, GatherMerge *gm = (GatherMerge *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1898,7 +1898,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1912,7 +1912,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1928,7 +1928,7 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_orclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } @@ -1945,14 +1945,14 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_andclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } break; case T_ForeignScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_foreignscan_info((ForeignScanState *) planstate, es); @@ -1962,7 +1962,7 @@ ExplainNode(PlanState *planstate, List *ancestors, CustomScanState *css = (CustomScanState *) planstate; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (css->methods->ExplainCustomScan) @@ -1976,7 +1976,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1989,7 +1989,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2002,7 +2002,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2010,14 +2010,14 @@ ExplainNode(PlanState *planstate, List *ancestors, show_agg_keys(castNode(AggState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); show_hashagg_info((AggState *) planstate, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; case T_Group: show_group_keys(castNode(GroupState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -2039,7 +2039,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_upper_qual((List *) ((Result *) plan)->resconstantqual, "One-Time Filter", planstate, ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 1b1557ce9fc..7f4b73fd42d 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t2.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t1.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N) Index Cond: (unique1 = t1.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -103,14 +101,13 @@ WHERE t2.unique1 < 1200;', true); -> Nested Loop (actual rows=1200 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N) Filter: (unique1 < 1200) - Rows Removed by Filter: 8800 -> Memoize (actual rows=1 loops=N) Cache Key: t2.thousand Cache Mode: logical Hits: N Misses: N Evictions: N Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.thousand) -(11 rows) +(10 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index cabadd48b81..3576e65bc29 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh Append (actual rows=0 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -(13 rows) +(9 rows) rollback; drop table list_part; @@ -1957,7 +1953,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; @@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) delete from lprt_a where a = 1; select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); @@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) Index Cond: (a = a.a) @@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) reset enable_hashjoin; reset enable_mergejoin; @@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1); Filter: ((a = $1) AND (b = $0)) -> Seq Scan on xy_1 (actual rows=0 loops=1) Filter: ((x = $1) AND (y = $0)) - Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = $0)) -(19 rows) +(18 rows) -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: value - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) Filter: (a = $0) -(9 rows) +(8 rows) explain (analyze, costs off, summary off, timing off) select * from boolp where a = (select value from boolvalues where not value); @@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: (NOT value) - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = $0) -(9 rows) +(8 rows) drop table boolp; -- @@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15); Subplans Removed: 1 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) +(7 rows) execute mt_q1(15); a @@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25); Subplans Removed: 2 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) +(5 rows) execute mt_q1(25); a diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 9f36c627419..02176d7e2f6 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off) -> Nested Loop (actual rows=98000 loops=1) -> Seq Scan on tenk2 (actual rows=10 loops=1) Filter: (thousand = 0) - Rows Removed by Filter: 9990 -> Gather (actual rows=9800 loops=10) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) Filter: (hundred > 1) - Rows Removed by Filter: 40 -(11 rows) +(9 rows) alter table tenk2 reset (parallel_workers); reset work_mem; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d70bd8610cb..e3938bea9c0 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -463,7 +463,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; -- 2.17.1 --AXo2lOxbfudqq8ta Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0006-f-Workers-Launched.patch" ^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 5/6] f!Rows removed by filter @ 2021-11-16 17:22 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Justin Pryzby @ 2021-11-16 17:22 UTC (permalink / raw) This cleans one more kludge in partition_prune, but drags in 2 more files... --- .../postgres_fdw/expected/postgres_fdw.out | 6 ++-- src/backend/commands/explain.c | 36 +++++++++---------- src/test/regress/expected/memoize.out | 9 ++--- src/test/regress/expected/partition_prune.out | 29 +++++---------- src/test/regress/expected/select_parallel.out | 4 +-- src/test/regress/sql/partition_prune.sql | 1 - 6 files changed, 32 insertions(+), 53 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f210f91188..1d03796c54 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10496,13 +10496,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Nested Loop (actual rows=1 loops=1) -> Seq Scan on local_tbl (actual rows=1 loops=1) Filter: (c = 'bar'::text) - Rows Removed by Filter: 1 -> Append (actual rows=1 loops=1) -> Async Foreign Scan on async_p1 async_pt_1 (never executed) -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1) -> Seq Scan on async_p3 async_pt_3 (never executed) Filter: (local_tbl.a = a) -(9 rows) +(8 rows) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; a | b | c | a | b | c @@ -10636,8 +10635,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (b === 505) -> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1) Filter: (b === 505) - Rows Removed by Filter: 101 -(9 rows) +(8 rows) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; a | b | c diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 71d6921a04..1b9976255c 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1771,7 +1771,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexScan *) plan)->indexorderbyorig, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1784,7 +1784,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexOnlyScan *) plan)->indexorderby, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze && es->machine) @@ -1802,7 +1802,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Index Recheck", 2, planstate, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze) @@ -1820,7 +1820,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_WorkTableScan: case T_SubqueryScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1829,7 +1829,7 @@ ExplainNode(PlanState *planstate, List *ancestors, Gather *gather = (Gather *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1857,7 +1857,7 @@ ExplainNode(PlanState *planstate, List *ancestors, GatherMerge *gm = (GatherMerge *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1895,7 +1895,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1909,7 +1909,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1925,7 +1925,7 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_orclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } @@ -1942,14 +1942,14 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_andclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } break; case T_ForeignScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_foreignscan_info((ForeignScanState *) planstate, es); @@ -1959,7 +1959,7 @@ ExplainNode(PlanState *planstate, List *ancestors, CustomScanState *css = (CustomScanState *) planstate; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (css->methods->ExplainCustomScan) @@ -1973,7 +1973,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1986,7 +1986,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1999,7 +1999,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2007,14 +2007,14 @@ ExplainNode(PlanState *planstate, List *ancestors, show_agg_keys(castNode(AggState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); show_hashagg_info((AggState *) planstate, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; case T_Group: show_group_keys(castNode(GroupState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -2036,7 +2036,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_upper_qual((List *) ((Result *) plan)->resconstantqual, "One-Time Filter", planstate, ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 1b1557ce9f..7f4b73fd42 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t2.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t1.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N) Index Cond: (unique1 = t1.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -103,14 +101,13 @@ WHERE t2.unique1 < 1200;', true); -> Nested Loop (actual rows=1200 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N) Filter: (unique1 < 1200) - Rows Removed by Filter: 8800 -> Memoize (actual rows=1 loops=N) Cache Key: t2.thousand Cache Mode: logical Hits: N Misses: N Evictions: N Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.thousand) -(11 rows) +(10 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index cabadd48b8..3576e65bc2 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh Append (actual rows=0 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -(13 rows) +(9 rows) rollback; drop table list_part; @@ -1957,7 +1953,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; @@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) delete from lprt_a where a = 1; select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); @@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) Index Cond: (a = a.a) @@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) reset enable_hashjoin; reset enable_mergejoin; @@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1); Filter: ((a = $1) AND (b = $0)) -> Seq Scan on xy_1 (actual rows=0 loops=1) Filter: ((x = $1) AND (y = $0)) - Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = $0)) -(19 rows) +(18 rows) -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: value - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) Filter: (a = $0) -(9 rows) +(8 rows) explain (analyze, costs off, summary off, timing off) select * from boolp where a = (select value from boolvalues where not value); @@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: (NOT value) - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = $0) -(9 rows) +(8 rows) drop table boolp; -- @@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15); Subplans Removed: 1 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) +(7 rows) execute mt_q1(15); a @@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25); Subplans Removed: 2 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) +(5 rows) execute mt_q1(25); a diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 9f36c62741..02176d7e2f 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off) -> Nested Loop (actual rows=98000 loops=1) -> Seq Scan on tenk2 (actual rows=10 loops=1) Filter: (thousand = 0) - Rows Removed by Filter: 9990 -> Gather (actual rows=9800 loops=10) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) Filter: (hundred > 1) - Rows Removed by Filter: 40 -(11 rows) +(9 rows) alter table tenk2 reset (parallel_workers); reset work_mem; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d70bd8610c..e3938bea9c 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -463,7 +463,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; -- 2.17.1 --Nj4mAaUCx+wbOcQD Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0006-f-Workers-Launched.patch" ^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 5/7] f!Rows removed by filter @ 2021-11-16 17:22 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Justin Pryzby @ 2021-11-16 17:22 UTC (permalink / raw) This cleans one more kludge in partition_prune, but drags in 2 more files... --- .../postgres_fdw/expected/postgres_fdw.out | 6 ++-- src/backend/commands/explain.c | 36 +++++++++---------- src/test/regress/expected/memoize.out | 9 ++--- src/test/regress/expected/merge.out | 3 +- src/test/regress/expected/partition_prune.out | 29 +++++---------- src/test/regress/expected/select_parallel.out | 4 +-- src/test/regress/sql/partition_prune.sql | 1 - 7 files changed, 33 insertions(+), 55 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 44457f930c2..16e1926ee40 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10632,13 +10632,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Nested Loop (actual rows=1 loops=1) -> Seq Scan on local_tbl (actual rows=1 loops=1) Filter: (c = 'bar'::text) - Rows Removed by Filter: 1 -> Append (actual rows=1 loops=1) -> Async Foreign Scan on async_p1 async_pt_1 (never executed) -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1) -> Seq Scan on async_p3 async_pt_3 (never executed) Filter: (local_tbl.a = a) -(9 rows) +(8 rows) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; a | b | c | a | b | c @@ -10920,8 +10919,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (b === 505) -> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1) Filter: (b === 505) - Rows Removed by Filter: 101 -(9 rows) +(8 rows) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; a | b | c diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 7c5e0ad2c1e..eeded88b1c6 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1796,7 +1796,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexScan *) plan)->indexorderbyorig, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1809,7 +1809,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexOnlyScan *) plan)->indexorderby, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze && es->machine) @@ -1827,7 +1827,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Index Recheck", 2, planstate, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze) @@ -1845,7 +1845,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_WorkTableScan: case T_SubqueryScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1854,7 +1854,7 @@ ExplainNode(PlanState *planstate, List *ancestors, Gather *gather = (Gather *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1882,7 +1882,7 @@ ExplainNode(PlanState *planstate, List *ancestors, GatherMerge *gm = (GatherMerge *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1920,7 +1920,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1934,7 +1934,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1950,7 +1950,7 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_orclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } @@ -1967,14 +1967,14 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_andclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } break; case T_ForeignScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_foreignscan_info((ForeignScanState *) planstate, es); @@ -1984,7 +1984,7 @@ ExplainNode(PlanState *planstate, List *ancestors, CustomScanState *css = (CustomScanState *) planstate; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (css->methods->ExplainCustomScan) @@ -1998,7 +1998,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2011,7 +2011,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2024,7 +2024,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2032,7 +2032,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_agg_keys(castNode(AggState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); show_hashagg_info((AggState *) planstate, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -2047,7 +2047,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_Group: show_group_keys(castNode(GroupState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -2069,7 +2069,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_upper_qual((List *) ((Result *) plan)->resconstantqual, "One-Time Filter", planstate, ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 1b1557ce9fc..7f4b73fd42d 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t2.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t1.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N) Index Cond: (unique1 = t1.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -103,14 +101,13 @@ WHERE t2.unique1 < 1200;', true); -> Nested Loop (actual rows=1200 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N) Filter: (unique1 < 1200) - Rows Removed by Filter: 8800 -> Memoize (actual rows=1 loops=N) Cache Key: t2.thousand Cache Mode: logical Hits: N Misses: N Evictions: N Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.thousand) -(11 rows) +(10 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index cebf011969d..486b5beaebf 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1447,8 +1447,7 @@ WHEN MATCHED AND t.a < 10 THEN Buckets: xxx Batches: xxx -> Seq Scan on ex_mtarget t (actual rows=0 loops=1) Filter: (a < '-1000'::integer) - Rows Removed by Filter: 54 -(9 rows) +(8 rows) DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index cabadd48b81..3576e65bc29 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh Append (actual rows=0 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -(13 rows) +(9 rows) rollback; drop table list_part; @@ -1957,7 +1953,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; @@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) delete from lprt_a where a = 1; select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); @@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) Index Cond: (a = a.a) @@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) reset enable_hashjoin; reset enable_mergejoin; @@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1); Filter: ((a = $1) AND (b = $0)) -> Seq Scan on xy_1 (actual rows=0 loops=1) Filter: ((x = $1) AND (y = $0)) - Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = $0)) -(19 rows) +(18 rows) -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: value - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) Filter: (a = $0) -(9 rows) +(8 rows) explain (analyze, costs off, summary off, timing off) select * from boolp where a = (select value from boolvalues where not value); @@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: (NOT value) - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = $0) -(9 rows) +(8 rows) drop table boolp; -- @@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15); Subplans Removed: 1 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) +(7 rows) execute mt_q1(15); a @@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25); Subplans Removed: 2 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) +(5 rows) execute mt_q1(25); a diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 9f36c627419..02176d7e2f6 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off) -> Nested Loop (actual rows=98000 loops=1) -> Seq Scan on tenk2 (actual rows=10 loops=1) Filter: (thousand = 0) - Rows Removed by Filter: 9990 -> Gather (actual rows=9800 loops=10) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) Filter: (hundred > 1) - Rows Removed by Filter: 40 -(11 rows) +(9 rows) alter table tenk2 reset (parallel_workers); reset work_mem; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d70bd8610cb..e3938bea9c0 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -463,7 +463,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; -- 2.17.1 --LQ77YLfPrO/qF/pM Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0006-f-Workers-Launched.patch" ^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: pg_combinebackup fails on file named INCREMENTAL.* @ 2024-04-16 07:09 Stefan Fercot <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Stefan Fercot @ 2024-04-16 07:09 UTC (permalink / raw) To: David Steele <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers; Tomas Vondra <[email protected]> Hi, > I think it would be reasonable to restrict what can be put in base/ and > global/ but users generally feel free to create whatever they want in > the root of PGDATA, despite being strongly encouraged not to. > > Anyway, I think it should be fixed or documented as a caveat since it > causes a hard failure on restore. +1. IMHO, no matter how you'd further decide to reference the incremental stubs, the earlier we can mention the failure to the user, the better. Tbh, I'm not very confortable seeing pg_combinebackup fail when the user would need to restore (whatever the reason). I mean, failing to take the backup is less of a problem (compared to failing to restore) because then the user might have the time to investigate and fix the issue, not being in the hurry of a down production to restore... > > But ... I didn't really end up feeling very comfortable with it. Right > > now, the backup manifest is something we only use to verify the > > integrity of the backup. If we were to do this, it would become a > > critical part of the backup. Isn't it already the case? I mean, you need the manifest of the previous backup to take an incremental one, right? And shouldn't we encourage to verify the backup sets before (at least) trying to combine them? It's not because a file was only use for one specific purpose until now that we can't improve it later. Splitting the meaningful information across multiple places would be more error-prone (for both devs and users) imo. > > I don't think I like the idea that > > removing the backup_manifest should be allowed to, in effect, corrupt > > the backup. But I think I dislike even more the idea that the data > > that is used to verify the backup gets mushed together with the backup > > data itself. Maybe in practice it's fine, but it doesn't seem very > > conceptually clean. Removing pretty much any file in the backup set would probably corrupt it. I mean, why would people remove the backup manifest when they already can't remove backup_label? A doc stating "dont touch anything" is IMHO easier than "this part is critical, not that one". > I don't think this is a problem. The manifest can do more than store > verification info, IMO. +1. Adding more info to the backup manifest can be handy for other use-cases too (i.e. like avoiding to store empty files, or storing the checksums state of the cluster). Kind Regards, -- Stefan FERCOT Data Egret (https://dataegret.com) ^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2024-04-16 07:09 UTC | newest] Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-02-21 03:00 file cloning in pg_upgrade and CREATE DATABASE Peter Eisentraut <[email protected]> 2018-02-21 16:33 ` Robert Haas <[email protected]> 2018-02-21 23:57 ` Tomas Vondra <[email protected]> 2018-02-22 22:49 ` Peter Eisentraut <[email protected]> 2018-03-19 07:06 ` Michael Paquier <[email protected]> 2018-03-19 07:14 ` Michael Paquier <[email protected]> 2018-03-20 02:58 ` Michael Paquier <[email protected]> 2018-03-20 14:55 ` Peter Eisentraut <[email protected]> 2018-03-22 02:38 ` Michael Paquier <[email protected]> 2018-03-26 01:33 ` Peter Eisentraut <[email protected]> 2018-03-26 06:15 ` Michael Paquier <[email protected]> 2018-03-30 20:15 ` Peter Eisentraut <[email protected]> 2018-04-05 20:55 ` Peter Eisentraut <[email protected]> 2018-03-23 17:16 ` Bruce Momjian <[email protected]> 2018-03-26 01:34 ` Peter Eisentraut <[email protected]> 2021-11-16 17:22 [PATCH 5/7] f!Rows removed by filter Justin Pryzby <[email protected]> 2021-11-16 17:22 [PATCH 5/6] f!Rows removed by filter Justin Pryzby <[email protected]> 2021-11-16 17:22 [PATCH 5/7] f!Rows removed by filter Justin Pryzby <[email protected]> 2021-11-16 17:22 [PATCH 05/11] f!Rows removed by filter Justin Pryzby <[email protected]> 2021-11-16 17:22 [PATCH 5/6] f!Rows removed by filter Justin Pryzby <[email protected]> 2024-04-16 07:09 Re: pg_combinebackup fails on file named INCREMENTAL.* Stefan Fercot <[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