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]>
2024-08-25 15:35 ` Re: Non-trivial condition is only propagated to one side of JOIN David G. Johnston <[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:10 Non-trivial condition is only propagated to one side of JOIN Tobias Hoffmann <[email protected]>
@ 2024-08-25 15:35 ` 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]>
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 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 ` 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]>
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 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 ` Tom Lane <[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