public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 07/10] add wal_compression_method: zstd
3+ messages / 2 participants
[nested] [flat]
* [PATCH 07/10] add wal_compression_method: zstd
@ 2021-03-12 20:43 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 3+ 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] 3+ messages in thread
* Add rows removed by hash join clause to instrumentation
@ 2026-01-16 23:51 Andrei Lepikhov <[email protected]>
2026-01-17 20:47 ` Re: Add rows removed by hash join clause to instrumentation Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Andrei Lepikhov @ 2026-01-16 23:51 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Nikolay Samokhvalov <[email protected]>; Ilia Evdokimov <[email protected]>
Hi,
Playing around [1] to understand how, in practice, an engineer should
identify potential indexes, I found that nfiltered1 and nfiltered2 are
sufficient enough to detect issues with leaf table scan operators. But
the situation is worse when it comes to joins.
The idea behind JOIN optimisation is that sometimes a highly selective,
parameterised NestLoop is more performant than a HashJoin. What we need
is to identify that only a tiny part of the hash table or a sorted
MergeJoin input has been used to produce the JOIN result.
Thanks to [2 - 5], we have metrics showing how many tuples are removed
by joinqual and otherquals in a JOIN operator. That’s good for starters.
But some cases aren’t covered yet: how many tuples filtered by
hashclauses or mergeclauses.
In the attached file, you can see that for the same query, NestLoop
exposes 100k filtered tuples, but HashJoin shows nothing. Original
threads argued that ‘Filtered out’ characteristics should expose extra
work done by the operator. Hashing operation, especially on a set of
variable-length columns sometimes quite expensive. Further filtering,
involving hashclauses looks pretty similar to the joinqual filtering.
For me, ‘filtered’ value represents a flag that some massive part of the
input is not needed at all and using proper parameterisation and
indexing, we could optimise such a JOIN with NestLoop or MergeJoin.
From this point of view, it seems logical to add a nfiltered3
instrumentation field and account rows, filtered out by a ‘special’ join
clause like hashclauses or mergeclauses.
In the attachment, I propose a sketch on how to calculate these metrics.
MergeJoin looks more complicated and I don't propose it for now, but
HashJoin is quite trivial.
Known issues:
- Hash clause failures are counted in nfiltered1, which is shared with
join filter removals. If both are present, counts are combined.
- The metric only counts outer tuples with zero hash-value matches,
not hash collisions within buckets.
Thoughts?
[1] Proposal: Add rows_filtered column to pg_stat_statements for index
opportunity
detectionhttps://www.postgresql.org/message-id/CAM527d-r%2BRsaAeYsyAPmYtnmWB3rJFJtixUq4bnJW59nN%3DZo3w%40mail.gmail.com
[2] RFD: Discarded tuple count for SeqScan nodes in EXPLAIN ANALYZE
https://www.postgresql.org/message-id/flat/[email protected][3]
EXPLAIN and nfiltered - Mailing list pgsql-hackers
https://www.postgresql.org/message-id/[email protected]
[4] Re: REVIEW: EXPLAIN and nfiltered
https://www.postgresql.org/message-id/9053.1295888538%40sss.pgh.pa.us
[5] EXPLAIN and nfiltered, take two
https://www.postgresql.org/message-id/flat/4E68B108.1090907%402ndquadrant.com
--
regards, Andrei Lepikhov,
pgEdge
From d1f896a33bd4bf1a338c210023abd42b35838371 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Sat, 17 Jan 2026 00:13:50 +0100
Subject: [PATCH] Add "Rows Removed by Hash Matching" to EXPLAIN ANALYZE for
hash joins
This patch adds instrumentation to track outer tuples that found no
matching inner tuples during hash join bucket scanning. The new metric
"Rows Removed by Hash Matching" is displayed in EXPLAIN ANALYZE output
when hash clauses are present.
The implementation adds a third filtered-tuple counter (nfiltered3) to
the Instrumentation struct. For hash joins, this counter tracks outer
tuples where no inner tuple with a matching hash value was found in
the probed bucket.
Additionally, tuples where the hash value matched but the hash clause
evaluation failed are counted in nfiltered1 (displayed as part of
"Rows Removed by Join Filter" when a join filter is present).
---
src/backend/commands/explain.c | 7 ++++++-
src/backend/executor/instrument.c | 1 +
src/backend/executor/nodeHash.c | 20 ++++++++++++++++----
src/include/executor/instrument.h | 3 ++-
src/include/nodes/execnodes.h | 5 +++++
5 files changed, 30 insertions(+), 6 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index b7bb111688c..e68dc2d8eee 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -2191,6 +2191,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
"Hash Cond", planstate, ancestors, es);
show_upper_qual(((HashJoin *) plan)->join.joinqual,
"Join Filter", planstate, ancestors, es);
+ if (((HashJoin *) plan)->hashclauses)
+ show_instrumentation_count("Rows Removed by Hash Matching", 3,
+ planstate, es);
if (((HashJoin *) plan)->join.joinqual)
show_instrumentation_count("Rows Removed by Join Filter", 1,
planstate, es);
@@ -3998,7 +4001,9 @@ show_instrumentation_count(const char *qlabel, int which,
if (!es->analyze || !planstate->instrument)
return;
- if (which == 2)
+ if (which == 3)
+ nfiltered = planstate->instrument->nfiltered3;
+ else if (which == 2)
nfiltered = planstate->instrument->nfiltered2;
else
nfiltered = planstate->instrument->nfiltered1;
diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c
index edab92a0ebe..38a1b5a4757 100644
--- a/src/backend/executor/instrument.c
+++ b/src/backend/executor/instrument.c
@@ -183,6 +183,7 @@ InstrAggNode(Instrumentation *dst, Instrumentation *add)
dst->nloops += add->nloops;
dst->nfiltered1 += add->nfiltered1;
dst->nfiltered2 += add->nfiltered2;
+ dst->nfiltered3 += add->nfiltered3;
/* Add delta of buffer usage since entry to node's totals */
if (dst->need_bufusage)
diff --git a/src/backend/executor/nodeHash.c b/src/backend/executor/nodeHash.c
index f5d3edb90e2..66a9de6dbfc 100644
--- a/src/backend/executor/nodeHash.c
+++ b/src/backend/executor/nodeHash.c
@@ -1995,6 +1995,7 @@ ExecScanHashBucket(HashJoinState *hjstate,
HashJoinTable hashtable = hjstate->hj_HashTable;
HashJoinTuple hashTuple = hjstate->hj_CurTuple;
uint32 hashvalue = hjstate->hj_CurHashValue;
+ TupleTableSlot *inntuple = NULL;
/*
* hj_CurTuple is the address of the tuple last returned from the current
@@ -2014,8 +2015,6 @@ ExecScanHashBucket(HashJoinState *hjstate,
{
if (hashTuple->hashvalue == hashvalue)
{
- TupleTableSlot *inntuple;
-
/* insert hashtable's tuple into exec slot so ExecQual sees it */
inntuple = ExecStoreMinimalTuple(HJTUPLE_MINTUPLE(hashTuple),
hjstate->hj_HashTupleSlot,
@@ -2027,6 +2026,9 @@ ExecScanHashBucket(HashJoinState *hjstate,
hjstate->hj_CurTuple = hashTuple;
return true;
}
+
+ /* Tuple matched hash value but failed hash join clauses */
+ InstrCountFiltered1(&hjstate->js.ps, 1);
}
hashTuple = hashTuple->next.unshared;
@@ -2035,6 +2037,10 @@ ExecScanHashBucket(HashJoinState *hjstate,
/*
* no match
*/
+
+ if (hjstate->hj_CurTuple == NULL && inntuple == NULL)
+ InstrCountFiltered3(&hjstate->js.ps, 1);
+
return false;
}
@@ -2056,6 +2062,7 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
HashJoinTable hashtable = hjstate->hj_HashTable;
HashJoinTuple hashTuple = hjstate->hj_CurTuple;
uint32 hashvalue = hjstate->hj_CurHashValue;
+ TupleTableSlot *inntuple = NULL;
/*
* hj_CurTuple is the address of the tuple last returned from the current
@@ -2071,8 +2078,6 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
{
if (hashTuple->hashvalue == hashvalue)
{
- TupleTableSlot *inntuple;
-
/* insert hashtable's tuple into exec slot so ExecQual sees it */
inntuple = ExecStoreMinimalTuple(HJTUPLE_MINTUPLE(hashTuple),
hjstate->hj_HashTupleSlot,
@@ -2084,6 +2089,9 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
hjstate->hj_CurTuple = hashTuple;
return true;
}
+
+ /* Tuple matched hash value but failed hash clauses */
+ InstrCountFiltered1(&hjstate->js.ps, 1);
}
hashTuple = ExecParallelHashNextTuple(hashtable, hashTuple);
@@ -2092,6 +2100,10 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
/*
* no match
*/
+
+ if (hjstate->hj_CurTuple == NULL && inntuple == NULL)
+ InstrCountFiltered3(&hjstate->js.ps, 1);
+
return false;
}
diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h
index 9759f3ea5d8..200ecb2c348 100644
--- a/src/include/executor/instrument.h
+++ b/src/include/executor/instrument.h
@@ -88,8 +88,9 @@ typedef struct Instrumentation
double ntuples; /* total tuples produced */
double ntuples2; /* secondary node-specific tuple counter */
double nloops; /* # of run cycles for this node */
- double nfiltered1; /* # of tuples removed by scanqual or joinqual */
+ double nfiltered1; /* # of tuples removed by scanqual, joinqual or hashqual */
double nfiltered2; /* # of tuples removed by "other" quals */
+ double nfiltered3; /* # of tuples removed by "hash matching" */
BufferUsage bufusage; /* total buffer usage */
WalUsage walusage; /* total WAL usage */
} Instrumentation;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f8053d9e572..5de0a08a456 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1278,6 +1278,11 @@ typedef struct PlanState
if (((PlanState *)(node))->instrument) \
((PlanState *)(node))->instrument->nfiltered2 += (delta); \
} while(0)
+#define InstrCountFiltered3(node, delta) \
+ do { \
+ if (((PlanState *)(node))->instrument) \
+ ((PlanState *)(node))->instrument->nfiltered3 += (delta); \
+ } while(0)
/*
* EPQState is state for executing an EvalPlanQual recheck on a candidate
--
2.52.0
Attachments:
[application/sql] filtered-comparison.sql (2.1K, ../../[email protected]/2-filtered-comparison.sql)
download
[text/plain] 0001-Add-Rows-Removed-by-Hash-Matching-to-EXPLAIN-ANALYZE.patch (6.6K, ../../[email protected]/3-0001-Add-Rows-Removed-by-Hash-Matching-to-EXPLAIN-ANALYZE.patch)
download | inline diff:
From d1f896a33bd4bf1a338c210023abd42b35838371 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Sat, 17 Jan 2026 00:13:50 +0100
Subject: [PATCH] Add "Rows Removed by Hash Matching" to EXPLAIN ANALYZE for
hash joins
This patch adds instrumentation to track outer tuples that found no
matching inner tuples during hash join bucket scanning. The new metric
"Rows Removed by Hash Matching" is displayed in EXPLAIN ANALYZE output
when hash clauses are present.
The implementation adds a third filtered-tuple counter (nfiltered3) to
the Instrumentation struct. For hash joins, this counter tracks outer
tuples where no inner tuple with a matching hash value was found in
the probed bucket.
Additionally, tuples where the hash value matched but the hash clause
evaluation failed are counted in nfiltered1 (displayed as part of
"Rows Removed by Join Filter" when a join filter is present).
---
src/backend/commands/explain.c | 7 ++++++-
src/backend/executor/instrument.c | 1 +
src/backend/executor/nodeHash.c | 20 ++++++++++++++++----
src/include/executor/instrument.h | 3 ++-
src/include/nodes/execnodes.h | 5 +++++
5 files changed, 30 insertions(+), 6 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index b7bb111688c..e68dc2d8eee 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -2191,6 +2191,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
"Hash Cond", planstate, ancestors, es);
show_upper_qual(((HashJoin *) plan)->join.joinqual,
"Join Filter", planstate, ancestors, es);
+ if (((HashJoin *) plan)->hashclauses)
+ show_instrumentation_count("Rows Removed by Hash Matching", 3,
+ planstate, es);
if (((HashJoin *) plan)->join.joinqual)
show_instrumentation_count("Rows Removed by Join Filter", 1,
planstate, es);
@@ -3998,7 +4001,9 @@ show_instrumentation_count(const char *qlabel, int which,
if (!es->analyze || !planstate->instrument)
return;
- if (which == 2)
+ if (which == 3)
+ nfiltered = planstate->instrument->nfiltered3;
+ else if (which == 2)
nfiltered = planstate->instrument->nfiltered2;
else
nfiltered = planstate->instrument->nfiltered1;
diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c
index edab92a0ebe..38a1b5a4757 100644
--- a/src/backend/executor/instrument.c
+++ b/src/backend/executor/instrument.c
@@ -183,6 +183,7 @@ InstrAggNode(Instrumentation *dst, Instrumentation *add)
dst->nloops += add->nloops;
dst->nfiltered1 += add->nfiltered1;
dst->nfiltered2 += add->nfiltered2;
+ dst->nfiltered3 += add->nfiltered3;
/* Add delta of buffer usage since entry to node's totals */
if (dst->need_bufusage)
diff --git a/src/backend/executor/nodeHash.c b/src/backend/executor/nodeHash.c
index f5d3edb90e2..66a9de6dbfc 100644
--- a/src/backend/executor/nodeHash.c
+++ b/src/backend/executor/nodeHash.c
@@ -1995,6 +1995,7 @@ ExecScanHashBucket(HashJoinState *hjstate,
HashJoinTable hashtable = hjstate->hj_HashTable;
HashJoinTuple hashTuple = hjstate->hj_CurTuple;
uint32 hashvalue = hjstate->hj_CurHashValue;
+ TupleTableSlot *inntuple = NULL;
/*
* hj_CurTuple is the address of the tuple last returned from the current
@@ -2014,8 +2015,6 @@ ExecScanHashBucket(HashJoinState *hjstate,
{
if (hashTuple->hashvalue == hashvalue)
{
- TupleTableSlot *inntuple;
-
/* insert hashtable's tuple into exec slot so ExecQual sees it */
inntuple = ExecStoreMinimalTuple(HJTUPLE_MINTUPLE(hashTuple),
hjstate->hj_HashTupleSlot,
@@ -2027,6 +2026,9 @@ ExecScanHashBucket(HashJoinState *hjstate,
hjstate->hj_CurTuple = hashTuple;
return true;
}
+
+ /* Tuple matched hash value but failed hash join clauses */
+ InstrCountFiltered1(&hjstate->js.ps, 1);
}
hashTuple = hashTuple->next.unshared;
@@ -2035,6 +2037,10 @@ ExecScanHashBucket(HashJoinState *hjstate,
/*
* no match
*/
+
+ if (hjstate->hj_CurTuple == NULL && inntuple == NULL)
+ InstrCountFiltered3(&hjstate->js.ps, 1);
+
return false;
}
@@ -2056,6 +2062,7 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
HashJoinTable hashtable = hjstate->hj_HashTable;
HashJoinTuple hashTuple = hjstate->hj_CurTuple;
uint32 hashvalue = hjstate->hj_CurHashValue;
+ TupleTableSlot *inntuple = NULL;
/*
* hj_CurTuple is the address of the tuple last returned from the current
@@ -2071,8 +2078,6 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
{
if (hashTuple->hashvalue == hashvalue)
{
- TupleTableSlot *inntuple;
-
/* insert hashtable's tuple into exec slot so ExecQual sees it */
inntuple = ExecStoreMinimalTuple(HJTUPLE_MINTUPLE(hashTuple),
hjstate->hj_HashTupleSlot,
@@ -2084,6 +2089,9 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
hjstate->hj_CurTuple = hashTuple;
return true;
}
+
+ /* Tuple matched hash value but failed hash clauses */
+ InstrCountFiltered1(&hjstate->js.ps, 1);
}
hashTuple = ExecParallelHashNextTuple(hashtable, hashTuple);
@@ -2092,6 +2100,10 @@ ExecParallelScanHashBucket(HashJoinState *hjstate,
/*
* no match
*/
+
+ if (hjstate->hj_CurTuple == NULL && inntuple == NULL)
+ InstrCountFiltered3(&hjstate->js.ps, 1);
+
return false;
}
diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h
index 9759f3ea5d8..200ecb2c348 100644
--- a/src/include/executor/instrument.h
+++ b/src/include/executor/instrument.h
@@ -88,8 +88,9 @@ typedef struct Instrumentation
double ntuples; /* total tuples produced */
double ntuples2; /* secondary node-specific tuple counter */
double nloops; /* # of run cycles for this node */
- double nfiltered1; /* # of tuples removed by scanqual or joinqual */
+ double nfiltered1; /* # of tuples removed by scanqual, joinqual or hashqual */
double nfiltered2; /* # of tuples removed by "other" quals */
+ double nfiltered3; /* # of tuples removed by "hash matching" */
BufferUsage bufusage; /* total buffer usage */
WalUsage walusage; /* total WAL usage */
} Instrumentation;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f8053d9e572..5de0a08a456 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1278,6 +1278,11 @@ typedef struct PlanState
if (((PlanState *)(node))->instrument) \
((PlanState *)(node))->instrument->nfiltered2 += (delta); \
} while(0)
+#define InstrCountFiltered3(node, delta) \
+ do { \
+ if (((PlanState *)(node))->instrument) \
+ ((PlanState *)(node))->instrument->nfiltered3 += (delta); \
+ } while(0)
/*
* EPQState is state for executing an EvalPlanQual recheck on a candidate
--
2.52.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Add rows removed by hash join clause to instrumentation
2026-01-16 23:51 Add rows removed by hash join clause to instrumentation Andrei Lepikhov <[email protected]>
@ 2026-01-17 20:47 ` Andrei Lepikhov <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Andrei Lepikhov @ 2026-01-17 20:47 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Nikolay Samokhvalov <[email protected]>; Ilia Evdokimov <[email protected]>; Lukas Fittl <[email protected]>
On 17/1/26 00:51, Andrei Lepikhov wrote:
> Thoughts?
>
In this letter, I want to demonstrate how to use the proposed feature.
Install PostgreSQL 18 and Join-Order-Benchmark [1]. We need just one
pass, executing each query one-by-one. To identify issues, I use the
pg_track_optimizer extension [2] to examine the ‘bad join’ criterion.
For each JOIN node, the following is calculated:
jf_max = (nfiltered1 + nfiltered2 + nfiltered3) / nloops
Extension stores MAX(jf_max) for each query to detect potentially
optimisable ones. So, let’s benchmark both with and without the proposed
change. TOP-5 bad queries expose quite a different picture:
SELECT queryid,LEFT(query,12) AS query,floor(jf_max) AS jf_max
FROM pg_track_optimizer WHERE jf_max > 0
ORDER BY jf_max DESC LIMIT 5;
Without the patch:
queryid | query | jf_max
----------------------±-------------±-------
4335842597099666660 | /* 27c.sql * | 490348
patched Postgres and the extension:
queryid | query | jf_max
----------------------±-------------±-------
-3135863217603061370 | /* 32a.sql * | 871591
6389747766960672879 | /* 27c.sql * | 491129
-6029921280260605067 | /* 6a.sql / | 218912
5025464644910963332 | / 25b.sql * | 25753
1798119524484989875 | /* 10c.sql * | 22939
In the patched variant, we see more potential cases. Let’s take a look
at the first three of them.
Query 27c.sql (see analysis-27c.sql in attachment) is detected as
filtering too much in both cases because, on occasion, intensive
filtering occurs in a NestLoop node. Index identification is a separate
topic, but here I used a quite simple approach: I just asked Claude to
collect join clauses that filter out many rows and propose potential
indexes. It is not for production, but worked with such a simple and
limited set of queries.
So, without the patch, we have extra indexes:
CREATE INDEX ON movie_info(movie_id);
CREATE INDEX ON movie_companies(movie_id);
Patched version finds one more problematic join, and the recommendation
changes a little:
CREATE INDEX ind_3 ON movie_companies(movie_id,company_id,company_type_id);
After applying these indexes query speeds up 3340 ms → 32 ms (x100)
Now, undetected without patch 32a.sql, exposes that we can employ the
following indexes:
CREATE INDEX ON movie_keyword (keyword_id,movie_id);
CREATE INDEX ON movie_link(movie_id);
It speeds up query: 250ms → 16ms (x15)
And the last one, 6a.sql, needs two iterations:
The first one demonstrates we need the index:
CREATE INDEX ON cast_info(movie_id,person_id);
An additional pass shows we can reduce the number of fetched tuples with
the following index:
CREATE INDEX ON movie_keyword(keyword_id,movie_id);
Finally, that speeds up query: 150ms->220ms → 26ms (x7)
As you can see, the main idea is to draw attention to specific queries
and having specific methodology propose indexes that may reduce the size
of initially fetched data.
[1] https://github.com/danolivo/jo-bench
[2] https://github.com/danolivo/pg_track_optimizer/tree/nfiltered3
--
regards, Andrei Lepikhov,
pgEdge
Attachments:
[application/sql] analysis-06a.sql (6.6K, ../../[email protected]/2-analysis-06a.sql)
download
[application/sql] analysis-27c.sql (29.5K, ../../[email protected]/3-analysis-27c.sql)
download
[application/sql] analysis-32a.sql (6.7K, ../../[email protected]/4-analysis-32a.sql)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-01-17 20:47 UTC | newest]
Thread overview: 3+ 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]>
2026-01-16 23:51 Add rows removed by hash join clause to instrumentation Andrei Lepikhov <[email protected]>
2026-01-17 20:47 ` Re: Add rows removed by hash join clause to instrumentation Andrei Lepikhov <[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