public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 07/10] add wal_compression_method: zstd 5+ messages / 4 participants [nested] [flat]
* [PATCH 07/10] add wal_compression_method: zstd @ 2021-03-12 20:43 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Justin Pryzby @ 2021-03-12 20:43 UTC (permalink / raw) --- configure | 163 ++++++++++++++++++ configure.ac | 26 +++ doc/src/sgml/config.sgml | 2 +- src/backend/access/transam/xlog.c | 3 + src/backend/access/transam/xloginsert.c | 19 ++ src/backend/access/transam/xlogreader.c | 16 ++ src/backend/utils/misc/postgresql.conf.sample | 2 +- src/include/access/xlog_internal.h | 1 + src/include/pg_config.h.in | 3 + src/tools/msvc/Solution.pm | 1 + 10 files changed, 234 insertions(+), 2 deletions(-) diff --git a/configure b/configure index 3afa1e87e3..20c6e08c02 100755 --- a/configure +++ b/configure @@ -699,6 +699,9 @@ with_gnu_ld LD LDFLAGS_SL LDFLAGS_EX +ZSTD_LIBS +ZSTD_CFLAGS +with_zstd LZ4_LIBS LZ4_CFLAGS with_lz4 @@ -868,6 +871,7 @@ with_libxslt with_system_tzdata with_zlib with_lz4 +with_zstd with_gnu_ld with_ssl with_openssl @@ -897,6 +901,8 @@ XML2_CFLAGS XML2_LIBS LZ4_CFLAGS LZ4_LIBS +ZSTD_CFLAGS +ZSTD_LIBS LDFLAGS_EX LDFLAGS_SL PERL @@ -1576,6 +1582,7 @@ Optional Packages: use system time zone data in DIR --without-zlib do not use Zlib --without-lz4 build without LZ4 support + --with-zstd build with Zstd compression library --with-gnu-ld assume the C compiler uses GNU ld [default=no] --with-ssl=LIB use LIB for SSL/TLS support (openssl) --with-openssl obsolete spelling of --with-ssl=openssl @@ -1605,6 +1612,8 @@ Some influential environment variables: XML2_LIBS linker flags for XML2, overriding pkg-config LZ4_CFLAGS C compiler flags for LZ4, overriding pkg-config LZ4_LIBS linker flags for LZ4, overriding pkg-config + ZSTD_CFLAGS C compiler flags for ZSTD, overriding pkg-config + ZSTD_LIBS linker flags for ZSTD, overriding pkg-config LDFLAGS_EX extra linker flags for linking executables only LDFLAGS_SL extra linker flags for linking shared libraries only PERL Perl program @@ -8705,6 +8714,137 @@ fi CFLAGS="$LZ4_CFLAGS $CFLAGS" fi +# +# ZSTD +# +{ $as_echo "$as_me:${as_lineno-$LINENO}: checking whether to build with zstd support" >&5 +$as_echo_n "checking whether to build with zstd support... " >&6; } + + + +# Check whether --with-zstd was given. +if test "${with_zstd+set}" = set; then : + withval=$with_zstd; + case $withval in + yes) + +$as_echo "#define USE_ZSTD 1" >>confdefs.h + + ;; + no) + : + ;; + *) + as_fn_error $? "no argument expected for --with-zstd option" "$LINENO" 5 + ;; + esac + +else + with_zstd=no + +fi + + +{ $as_echo "$as_me:${as_lineno-$LINENO}: result: $with_zstd" >&5 +$as_echo "$with_zstd" >&6; } + + +if test "$with_zstd" = yes; then + +pkg_failed=no +{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for libzstd" >&5 +$as_echo_n "checking for libzstd... " >&6; } + +if test -n "$ZSTD_CFLAGS"; then + pkg_cv_ZSTD_CFLAGS="$ZSTD_CFLAGS" + elif test -n "$PKG_CONFIG"; then + if test -n "$PKG_CONFIG" && \ + { { $as_echo "$as_me:${as_lineno-$LINENO}: \$PKG_CONFIG --exists --print-errors \"libzstd\""; } >&5 + ($PKG_CONFIG --exists --print-errors "libzstd") 2>&5 + ac_status=$? + $as_echo "$as_me:${as_lineno-$LINENO}: \$? = $ac_status" >&5 + test $ac_status = 0; }; then + pkg_cv_ZSTD_CFLAGS=`$PKG_CONFIG --cflags "libzstd" 2>/dev/null` + test "x$?" != "x0" && pkg_failed=yes +else + pkg_failed=yes +fi + else + pkg_failed=untried +fi +if test -n "$ZSTD_LIBS"; then + pkg_cv_ZSTD_LIBS="$ZSTD_LIBS" + elif test -n "$PKG_CONFIG"; then + if test -n "$PKG_CONFIG" && \ + { { $as_echo "$as_me:${as_lineno-$LINENO}: \$PKG_CONFIG --exists --print-errors \"libzstd\""; } >&5 + ($PKG_CONFIG --exists --print-errors "libzstd") 2>&5 + ac_status=$? + $as_echo "$as_me:${as_lineno-$LINENO}: \$? = $ac_status" >&5 + test $ac_status = 0; }; then + pkg_cv_ZSTD_LIBS=`$PKG_CONFIG --libs "libzstd" 2>/dev/null` + test "x$?" != "x0" && pkg_failed=yes +else + pkg_failed=yes +fi + else + pkg_failed=untried +fi + + + +if test $pkg_failed = yes; then + { $as_echo "$as_me:${as_lineno-$LINENO}: result: no" >&5 +$as_echo "no" >&6; } + +if $PKG_CONFIG --atleast-pkgconfig-version 0.20; then + _pkg_short_errors_supported=yes +else + _pkg_short_errors_supported=no +fi + if test $_pkg_short_errors_supported = yes; then + ZSTD_PKG_ERRORS=`$PKG_CONFIG --short-errors --print-errors --cflags --libs "libzstd" 2>&1` + else + ZSTD_PKG_ERRORS=`$PKG_CONFIG --print-errors --cflags --libs "libzstd" 2>&1` + fi + # Put the nasty error message in config.log where it belongs + echo "$ZSTD_PKG_ERRORS" >&5 + + as_fn_error $? "Package requirements (libzstd) were not met: + +$ZSTD_PKG_ERRORS + +Consider adjusting the PKG_CONFIG_PATH environment variable if you +installed software in a non-standard prefix. + +Alternatively, you may set the environment variables ZSTD_CFLAGS +and ZSTD_LIBS to avoid the need to call pkg-config. +See the pkg-config man page for more details." "$LINENO" 5 +elif test $pkg_failed = untried; then + { $as_echo "$as_me:${as_lineno-$LINENO}: result: no" >&5 +$as_echo "no" >&6; } + { { $as_echo "$as_me:${as_lineno-$LINENO}: error: in \`$ac_pwd':" >&5 +$as_echo "$as_me: error: in \`$ac_pwd':" >&2;} +as_fn_error $? "The pkg-config script could not be found or is too old. Make sure it +is in your PATH or set the PKG_CONFIG environment variable to the full +path to pkg-config. + +Alternatively, you may set the environment variables ZSTD_CFLAGS +and ZSTD_LIBS to avoid the need to call pkg-config. +See the pkg-config man page for more details. + +To get pkg-config, see <http://pkg-config.freedesktop.org/;. +See \`config.log' for more details" "$LINENO" 5; } +else + ZSTD_CFLAGS=$pkg_cv_ZSTD_CFLAGS + ZSTD_LIBS=$pkg_cv_ZSTD_LIBS + { $as_echo "$as_me:${as_lineno-$LINENO}: result: yes" >&5 +$as_echo "yes" >&6; } + +fi + LIBS="$ZSTD_LIBS $LIBS" + CFLAGS="$ZSTD_CFLAGS $CFLAGS" +fi + # # Assignments # @@ -13556,6 +13696,29 @@ done CPPFLAGS=$ac_save_CPPFLAGS fi +if test "$with_zstd" = yes; then + ac_save_CPPFLAGS=$CPPFLAGS + CPPFLAGS="$ZSTD_CFLAGS $CPPFLAGS" + + # Verify we have zstd's header files + for ac_header in zstd.h +do : + ac_fn_c_check_header_mongrel "$LINENO" "zstd.h" "ac_cv_header_zstd_h" "$ac_includes_default" +if test "x$ac_cv_header_zstd_h" = xyes; then : + cat >>confdefs.h <<_ACEOF +#define HAVE_ZSTD_H 1 +_ACEOF + +else + as_fn_error $? "zstd.h header file is required for zstd" "$LINENO" 5 +fi + +done + + + CPPFLAGS=$ac_save_CPPFLAGS +fi + if test "$with_gssapi" = yes ; then for ac_header in gssapi/gssapi.h do : diff --git a/configure.ac b/configure.ac index d62ea5f742..c4956745ce 100644 --- a/configure.ac +++ b/configure.ac @@ -1001,6 +1001,21 @@ if test "$with_lz4" = yes; then CFLAGS="$LZ4_CFLAGS $CFLAGS" fi +# +# ZSTD +# +AC_MSG_CHECKING([whether to build with zstd support]) +PGAC_ARG_BOOL(with, zstd, no, [build with Zstd compression library], + [AC_DEFINE([USE_ZSTD], 1, [Define to 1 to build with zstd support. (--with-zstd)])]) +AC_MSG_RESULT([$with_zstd]) +AC_SUBST(with_zstd) + +if test "$with_zstd" = yes; then + PKG_CHECK_MODULES(ZSTD, libzstd) + LIBS="$ZSTD_LIBS $LIBS" + CFLAGS="$ZSTD_CFLAGS $CFLAGS" +fi + # # Assignments # @@ -1433,6 +1448,17 @@ if test "$with_lz4" = yes; then CPPFLAGS=$ac_save_CPPFLAGS fi +if test "$with_zstd" = yes; then + ac_save_CPPFLAGS=$CPPFLAGS + CPPFLAGS="$ZSTD_CFLAGS $CPPFLAGS" + + # Verify we have zstd's header files + AC_CHECK_HEADERS(zstd.h, [], + [AC_MSG_ERROR([zstd.h header file is required for zstd])]) + + CPPFLAGS=$ac_save_CPPFLAGS +fi + if test "$with_gssapi" = yes ; then AC_CHECK_HEADERS(gssapi/gssapi.h, [], [AC_CHECK_HEADERS(gssapi.h, [], [AC_MSG_ERROR([gssapi.h header file is required for GSSAPI])])]) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 257775c83b..94dd6ef3e9 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3083,7 +3083,7 @@ include_dir 'conf.d' This parameter selects the compression method used to compress WAL when <varname>wal_compression</varname> is enabled. The supported methods are pglz, zlib, and (if configured when - <productname>PostgreSQL</productname> was built) lz4. + <productname>PostgreSQL</productname> was built) lz4 and zstd. The default value is <literal>pglz</literal>. Only superusers can change this setting. </para> diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 609d954a3a..9bac79b579 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -189,6 +189,9 @@ const struct config_enum_entry wal_compression_options[] = { #endif #ifdef USE_LZ4 {"lz4", WAL_COMPRESSION_LZ4, false}, +#endif +#ifdef USE_ZSTD + {"zstd", WAL_COMPRESSION_ZSTD, false}, #endif {NULL, 0, false} }; diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c index 9fe5c30236..5d1ae37dae 100644 --- a/src/backend/access/transam/xloginsert.c +++ b/src/backend/access/transam/xloginsert.c @@ -41,6 +41,10 @@ #include "lz4.h" #endif +#ifdef USE_ZSTD +#include "zstd.h" +#endif + /* Buffer size required to store a compressed version of backup block image */ #define PGLZ_MAX_BLCKSZ PGLZ_MAX_OUTPUT(BLCKSZ) @@ -896,6 +900,21 @@ XLogCompressBackupBlock(char *page, uint16 hole_offset, uint16 hole_length, break; #endif +#ifdef USE_ZSTD + case WAL_COMPRESSION_ZSTD: + len = ZSTD_compress(dest, PGLZ_MAX_BLCKSZ, source, orig_len, 1); + if (ZSTD_isError(len)) + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("failed compressing zstd: %s", + ZSTD_getErrorName(len)))); + len = -1; + } + + break; +#endif + default: /* * It should be impossible to get here for unsupported algorithms, diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c index fa1e38a810..caa1031d63 100644 --- a/src/backend/access/transam/xlogreader.c +++ b/src/backend/access/transam/xlogreader.c @@ -41,6 +41,10 @@ #include "lz4.h" #endif +#ifdef USE_ZSTD +#include "zstd.h" +#endif + static void report_invalid_record(XLogReaderState *state, const char *fmt,...) pg_attribute_printf(2, 3); static bool allocate_recordbuf(XLogReaderState *state, uint32 reclength); @@ -1564,6 +1568,8 @@ wal_compression_name(WalCompression compression) return "zlib"; case WAL_COMPRESSION_LZ4: return "lz4"; + case WAL_COMPRESSION_ZSTD: + return "zstd"; default: return "???"; } @@ -1621,6 +1627,16 @@ RestoreBlockImage(XLogReaderState *record, uint8 block_id, char *page) break; #endif +#ifdef USE_ZSTD + case WAL_COMPRESSION_ZSTD: + decomp_result = ZSTD_decompress(tmp.data, BLCKSZ-bkpb->hole_length, + ptr, bkpb->bimg_len); + // XXX: ZSTD_getErrorName + if (ZSTD_isError(decomp_result)) + decomp_result = -1; + break; +#endif + default: report_invalid_record(record, "image at %X/%X is compressed with unsupported codec, block %d (%d/%s)", (uint32) (record->ReadRecPtr >> 32), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 76f494cb9b..d372e2a817 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -213,7 +213,7 @@ # open_sync #full_page_writes = on # recover from partial page writes #wal_compression = off # enable compression of full-page writes -#wal_compression_method = pglz # pglz, zlib, lz4 +#wal_compression_method = pglz # pglz, zlib, lz4, zstd #wal_log_hints = off # also do full page writes of non-critical updates # (change requires restart) #wal_init_zero = on # zero-fill new WAL files diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h index e70886b81c..48b16b6083 100644 --- a/src/include/access/xlog_internal.h +++ b/src/include/access/xlog_internal.h @@ -329,6 +329,7 @@ typedef enum WalCompression WAL_COMPRESSION_PGLZ, WAL_COMPRESSION_ZLIB, WAL_COMPRESSION_LZ4, + WAL_COMPRESSION_ZSTD, } WalCompression; extern const char *wal_compression_name(WalCompression compression); diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in index 0a6422da4f..ad26393352 100644 --- a/src/include/pg_config.h.in +++ b/src/include/pg_config.h.in @@ -902,6 +902,9 @@ /* Define to 1 to build with LZ4 support (--with-lz4) */ #undef USE_LZ4 +/* Define to 1 if you have the `zstd' library (-lzstd). */ +#undef USE_ZSTD + /* Define to select named POSIX semaphores. */ #undef USE_NAMED_POSIX_SEMAPHORES diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm index 14605371bb..fff0212087 100644 --- a/src/tools/msvc/Solution.pm +++ b/src/tools/msvc/Solution.pm @@ -486,6 +486,7 @@ sub GenerateFiles USE_LIBXML => undef, USE_LIBXSLT => undef, USE_LZ4 => undef, + USE_ZSTD => $self->{options}->{zstd} ? 1 : undef, USE_LDAP => $self->{options}->{ldap} ? 1 : undef, USE_LLVM => undef, USE_NAMED_POSIX_SEMAPHORES => undef, -- 2.17.0 --0qVF/w3MHQqLSynd Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0008-Default-to-zstd.patch" ^ permalink raw reply [nested|flat] 5+ messages in thread
* Non-trivial condition is only propagated to one side of JOIN @ 2024-08-25 15:10 Tobias Hoffmann <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tobias Hoffmann @ 2024-08-25 15:10 UTC (permalink / raw) To: pgsql-hackers Hi, using `PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit`, I've observed the following behavior: – keep in mind that this example is as simplified as possible, the original query involves foreign tables, and the failure to propagate / push down the condition results in a query plan that basically tries to download the complete foreign table, which is not a feasible execution strategy: Setup: CREATE TABLE tbl1 (id INTEGER GENERATED ALWAYS AS IDENTITY, site_id INTEGER NOT NULL, data TEXT); CREATE TABLE tbl2 (id INTEGER GENERATED ALWAYS AS IDENTITY, site_id INTEGER NOT NULL, data TEXT); CREATE INDEX ON tbl1 (site_id); CREATE INDEX ON tbl2 (site_id); Working queries: SELECT * FROM tbl1 WHERE tbl1.site_id = 1; -- "trivial condition" SELECT * FROM tbl2 WHERE tbl2.site_id = 1; SELECT * FROM tbl1 WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; -- "non-trivial condition" SELECT * FROM tbl2 WHERE tbl2.site_id = 1 OR tbl2.site_id IS NULL; 1) Exemplary Query Plan: # EXPLAIN SELECT * FROM tbl2 WHERE tbl2.site_id = 1 OR tbl2.site_id IS NULL; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on tbl2 (cost=8.40..19.08 rows=12 width=40) Recheck Cond: ((site_id = 1) OR (site_id IS NULL)) -> BitmapOr (cost=8.40..8.40 rows=12 width=0) -> Bitmap Index Scan on tbl2_site_id_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (site_id = 1) -> Bitmap Index Scan on tbl2_site_id_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (site_id IS NULL) (7 rows) The key takeaway is, that the index can be used, because the condition is propagated deep enough. 2) Still working example: # EXPLAIN SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl2.site_id = tbl1.site_id WHERE tbl1.site_id = 1; QUERY PLAN ------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=8.40..27.80 rows=36 width=80) -> Bitmap Heap Scan on tbl1 (cost=4.20..13.67 rows=6 width=40) Recheck Cond: (site_id = 1) -> Bitmap Index Scan on tbl1_site_id_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (site_id = 1) -> Materialize (cost=4.20..13.70 rows=6 width=40) -> Bitmap Heap Scan on tbl2 (cost=4.20..13.67 rows=6 width=40) Recheck Cond: (site_id = 1) -> Bitmap Index Scan on tbl2_site_id_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (site_id = 1) (10 rows) The condition is propagated into BOTH branches of the join. The join could also be an INNER join and might also be realized as a Merge Join or Hash Join: they all behave the same. 3) Problematic example: # EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id = tbl1.site_id WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; QUERY PLAN ------------------------------------------------------------------------------------------------- Hash Join (cost=19.23..46.45 rows=72 width=80) Hash Cond: (tbl2.site_id = tbl1.site_id) -> Seq Scan on tbl2 (cost=0.00..22.00 rows=1200 width=40) -> Hash (cost=19.08..19.08 rows=12 width=40) -> Bitmap Heap Scan on tbl1 (cost=8.40..19.08 rows=12 width=40) Recheck Cond: ((site_id = 1) OR (site_id IS NULL)) -> BitmapOr (cost=8.40..8.40 rows=12 width=0) -> Bitmap Index Scan on tbl1_site_id_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (site_id = 1) -> Bitmap Index Scan on tbl1_site_id_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (site_id IS NULL) (11 rows) Now, a full seq scan used for tbl2, the condition is only pushed down on ONE side of the JOIN! (with `WHERE tbl2.site_id = 1 OR tbl2.site_id IS NULL`, the Seq Scan would have been on tbl1... [not so easily demostrated w/ LEFT JOINs]). Also, `ON tbl1.site_id IS NOT DISTINCT FROM tbl2.site_id` does not help, The weird thing is: The subqueries on both sides of the join are perfectly capable of accepting/using the "non-trivial" condition, as demonstrated in 1), and JOINs are generally able to propagate conditions to both sides, as demonstrated in 2). Is there a magic knob to force postgres to do the right thing, or is this basically a bug in the query planner? Tobias ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Non-trivial condition is only propagated to one side of JOIN @ 2024-08-25 15:35 David G. Johnston <[email protected]> parent: Tobias Hoffmann <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: David G. Johnston @ 2024-08-25 15:35 UTC (permalink / raw) To: Tobias Hoffmann <[email protected]>; +Cc: pgsql-hackers On Sunday, August 25, 2024, Tobias Hoffmann <[email protected]> wrote: > > 3) Problematic example: > > # EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id = tbl1.site_id > WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; The “is null” predicate in this query is doing nothing as your next comment alludes to; you will produce no rows out of the join with a null site_id due to the use of the equals operator in the join. > > Also, `ON tbl1.site_id IS NOT DISTINCT FROM tbl2.site_id` does not help, > Others may correct me but I’m guessing that indeed the optimizer has a gap here that could be filled in, it’s just it feels like adding code to deal with broken queries so isn’t overly motivated to work on. Joining using distinct instead of equality is uncommon, since nearly all models join primary keys to foreign keys and both of those are almost always non-null. David J. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Non-trivial condition is only propagated to one side of JOIN @ 2024-08-25 17:04 Tobias Hoffmann <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tobias Hoffmann @ 2024-08-25 17:04 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-hackers On 25/08/2024 17:35, David G. Johnston wrote: > On Sunday, August 25, 2024, Tobias Hoffmann > <[email protected]> wrote: > > > 3) Problematic example: > > # EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id = > tbl1.site_id WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; > > > The “is null” predicate in this query is doing nothing as your next > comment alludes to; you will produce no rows out of the join with a > null site_id due to the use of the equals operator in the join. Well, that's why I said: "keep in mind that this example is as simplified as possible"... Even though `tbl1.site_id = 1` is – in this case – completely equivalent to `tbl1.site_id = 1 OR tbl1.site_id IS NULL` – the first one is completely pushed down, but the second is not. A more complete example might look more like this: CREATE VIEW "subview1" AS SELECT tbl1.site_id, ... JOIN ... ON tbl1.site_id = tbl2.site_id WHERE ...; CREATE VIEW "view1" AS SELECT site_id, ... FROM subview1 -- maybe even: WHERE site_id IS NOT NULL UNION ALL SELECT null, ...; SELECT * FROM view1 WHERE (site_id = 1 OR site_id IS NULL); The reason, why the outer query would have a more complicated condition might have nothing to do with the subquery containing the JOIN. (This is also not a `UNION ALL` special case: `site_id IS NULL` could also be generated by a LEFT JOIN, e.g.) But not pushing down the condition has the grave impact, that those - otherwise working VIEWs (i.e. subview1) become "unfixably" broken, for certain WHERE-conditions on the outside. Another reason why I said `site_id INTEGER NOT NULL` and `IS NOT DISTINCT FROM`, is that there might be some mathematical reason I'm not yet aware of where the propagation would not be sound, but which would not apply for arbitrary site_id [nullable, ...]. My primary goal is to find at least *some* way to get the condition pushed further in to avoid the full table scan, and to not have to completely rewrite all the VIEWs into a single big query, where I could inject the site_id parameter (e.g. "$1") in multiple places as needed... Tobias ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Non-trivial condition is only propagated to one side of JOIN @ 2024-08-25 17:28 Tom Lane <[email protected]> parent: Tobias Hoffmann <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2024-08-25 17:28 UTC (permalink / raw) To: Tobias Hoffmann <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-hackers Tobias Hoffmann <[email protected]> writes: > A more complete example might look more like this: > CREATE VIEW "subview1" AS > SELECT tbl1.site_id, ... JOIN ... ON tbl1.site_id = tbl2.site_id > WHERE ...; > CREATE VIEW "view1" AS > SELECT site_id, ... FROM subview1 -- maybe even: WHERE site_id IS > NOT NULL > UNION ALL > SELECT null, ...; > SELECT * FROM view1 WHERE (site_id = 1 OR site_id IS NULL); For this particular case, you could probably get somewhere by writing SELECT * FROM view1 WHERE site_id = 1 UNION ALL SELECT * FROM view1 WHERE site_id IS NULL; since the sets of rows satisfying those two WHERE conditions must be disjoint. (I recall working on a patch that essentially tried to do that transformation automatically, but it eventually failed because things get too messy if the row sets might not be disjoint.) regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-08-25 17:28 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-03-12 20:43 [PATCH 07/10] add wal_compression_method: zstd Justin Pryzby <[email protected]> 2024-08-25 15:10 Non-trivial condition is only propagated to one side of JOIN Tobias Hoffmann <[email protected]> 2024-08-25 15:35 ` Re: Non-trivial condition is only propagated to one side of JOIN David G. Johnston <[email protected]> 2024-08-25 17:04 ` Re: Non-trivial condition is only propagated to one side of JOIN Tobias Hoffmann <[email protected]> 2024-08-25 17:28 ` Re: Non-trivial condition is only propagated to one side of JOIN Tom Lane <[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