agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH v49 1/7] sequential scan for dshash
24+ messages / 10 participants
[nested] [flat]
* [PATCH v49 1/7] sequential scan for dshash
@ 2020-03-13 07:58 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Kyotaro Horiguchi @ 2020-03-13 07:58 UTC (permalink / raw)
Dshash did not allow scan the all entries sequentially. This adds the
functionality. The interface is similar but a bit different both from
that of dynahash and simple dshash search functions. One of the most
significant differences is the sequential scan interface of dshash
always needs a call to dshash_seq_term when scan ends. Another is
locking. Dshash holds partition lock when returning an entry,
dshash_seq_next() also holds lock when returning an entry but callers
shouldn't release it, since the lock is essential to continue a
scan. The seqscan interface allows entry deletion while a scan. The
in-scan deletion should be performed by dshash_delete_current().
---
src/backend/lib/dshash.c | 160 ++++++++++++++++++++++++++++++-
src/include/lib/dshash.h | 22 +++++
src/tools/pgindent/typedefs.list | 1 +
3 files changed, 182 insertions(+), 1 deletion(-)
diff --git a/src/backend/lib/dshash.c b/src/backend/lib/dshash.c
index e0c763be32..520bfa0979 100644
--- a/src/backend/lib/dshash.c
+++ b/src/backend/lib/dshash.c
@@ -127,6 +127,10 @@ struct dshash_table
#define NUM_SPLITS(size_log2) \
(size_log2 - DSHASH_NUM_PARTITIONS_LOG2)
+/* How many buckets are there in a given size? */
+#define NUM_BUCKETS(size_log2) \
+ (((size_t) 1) << (size_log2))
+
/* How many buckets are there in each partition at a given size? */
#define BUCKETS_PER_PARTITION(size_log2) \
(((size_t) 1) << NUM_SPLITS(size_log2))
@@ -153,6 +157,10 @@ struct dshash_table
#define BUCKET_INDEX_FOR_PARTITION(partition, size_log2) \
((partition) << NUM_SPLITS(size_log2))
+/* Choose partition based on bucket index. */
+#define PARTITION_FOR_BUCKET_INDEX(bucket_idx, size_log2) \
+ ((bucket_idx) >> NUM_SPLITS(size_log2))
+
/* The head of the active bucket for a given hash value (lvalue). */
#define BUCKET_FOR_HASH(hash_table, hash) \
(hash_table->buckets[ \
@@ -324,7 +332,7 @@ dshash_destroy(dshash_table *hash_table)
ensure_valid_bucket_pointers(hash_table);
/* Free all the entries. */
- size = ((size_t) 1) << hash_table->size_log2;
+ size = NUM_BUCKETS(hash_table->size_log2);
for (i = 0; i < size; ++i)
{
dsa_pointer item_pointer = hash_table->buckets[i];
@@ -592,6 +600,156 @@ dshash_memhash(const void *v, size_t size, void *arg)
return tag_hash(v, size);
}
+/*
+ * dshash_seq_init/_next/_term
+ * Sequentially scan through dshash table and return all the
+ * elements one by one, return NULL when no more.
+ *
+ * dshash_seq_term should always be called when a scan finished.
+ * The caller may delete returned elements midst of a scan by using
+ * dshash_delete_current(). exclusive must be true to delete elements.
+ */
+void
+dshash_seq_init(dshash_seq_status *status, dshash_table *hash_table,
+ bool exclusive)
+{
+ status->hash_table = hash_table;
+ status->curbucket = 0;
+ status->nbuckets = 0;
+ status->curitem = NULL;
+ status->pnextitem = InvalidDsaPointer;
+ status->curpartition = -1;
+ status->exclusive = exclusive;
+}
+
+/*
+ * Returns the next element.
+ *
+ * Returned elements are locked and the caller must not explicitly release
+ * it. It is released at the next call to dshash_next().
+ */
+void *
+dshash_seq_next(dshash_seq_status *status)
+{
+ dsa_pointer next_item_pointer;
+
+ if (status->curitem == NULL)
+ {
+ int partition;
+
+ Assert(status->curbucket == 0);
+ Assert(!status->hash_table->find_locked);
+
+ /* first shot. grab the first item. */
+ partition =
+ PARTITION_FOR_BUCKET_INDEX(status->curbucket,
+ status->hash_table->size_log2);
+ LWLockAcquire(PARTITION_LOCK(status->hash_table, partition),
+ status->exclusive ? LW_EXCLUSIVE : LW_SHARED);
+ status->curpartition = partition;
+
+ /* resize doesn't happen from now until seq scan ends */
+ status->nbuckets =
+ NUM_BUCKETS(status->hash_table->control->size_log2);
+ ensure_valid_bucket_pointers(status->hash_table);
+
+ next_item_pointer = status->hash_table->buckets[status->curbucket];
+ }
+ else
+ next_item_pointer = status->pnextitem;
+
+ Assert(LWLockHeldByMeInMode(PARTITION_LOCK(status->hash_table,
+ status->curpartition),
+ status->exclusive ? LW_EXCLUSIVE : LW_SHARED));
+
+ /* Move to the next bucket if we finished the current bucket */
+ while (!DsaPointerIsValid(next_item_pointer))
+ {
+ int next_partition;
+
+ if (++status->curbucket >= status->nbuckets)
+ {
+ /* all buckets have been scanned. finish. */
+ return NULL;
+ }
+
+ /* Check if move to the next partition */
+ next_partition =
+ PARTITION_FOR_BUCKET_INDEX(status->curbucket,
+ status->hash_table->size_log2);
+
+ if (status->curpartition != next_partition)
+ {
+ /*
+ * Move to the next partition. Lock the next partition then release
+ * the current, not in the reverse order to avoid concurrent
+ * resizing. Avoid dead lock by taking lock in the same order
+ * with resize().
+ */
+ LWLockAcquire(PARTITION_LOCK(status->hash_table,
+ next_partition),
+ status->exclusive ? LW_EXCLUSIVE : LW_SHARED);
+ LWLockRelease(PARTITION_LOCK(status->hash_table,
+ status->curpartition));
+ status->curpartition = next_partition;
+ }
+
+ next_item_pointer = status->hash_table->buckets[status->curbucket];
+ }
+
+ status->curitem =
+ dsa_get_address(status->hash_table->area, next_item_pointer);
+ status->hash_table->find_locked = true;
+ status->hash_table->find_exclusively_locked = status->exclusive;
+
+ /*
+ * The caller may delete the item. Store the next item in case of deletion.
+ */
+ status->pnextitem = status->curitem->next;
+
+ return ENTRY_FROM_ITEM(status->curitem);
+}
+
+/*
+ * Terminates the seqscan and release all locks.
+ *
+ * Should be always called when finishing or exiting a seqscan.
+ */
+void
+dshash_seq_term(dshash_seq_status *status)
+{
+ status->hash_table->find_locked = false;
+ status->hash_table->find_exclusively_locked = false;
+
+ if (status->curpartition >= 0)
+ LWLockRelease(PARTITION_LOCK(status->hash_table, status->curpartition));
+}
+
+/* Remove the current entry while a seq scan. */
+void
+dshash_delete_current(dshash_seq_status *status)
+{
+ dshash_table *hash_table = status->hash_table;
+ dshash_table_item *item = status->curitem;
+ size_t partition = PARTITION_FOR_HASH(item->hash);
+
+ Assert(status->exclusive);
+ Assert(hash_table->control->magic == DSHASH_MAGIC);
+ Assert(hash_table->find_locked);
+ Assert(hash_table->find_exclusively_locked);
+ Assert(LWLockHeldByMeInMode(PARTITION_LOCK(hash_table, partition),
+ LW_EXCLUSIVE));
+
+ delete_item(hash_table, item);
+}
+
+/* Get the current entry while a seq scan. */
+void *
+dshash_get_current(dshash_seq_status *status)
+{
+ return ENTRY_FROM_ITEM(status->curitem);
+}
+
/*
* Print debugging information about the internal state of the hash table to
* stderr. The caller must hold no partition locks.
diff --git a/src/include/lib/dshash.h b/src/include/lib/dshash.h
index c069ec9de7..a6ea377173 100644
--- a/src/include/lib/dshash.h
+++ b/src/include/lib/dshash.h
@@ -59,6 +59,21 @@ typedef struct dshash_parameters
struct dshash_table_item;
typedef struct dshash_table_item dshash_table_item;
+/*
+ * Sequential scan state. The detail is exposed to let users know the storage
+ * size but it should be considered as an opaque type by callers.
+ */
+typedef struct dshash_seq_status
+{
+ dshash_table *hash_table; /* dshash table working on */
+ int curbucket; /* bucket number we are at */
+ int nbuckets; /* total number of buckets in the dshash */
+ dshash_table_item *curitem; /* item we are currently at */
+ dsa_pointer pnextitem; /* dsa-pointer to the next item */
+ int curpartition; /* partition number we are at */
+ bool exclusive; /* locking mode */
+} dshash_seq_status;
+
/* Creating, sharing and destroying from hash tables. */
extern dshash_table *dshash_create(dsa_area *area,
const dshash_parameters *params,
@@ -80,6 +95,13 @@ extern bool dshash_delete_key(dshash_table *hash_table, const void *key);
extern void dshash_delete_entry(dshash_table *hash_table, void *entry);
extern void dshash_release_lock(dshash_table *hash_table, void *entry);
+/* seq scan support */
+extern void dshash_seq_init(dshash_seq_status *status, dshash_table *hash_table,
+ bool exclusive);
+extern void *dshash_seq_next(dshash_seq_status *status);
+extern void dshash_seq_term(dshash_seq_status *status);
+extern void dshash_delete_current(dshash_seq_status *status);
+extern void *dshash_get_current(dshash_seq_status *status);
/* Convenience hash and compare functions wrapping memcmp and tag_hash. */
extern int dshash_memcmp(const void *a, const void *b, size_t size, void *arg);
extern dshash_hash dshash_memhash(const void *v, size_t size, void *arg);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8bd95aefa1..2b591e94e6 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2954,6 +2954,7 @@ dshash_hash
dshash_hash_function
dshash_parameters
dshash_partition
+dshash_seq_status
dshash_table
dshash_table_control
dshash_table_handle
--
2.27.0
----Next_Part(Tue_Mar__9_16_53_11_2021_575)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v49-0002-Add-conditional-lock-feature-to-dshash.patch"
^ permalink raw reply [nested|flat] 24+ messages in thread
* Prefetch the next tuple's memory during seqscans
@ 2022-10-31 03:52 David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: David Rowley @ 2022-10-31 03:52 UTC (permalink / raw)
To: PostgreSQL Developers <[email protected]>; +Cc: Thomas Munro <[email protected]>
As part of the AIO work [1], Andres mentioned to me that he found that
prefetching tuple memory during hot pruning showed significant wins.
I'm not proposing anything to improve HOT pruning here, but as a segue
to get the prefetching infrastructure in so that there are fewer AIO
patches, I'm proposing we prefetch the next tuple during sequence
scans in while page mode.
It turns out the gains are pretty good when we apply this:
-- table with 4 bytes of user columns
create table t as select a from generate_series(1,10000000)a;
vacuum freeze t;
select pg_prewarm('t');
Master @ a9f8ca600
# select * from t where a = 0;
Time: 355.001 ms
Time: 354.573 ms
Time: 354.490 ms
Time: 354.556 ms
Time: 354.335 ms
Master + 0001 + 0003:
# select * from t where a = 0;
Time: 328.578 ms
Time: 329.387 ms
Time: 329.349 ms
Time: 329.704 ms
Time: 328.225 ms (avg ~7.7% faster)
-- table with 64 bytes of user columns
create table t2 as
select a,a a2,a a3,a a4,a a5,a a6,a a7,a a8,a a9,a a10,a a11,a a12,a
a13,a a14,a a15,a a16
from generate_series(1,10000000)a;
vacuum freeze t2;
select pg_prewarm('t2');
Master:
# select * from t2 where a = 0;
Time: 501.725 ms
Time: 501.815 ms
Time: 503.225 ms
Time: 501.242 ms
Time: 502.394 ms
Master + 0001 + 0003:
# select * from t2 where a = 0;
Time: 412.076 ms
Time: 410.669 ms
Time: 410.490 ms
Time: 409.782 ms
Time: 410.843 ms (avg ~22% faster)
This was tested on an AMD 3990x CPU. I imagine the CPU matters quite a
bit here. It would be interesting to see if the same or similar gains
can be seen on some modern intel chip too.
I believe Thomas wrote the 0001 patch (same as patch in [2]?). I only
quickly put together the 0003 patch.
I wondered if we might want to add a macro to 0001 that says if
pg_prefetch_mem() is empty or not then use that to #ifdef out the code
I added to heapam.c. Although, perhaps most compilers will be able to
optimise away the extra lines that are figuring out what the address
of the next tuple is.
My tests above are likely the best case for this. It seems plausible
to me that if there was a much more complex plan that found a
reasonable number of tuples and did something with them that we
wouldn't see the same sort of gains. Also, it also does not seem
impossible that the prefetch just results in evicting some
useful-to-some-other-exec-node cache line or that the prefetched tuple
gets flushed out the cache by the time we get around to fetching the
next tuple from the scan again due to various other node processing
that's occurred since the seq scan was last called. I imagine such
things would be indistinguishable from noise, but I've not tested.
I also tried prefetching out by 2 tuples. It didn't help any further
than prefetching 1 tuple.
I'll add this to the November CF.
David
[1] https://www.postgresql.org/message-id/flat/[email protected]
[2] https://www.postgresql.org/message-id/CA%2BhUKG%2Bpi63ZbcZkYK3XB1pfN%3DkuaDaeV0Ha9E%2BX_p6TTbKBYw%40...
From 2fd10f1266550f26f4395de080bcdcf89b6859b6 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Wed, 19 Oct 2022 08:54:01 +1300
Subject: [PATCH 1/3] Add pg_prefetch_mem() macro to load cache lines.
Initially mapping to GCC, Clang and MSVC builtins.
Discussion: https://postgr.es/m/CAEepm%3D2y9HM9QP%2BHhRZdQ3pU6FShSMyu%3DV1uHXhQ5gG-dketHg%40mail.gmail.com
---
config/c-compiler.m4 | 17 ++++++++++++++++
configure | 40 ++++++++++++++++++++++++++++++++++++++
configure.ac | 3 +++
meson.build | 3 ++-
src/include/c.h | 8 ++++++++
src/include/pg_config.h.in | 3 +++
src/tools/msvc/Solution.pm | 1 +
7 files changed, 74 insertions(+), 1 deletion(-)
diff --git a/config/c-compiler.m4 b/config/c-compiler.m4
index 000b075312..582a47501c 100644
--- a/config/c-compiler.m4
+++ b/config/c-compiler.m4
@@ -355,6 +355,23 @@ AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
[Define to 1 if your compiler understands $1.])
fi])# PGAC_CHECK_BUILTIN_FUNC
+# PGAC_CHECK_BUILTIN_VOID_FUNC
+# -----------------------
+# Variant for void functions.
+AC_DEFUN([PGAC_CHECK_BUILTIN_VOID_FUNC],
+[AC_CACHE_CHECK(for $1, pgac_cv$1,
+[AC_LINK_IFELSE([AC_LANG_PROGRAM([
+void
+call$1($2)
+{
+ $1(x);
+}], [])],
+[pgac_cv$1=yes],
+[pgac_cv$1=no])])
+if test x"${pgac_cv$1}" = xyes ; then
+AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
+ [Define to 1 if your compiler understands $1.])
+fi])# PGAC_CHECK_BUILTIN_VOID_FUNC
# PGAC_CHECK_BUILTIN_FUNC_PTR
diff --git a/configure b/configure
index 3966368b8d..c4685b8a1e 100755
--- a/configure
+++ b/configure
@@ -15988,6 +15988,46 @@ _ACEOF
fi
+# Can we use a built-in to prefetch memory?
+{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for __builtin_prefetch" >&5
+$as_echo_n "checking for __builtin_prefetch... " >&6; }
+if ${pgac_cv__builtin_prefetch+:} false; then :
+ $as_echo_n "(cached) " >&6
+else
+ cat confdefs.h - <<_ACEOF >conftest.$ac_ext
+/* end confdefs.h. */
+
+void
+call__builtin_prefetch(void *x)
+{
+ __builtin_prefetch(x);
+}
+int
+main ()
+{
+
+ ;
+ return 0;
+}
+_ACEOF
+if ac_fn_c_try_link "$LINENO"; then :
+ pgac_cv__builtin_prefetch=yes
+else
+ pgac_cv__builtin_prefetch=no
+fi
+rm -f core conftest.err conftest.$ac_objext \
+ conftest$ac_exeext conftest.$ac_ext
+fi
+{ $as_echo "$as_me:${as_lineno-$LINENO}: result: $pgac_cv__builtin_prefetch" >&5
+$as_echo "$pgac_cv__builtin_prefetch" >&6; }
+if test x"${pgac_cv__builtin_prefetch}" = xyes ; then
+
+cat >>confdefs.h <<_ACEOF
+#define HAVE__BUILTIN_PREFETCH 1
+_ACEOF
+
+fi
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for _LARGEFILE_SOURCE value needed for large files" >&5
diff --git a/configure.ac b/configure.ac
index f76b7ee31f..2d4938d43d 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1802,6 +1802,9 @@ PGAC_CHECK_BUILTIN_FUNC([__builtin_popcount], [unsigned int x])
# so it needs a different test function.
PGAC_CHECK_BUILTIN_FUNC_PTR([__builtin_frame_address], [0])
+# Can we use a built-in to prefetch memory?
+PGAC_CHECK_BUILTIN_VOID_FUNC([__builtin_prefetch], [void *x])
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
AC_FUNC_FSEEKO
diff --git a/meson.build b/meson.build
index bfacbdc0af..9c35637826 100644
--- a/meson.build
+++ b/meson.build
@@ -1587,10 +1587,11 @@ builtins = [
'bswap32',
'bswap64',
'clz',
- 'ctz',
'constant_p',
+ 'ctz',
'frame_address',
'popcount',
+ 'prefetch',
'unreachable',
]
diff --git a/src/include/c.h b/src/include/c.h
index d70ed84ac5..26a1586dc3 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -361,6 +361,14 @@ typedef void (*pg_funcptr_t) (void);
*/
#define FLEXIBLE_ARRAY_MEMBER /* empty */
+/* Do we have support for prefetching memory? */
+#if defined(HAVE__BUILTIN_PREFETCH)
+#define pg_prefetch_mem(a) __builtin_prefetch(a)
+#elif defined(_MSC_VER)
+#define pg_prefetch_mem(a) _m_prefetch(a)
+#else
+#define pg_prefetch_mem(a)
+#endif
/* ----------------------------------------------------------------
* Section 2: bool, true, false
diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in
index c5a80b829e..07a661e288 100644
--- a/src/include/pg_config.h.in
+++ b/src/include/pg_config.h.in
@@ -559,6 +559,9 @@
/* Define to 1 if your compiler understands __builtin_popcount. */
#undef HAVE__BUILTIN_POPCOUNT
+/* Define to 1 if your compiler understands __builtin_prefetch. */
+#undef HAVE__BUILTIN_PREFETCH
+
/* Define to 1 if your compiler understands __builtin_types_compatible_p. */
#undef HAVE__BUILTIN_TYPES_COMPATIBLE_P
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index c2acb58df0..95de91890e 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -227,6 +227,7 @@ sub GenerateFiles
HAVE_BACKTRACE_SYMBOLS => undef,
HAVE_BIO_GET_DATA => undef,
HAVE_BIO_METH_NEW => undef,
+ HAVE__BUILTIN_PREFETCH => undef,
HAVE_COMPUTED_GOTO => undef,
HAVE_COPYFILE => undef,
HAVE_COPYFILE_H => undef,
--
2.34.1
From 8459bc4bcdf0403f8c9513dd4d1fed0840acafc1 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Mon, 31 Oct 2022 10:05:12 +1300
Subject: [PATCH 3/3] Prefetch tuple memory during forward seqscans
---
src/backend/access/heap/heapam.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 12be87efed..e8f1fc2d71 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1025,6 +1025,17 @@ heapgettup_pagemode(HeapScanDesc scan,
tuple->t_len = ItemIdGetLength(lpp);
ItemPointerSet(&(tuple->t_self), page, lineoff);
+ /*
+ * Prefetching the memory for the next tuple has shown to improve
+ * performance on certain hardware.
+ */
+ if (!backward && linesleft > 1)
+ {
+ lineoff = scan->rs_vistuples[lineindex + 1];
+ lpp = PageGetItemId(dp, lineoff);
+ pg_prefetch_mem(PageGetItem((Page) dp, lpp));
+ }
+
/*
* if current tuple qualifies, return it.
*/
--
2.34.1
Attachments:
[text/plain] 0001-Add-pg_prefetch_mem-macro-to-load-cache-lines.patch (5.2K, ../../CAApHDvpTRx7hqFZGiZJ=d9JN4h1tzJ2=xt7bM-9XRmpVj63psQ@mail.gmail.com/2-0001-Add-pg_prefetch_mem-macro-to-load-cache-lines.patch)
download | inline diff:
From 2fd10f1266550f26f4395de080bcdcf89b6859b6 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Wed, 19 Oct 2022 08:54:01 +1300
Subject: [PATCH 1/3] Add pg_prefetch_mem() macro to load cache lines.
Initially mapping to GCC, Clang and MSVC builtins.
Discussion: https://postgr.es/m/CAEepm%3D2y9HM9QP%2BHhRZdQ3pU6FShSMyu%3DV1uHXhQ5gG-dketHg%40mail.gmail.com
---
config/c-compiler.m4 | 17 ++++++++++++++++
configure | 40 ++++++++++++++++++++++++++++++++++++++
configure.ac | 3 +++
meson.build | 3 ++-
src/include/c.h | 8 ++++++++
src/include/pg_config.h.in | 3 +++
src/tools/msvc/Solution.pm | 1 +
7 files changed, 74 insertions(+), 1 deletion(-)
diff --git a/config/c-compiler.m4 b/config/c-compiler.m4
index 000b075312..582a47501c 100644
--- a/config/c-compiler.m4
+++ b/config/c-compiler.m4
@@ -355,6 +355,23 @@ AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
[Define to 1 if your compiler understands $1.])
fi])# PGAC_CHECK_BUILTIN_FUNC
+# PGAC_CHECK_BUILTIN_VOID_FUNC
+# -----------------------
+# Variant for void functions.
+AC_DEFUN([PGAC_CHECK_BUILTIN_VOID_FUNC],
+[AC_CACHE_CHECK(for $1, pgac_cv$1,
+[AC_LINK_IFELSE([AC_LANG_PROGRAM([
+void
+call$1($2)
+{
+ $1(x);
+}], [])],
+[pgac_cv$1=yes],
+[pgac_cv$1=no])])
+if test x"${pgac_cv$1}" = xyes ; then
+AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
+ [Define to 1 if your compiler understands $1.])
+fi])# PGAC_CHECK_BUILTIN_VOID_FUNC
# PGAC_CHECK_BUILTIN_FUNC_PTR
diff --git a/configure b/configure
index 3966368b8d..c4685b8a1e 100755
--- a/configure
+++ b/configure
@@ -15988,6 +15988,46 @@ _ACEOF
fi
+# Can we use a built-in to prefetch memory?
+{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for __builtin_prefetch" >&5
+$as_echo_n "checking for __builtin_prefetch... " >&6; }
+if ${pgac_cv__builtin_prefetch+:} false; then :
+ $as_echo_n "(cached) " >&6
+else
+ cat confdefs.h - <<_ACEOF >conftest.$ac_ext
+/* end confdefs.h. */
+
+void
+call__builtin_prefetch(void *x)
+{
+ __builtin_prefetch(x);
+}
+int
+main ()
+{
+
+ ;
+ return 0;
+}
+_ACEOF
+if ac_fn_c_try_link "$LINENO"; then :
+ pgac_cv__builtin_prefetch=yes
+else
+ pgac_cv__builtin_prefetch=no
+fi
+rm -f core conftest.err conftest.$ac_objext \
+ conftest$ac_exeext conftest.$ac_ext
+fi
+{ $as_echo "$as_me:${as_lineno-$LINENO}: result: $pgac_cv__builtin_prefetch" >&5
+$as_echo "$pgac_cv__builtin_prefetch" >&6; }
+if test x"${pgac_cv__builtin_prefetch}" = xyes ; then
+
+cat >>confdefs.h <<_ACEOF
+#define HAVE__BUILTIN_PREFETCH 1
+_ACEOF
+
+fi
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for _LARGEFILE_SOURCE value needed for large files" >&5
diff --git a/configure.ac b/configure.ac
index f76b7ee31f..2d4938d43d 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1802,6 +1802,9 @@ PGAC_CHECK_BUILTIN_FUNC([__builtin_popcount], [unsigned int x])
# so it needs a different test function.
PGAC_CHECK_BUILTIN_FUNC_PTR([__builtin_frame_address], [0])
+# Can we use a built-in to prefetch memory?
+PGAC_CHECK_BUILTIN_VOID_FUNC([__builtin_prefetch], [void *x])
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
AC_FUNC_FSEEKO
diff --git a/meson.build b/meson.build
index bfacbdc0af..9c35637826 100644
--- a/meson.build
+++ b/meson.build
@@ -1587,10 +1587,11 @@ builtins = [
'bswap32',
'bswap64',
'clz',
- 'ctz',
'constant_p',
+ 'ctz',
'frame_address',
'popcount',
+ 'prefetch',
'unreachable',
]
diff --git a/src/include/c.h b/src/include/c.h
index d70ed84ac5..26a1586dc3 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -361,6 +361,14 @@ typedef void (*pg_funcptr_t) (void);
*/
#define FLEXIBLE_ARRAY_MEMBER /* empty */
+/* Do we have support for prefetching memory? */
+#if defined(HAVE__BUILTIN_PREFETCH)
+#define pg_prefetch_mem(a) __builtin_prefetch(a)
+#elif defined(_MSC_VER)
+#define pg_prefetch_mem(a) _m_prefetch(a)
+#else
+#define pg_prefetch_mem(a)
+#endif
/* ----------------------------------------------------------------
* Section 2: bool, true, false
diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in
index c5a80b829e..07a661e288 100644
--- a/src/include/pg_config.h.in
+++ b/src/include/pg_config.h.in
@@ -559,6 +559,9 @@
/* Define to 1 if your compiler understands __builtin_popcount. */
#undef HAVE__BUILTIN_POPCOUNT
+/* Define to 1 if your compiler understands __builtin_prefetch. */
+#undef HAVE__BUILTIN_PREFETCH
+
/* Define to 1 if your compiler understands __builtin_types_compatible_p. */
#undef HAVE__BUILTIN_TYPES_COMPATIBLE_P
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index c2acb58df0..95de91890e 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -227,6 +227,7 @@ sub GenerateFiles
HAVE_BACKTRACE_SYMBOLS => undef,
HAVE_BIO_GET_DATA => undef,
HAVE_BIO_METH_NEW => undef,
+ HAVE__BUILTIN_PREFETCH => undef,
HAVE_COMPUTED_GOTO => undef,
HAVE_COPYFILE => undef,
HAVE_COPYFILE_H => undef,
--
2.34.1
[text/plain] 0003-Prefetch-tuple-memory-during-forward-seqscans.patch (1.0K, ../../CAApHDvpTRx7hqFZGiZJ=d9JN4h1tzJ2=xt7bM-9XRmpVj63psQ@mail.gmail.com/3-0003-Prefetch-tuple-memory-during-forward-seqscans.patch)
download | inline diff:
From 8459bc4bcdf0403f8c9513dd4d1fed0840acafc1 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Mon, 31 Oct 2022 10:05:12 +1300
Subject: [PATCH 3/3] Prefetch tuple memory during forward seqscans
---
src/backend/access/heap/heapam.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 12be87efed..e8f1fc2d71 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1025,6 +1025,17 @@ heapgettup_pagemode(HeapScanDesc scan,
tuple->t_len = ItemIdGetLength(lpp);
ItemPointerSet(&(tuple->t_self), page, lineoff);
+ /*
+ * Prefetching the memory for the next tuple has shown to improve
+ * performance on certain hardware.
+ */
+ if (!backward && linesleft > 1)
+ {
+ lineoff = scan->rs_vistuples[lineindex + 1];
+ lpp = PageGetItemId(dp, lineoff);
+ pg_prefetch_mem(PageGetItem((Page) dp, lpp));
+ }
+
/*
* if current tuple qualifies, return it.
*/
--
2.34.1
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-10-31 14:12 ` Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Aleksander Alekseev @ 2022-10-31 14:12 UTC (permalink / raw)
To: PostgreSQL Developers <[email protected]>; +Cc: David Rowley <[email protected]>; Thomas Munro <[email protected]>
Hi David,
> I'll add this to the November CF.
Thanks for the patch.
I wonder if we can be sure and/or check that there is no performance
degradation under different loads and different platforms...
Also I see 0001 and 0003 but no 0002. Just wanted to double check that
there is no patch missing.
--
Best regards,
Aleksander Alekseev
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
@ 2022-10-31 22:17 ` David Rowley <[email protected]>
2022-11-01 11:08 ` Re: Prefetch the next tuple's memory during seqscans Andy Fan <[email protected]>
2022-11-03 09:09 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: David Rowley @ 2022-10-31 22:17 UTC (permalink / raw)
To: Aleksander Alekseev <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Tue, 1 Nov 2022 at 03:12, Aleksander Alekseev
<[email protected]> wrote:
> I wonder if we can be sure and/or check that there is no performance
> degradation under different loads and different platforms...
Different platforms would be good. Certainly, 1 platform isn't a good
enough indication that this is going to be useful.
As for different loads. I imagine the worst case for this will be that
the prefetched tuple is flushed from the cache by some other operation
in the plan making the prefetch useless.
I tried the following so that we read 1 million tuples from a Sort
node before coming back and reading another tuple from the seqscan.
create table a as select 1 as a from generate_series(1,2) a;
create table b as select 1 as a from generate_series(1,10000000) a;
vacuum freeze a,b;
select pg_prewarm('a'),pg_prewarm('b');
set work_mem = '256MB';
select * from a, lateral (select * from b order by a) b offset 20000000;
Master (@ a9f8ca600)
Time: 1414.590 ms (00:01.415)
Time: 1373.584 ms (00:01.374)
Time: 1373.057 ms (00:01.373)
Time: 1383.033 ms (00:01.383)
Time: 1378.865 ms (00:01.379)
Master + 0001 + 0003:
Time: 1352.726 ms (00:01.353)
Time: 1348.306 ms (00:01.348)
Time: 1358.033 ms (00:01.358)
Time: 1354.348 ms (00:01.354)
Time: 1353.971 ms (00:01.354)
As I'd have expected, I see no regression. It's hard to imagine we'd
be able to measure the regression over the overhead of some operation
that would evict everything out of cache.
FWIW, this CPU has a 256MB L3 cache and the Sort node's EXPLAIN
ANALYZE looks like:
Sort Method: quicksort Memory: 262144kB
> Also I see 0001 and 0003 but no 0002. Just wanted to double check that
> there is no patch missing.
Perhaps I should resequence the patches to avoid confusion. I didn't
send 0002 on purpose. The 0002 is Andres' patch to prefetch during HOT
pruning. Here I'm only interested in seeing if we can get the
pg_prefetch_mem macros in core to reduce the number of AIO patches by
1.
Another thing about this is that I'm really only fetching the first
cache line of the tuple. All columns in the t2 table (from the
earlier email) are fixed width, so accessing the a16 column is a
cached offset. I ran a benchmark using the same t2 table as my earlier
email, i.e:
-- table with 64 bytes of user columns
create table t2 as
select a,a a2,a a3,a a4,a a5,a a6,a a7,a a8,a a9,a a10,a a11,a a12,a
a13,a a14,a a15,a a16
from generate_series(1,10000000)a;
vacuum freeze t2;
My test is to run 16 queries changing the WHERE clause each time to
have WHERE a = 0, then WHERE a2 = 0 ... WHERE a16 = 0. I wanted to
know if prefetching only the first cache line of the tuple would be
less useful when we require evaluation of say, the "a16" column vs the
"a" column.
The times below (in milliseconds) are what I got from a 10-second pgbench run:
column master patched
a 490.571 409.748
a2 428.004 430.927
a3 449.156 453.858
a4 474.945 479.73
a5 514.646 507.809
a6 517.525 519.956
a7 543.587 539.023
a8 562.718 559.387
a9 585.458 584.63
a10 609.143 604.606
a11 645.273 638.535
a12 658.848 657.377
a13 696.395 685.389
a14 702.779 716.722
a15 727.161 723.567
a16 756.186 749.396
I'm not sure how to explain why only the "a" column seems to improve
and the rest seem mostly unaffected.
David
#!/bin/bash
psql -c "select pg_prewarm('t2');" postgres
for i in a a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16;
do
echo Testing $i
echo "select * from t2 where $i = 0;" > bench.sql
pgbench -f bench.sql -M prepared -n -T 10 postgres | grep latency
done
Attachments:
[text/plain] bench_prefetch.sh.txt (269B, ../../CAApHDvqWexy_6jGmB39Vr3OqxZ_w6stAFkq52hODvwaW-19aiA@mail.gmail.com/2-bench_prefetch.sh.txt)
download | inline:
#!/bin/bash
psql -c "select pg_prewarm('t2');" postgres
for i in a a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16;
do
echo Testing $i
echo "select * from t2 where $i = 0;" > bench.sql
pgbench -f bench.sql -M prepared -n -T 10 postgres | grep latency
done
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-11-01 11:08 ` Andy Fan <[email protected]>
2022-11-01 11:42 ` Re: Prefetch the next tuple's memory during seqscans Thomas Munro <[email protected]>
2022-11-01 11:50 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
1 sibling, 2 replies; 24+ messages in thread
From: Andy Fan @ 2022-11-01 11:08 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
Hi:
> Different platforms would be good. Certainly, 1 platform isn't a good
> enough indication that this is going to be useful.
I just have a different platforms at hand, Here is my test with
Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz.
shared_buffers has been set to big enough to hold all the data.
columns Master Patched Improvement
a 310.931 289.251 6.972608071
a2 329.577 299.975 8.981816085
a3 336.887 313.502 6.941496704
a4 352.099 325.345 7.598431123
a5 358.582 336.486 6.162049406
a6 375.004 349.12 6.902326375
a7 379.699 362.998 4.398484062
a8 391.911 371.41 5.231034597
a9 404.3 383.779 5.075686372
a10 425.48 396.114 6.901852026
a11 449.944 431.826 4.026723326
a12 461.876 443.579 3.961452857
a13 470.59 460.237 2.20000425
a14 483.332 467.078 3.362905829
a15 490.798 472.262 3.776706507
a16 503.321 484.322 3.774728255
By theory, Why does the preferch make thing better? I am asking this
because I think we need to read the data from buffer to cache line once
in either case (I'm obvious wrong in face of the test result.)
Another simple point is the below styles are same. But the format 3 looks
clearer than others for me. It can tell code reader more stuffs. just fyi.
pg_prefetch_mem(PageGetItem((Page) dp, lpp));
pg_prefetch_mem(tuple->t_data);
pg_prefetch_mem((scan->rs_ctup.t_data);
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-01 11:08 ` Re: Prefetch the next tuple's memory during seqscans Andy Fan <[email protected]>
@ 2022-11-01 11:42 ` Thomas Munro <[email protected]>
2022-11-23 16:03 ` Re: Prefetch the next tuple's memory during seqscans Bruce Momjian <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Thomas Munro @ 2022-11-01 11:42 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: David Rowley <[email protected]>; Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>
On Wed, Nov 2, 2022 at 12:09 AM Andy Fan <[email protected]> wrote:
> By theory, Why does the preferch make thing better? I am asking this
> because I think we need to read the data from buffer to cache line once
> in either case (I'm obvious wrong in face of the test result.)
CPUs have several different kinds of 'hardware prefetchers' (worth
reading about), that look out for sequential and striding patterns and
try to get the cache line ready before you access it. Using the
prefetch instructions explicitly is called 'software prefetching'
(special instructions inserted by programmers or compilers). The
theory here would have to be that the hardware prefetchers couldn't
pick up the pattern, but we know how to do it. The exact details of
the hardware prefetchers vary between chips, and there are even some
parameters you can adjust in BIOS settings. One idea is that the
hardware prefetchers are generally biased towards increasing
addresses, but our tuples tend to go backwards on the page[1]. It's
possible that some other CPUs can detect backwards strides better, but
since real world tuples aren't of equal size anyway, there isn't
really a fixed stride at all, so software prefetching seems quite
promising for this...
[1] https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-PAGE-LAYOUT-FIGURE
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-01 11:08 ` Re: Prefetch the next tuple's memory during seqscans Andy Fan <[email protected]>
2022-11-01 11:42 ` Re: Prefetch the next tuple's memory during seqscans Thomas Munro <[email protected]>
@ 2022-11-23 16:03 ` Bruce Momjian <[email protected]>
2022-11-23 16:14 ` Re: Prefetch the next tuple's memory during seqscans Bruce Momjian <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Bruce Momjian @ 2022-11-23 16:03 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Andy Fan <[email protected]>; David Rowley <[email protected]>; Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>
On Wed, Nov 2, 2022 at 12:42:11AM +1300, Thomas Munro wrote:
> On Wed, Nov 2, 2022 at 12:09 AM Andy Fan <[email protected]> wrote:
> > By theory, Why does the preferch make thing better? I am asking this
> > because I think we need to read the data from buffer to cache line once
> > in either case (I'm obvious wrong in face of the test result.)
>
> CPUs have several different kinds of 'hardware prefetchers' (worth
> reading about), that look out for sequential and striding patterns and
> try to get the cache line ready before you access it. Using the
> prefetch instructions explicitly is called 'software prefetching'
> (special instructions inserted by programmers or compilers). The
> theory here would have to be that the hardware prefetchers couldn't
> pick up the pattern, but we know how to do it. The exact details of
> the hardware prefetchers vary between chips, and there are even some
> parameters you can adjust in BIOS settings. One idea is that the
> hardware prefetchers are generally biased towards increasing
> addresses, but our tuples tend to go backwards on the page[1]. It's
> possible that some other CPUs can detect backwards strides better, but
> since real world tuples aren't of equal size anyway, there isn't
> really a fixed stride at all, so software prefetching seems quite
> promising for this...
>
> [1] https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-PAGE-LAYOUT-FIGURE
I remember someone showing that having our item pointers at the _end_ of
the page and tuples at the start moving toward the end increased
performance significantly.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-01 11:08 ` Re: Prefetch the next tuple's memory during seqscans Andy Fan <[email protected]>
2022-11-01 11:42 ` Re: Prefetch the next tuple's memory during seqscans Thomas Munro <[email protected]>
2022-11-23 16:03 ` Re: Prefetch the next tuple's memory during seqscans Bruce Momjian <[email protected]>
@ 2022-11-23 16:14 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Bruce Momjian @ 2022-11-23 16:14 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Andy Fan <[email protected]>; David Rowley <[email protected]>; Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>
On Wed, Nov 23, 2022 at 11:03:22AM -0500, Bruce Momjian wrote:
> > CPUs have several different kinds of 'hardware prefetchers' (worth
> > reading about), that look out for sequential and striding patterns and
> > try to get the cache line ready before you access it. Using the
> > prefetch instructions explicitly is called 'software prefetching'
> > (special instructions inserted by programmers or compilers). The
> > theory here would have to be that the hardware prefetchers couldn't
> > pick up the pattern, but we know how to do it. The exact details of
> > the hardware prefetchers vary between chips, and there are even some
> > parameters you can adjust in BIOS settings. One idea is that the
> > hardware prefetchers are generally biased towards increasing
> > addresses, but our tuples tend to go backwards on the page[1]. It's
> > possible that some other CPUs can detect backwards strides better, but
> > since real world tuples aren't of equal size anyway, there isn't
> > really a fixed stride at all, so software prefetching seems quite
> > promising for this...
> >
> > [1] https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-PAGE-LAYOUT-FIGURE
>
> I remember someone showing that having our item pointers at the _end_ of
> the page and tuples at the start moving toward the end increased
> performance significantly.
Ah, I found it, from 2017, with a 15-25% slowdown:
https://www.postgresql.org/message-id/20171108205943.tps27i2tujsstrg7%40alap3.anarazel.de
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-01 11:08 ` Re: Prefetch the next tuple's memory during seqscans Andy Fan <[email protected]>
@ 2022-11-01 11:50 ` David Rowley <[email protected]>
1 sibling, 0 replies; 24+ messages in thread
From: David Rowley @ 2022-11-01 11:50 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Wed, 2 Nov 2022 at 00:09, Andy Fan <[email protected]> wrote:
> I just have a different platforms at hand, Here is my test with
> Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz.
> shared_buffers has been set to big enough to hold all the data.
Many thanks for testing that. Those numbers look much better than the
ones I got from my AMD machine.
> By theory, Why does the preferch make thing better? I am asking this
> because I think we need to read the data from buffer to cache line once
> in either case (I'm obvious wrong in face of the test result.)
That's a good question. I didn't really explain that in my email.
There's quite a bit of information in [1]. My basic understanding is
that many modern CPU architectures are ok at "Sequential Prefetching"
of cache lines from main memory when the direction is forward, but I
believe that they're not very good at detecting access patterns that
are scanning memory addresses in a backwards direction.
Because of our page layout, we have the page header followed by item
pointers at the start of the page. These item pointers are fixed with
and point to the tuples, which are variable width. Tuples are written
starting at the end of the page. The page is full when the tuples
would overlap with the item pointers. See diagrams in [2].
We do our best to keep those tuples in reverse order of the item
pointer array. This means when we're performing a forward sequence
scan, we're (generally) reading tuples starting at the end of the page
and working backwards. Since the CPU is not very good at noticing
this and prefetching the preceding cacheline, we can make things go
faster (seemingly) by issuing a manual prefetch operation by way of
pg_prefetch_mem().
The key here is that accessing RAM is far slower than accessing CPU
caches. Modern CPUs can perform multiple operations in parallel and
these can be rearranged by the CPU so they're not in the same order as
the instructions are written in the programme. It's possible that
high latency operations such as accessing RAM could hold up other
operations which depend on the value of what's waiting to come in from
RAM. If the CPU is held up like this, it's called a pipeline stall
[3]. The prefetching in this case is helping to reduce the time spent
stalled waiting for memory access.
David
[1] https://en.wikipedia.org/wiki/Cache_prefetching
I might not do the explanation justice, but I believe many CPU archate
[2] https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/speeding-up-recovery-and-vacuum...
[3] https://en.wikipedia.org/wiki/Pipeline_stall
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-11-03 09:09 ` John Naylor <[email protected]>
2022-11-22 22:00 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: John Naylor @ 2022-11-03 09:09 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Tue, Nov 1, 2022 at 5:17 AM David Rowley <[email protected]> wrote:
>
> My test is to run 16 queries changing the WHERE clause each time to
> have WHERE a = 0, then WHERE a2 = 0 ... WHERE a16 = 0. I wanted to
> know if prefetching only the first cache line of the tuple would be
> less useful when we require evaluation of say, the "a16" column vs the
> "a" column.
I tried a similar test, but with text fields of random length, and there is
improvement here:
Intel laptop, turbo boost off
shared_buffers = '4GB'
huge_pages = 'on'
max_parallel_workers_per_gather = '0'
create table text8 as
select
repeat('X', int4(random() * 20)) a1,
repeat('X', int4(random() * 20)) a2,
repeat('X', int4(random() * 20)) a3,
repeat('X', int4(random() * 20)) a4,
repeat('X', int4(random() * 20)) a5,
repeat('X', int4(random() * 20)) a6,
repeat('X', int4(random() * 20)) a7,
repeat('X', int4(random() * 20)) a8
from generate_series(1,10000000) a;
vacuum freeze text8;
psql -c "select pg_prewarm('text8')" && \
for i in a1 a2 a3 a4 a5 a6 a7 a8;
do
echo Testing $i
echo "select * from text8 where $i = 'ZZZ';" > bench.sql
pgbench -f bench.sql -M prepared -n -T 10 postgres | grep latency
done
Master:
Testing a1
latency average = 980.595 ms
Testing a2
latency average = 1045.081 ms
Testing a3
latency average = 1107.736 ms
Testing a4
latency average = 1162.188 ms
Testing a5
latency average = 1213.985 ms
Testing a6
latency average = 1272.156 ms
Testing a7
latency average = 1318.281 ms
Testing a8
latency average = 1363.359 ms
Patch 0001+0003:
Testing a1
latency average = 812.548 ms
Testing a2
latency average = 897.303 ms
Testing a3
latency average = 955.997 ms
Testing a4
latency average = 1023.497 ms
Testing a5
latency average = 1088.494 ms
Testing a6
latency average = 1149.418 ms
Testing a7
latency average = 1213.134 ms
Testing a8
latency average = 1282.760 ms
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-03 09:09 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
@ 2022-11-22 22:00 ` David Rowley <[email protected]>
2022-11-23 01:35 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: David Rowley @ 2022-11-22 22:00 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Thu, 3 Nov 2022 at 22:09, John Naylor <[email protected]> wrote:
> I tried a similar test, but with text fields of random length, and there is improvement here:
Thank you for testing that. Can you share which CPU this was on?
My tests were all on AMD Zen 2. I'm keen to see what the results are
on intel hardware.
David
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-03 09:09 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
2022-11-22 22:00 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-11-23 01:35 ` John Naylor <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: John Naylor @ 2022-11-23 01:35 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Wed, Nov 23, 2022 at 5:00 AM David Rowley <[email protected]> wrote:
>
> On Thu, 3 Nov 2022 at 22:09, John Naylor <[email protected]>
wrote:
> > I tried a similar test, but with text fields of random length, and
there is improvement here:
>
> Thank you for testing that. Can you share which CPU this was on?
That was an Intel Core i7-10750H
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-11-02 03:00 ` Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Andres Freund @ 2022-11-02 03:00 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
Hi,
On 2022-10-31 16:52:52 +1300, David Rowley wrote:
> As part of the AIO work [1], Andres mentioned to me that he found that
> prefetching tuple memory during hot pruning showed significant wins.
> I'm not proposing anything to improve HOT pruning here
I did try and reproduce my old results, and it does look like we already get
most of the gains from prefetching via 18b87b201f7. I see gains from
prefetching before that patch, but see it hurt after. If I reverse the
iteration order from 18b87b201f7 prefetching helps again.
> but as a segue to get the prefetching infrastructure in so that there are
> fewer AIO patches, I'm proposing we prefetch the next tuple during sequence
> scans in while page mode.
> Time: 328.225 ms (avg ~7.7% faster)
> ...
> Time: 410.843 ms (avg ~22% faster)
That's a pretty impressive result.
I suspect that prefetching in heapgetpage() would provide gains as well, at
least for pages that aren't marked all-visible, pretty common in the real
world IME.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
@ 2022-11-02 17:25 ` Andres Freund <[email protected]>
2022-11-02 20:53 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Andres Freund @ 2022-11-02 17:25 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
Hi,
On 2022-11-01 20:00:43 -0700, Andres Freund wrote:
> I suspect that prefetching in heapgetpage() would provide gains as well, at
> least for pages that aren't marked all-visible, pretty common in the real
> world IME.
Attached is an experimental patch/hack for that. It ended up being more
beneficial to make the access ordering more optimal than prefetching the tuple
contents, but I'm not at all sure that's the be-all-end-all.
I separately benchmarked pinning the CPU and memory to the same socket,
different socket and interleaving memory.
I did this for HEAD, your patch, your patch and mine.
BEGIN; DROP TABLE IF EXISTS large; CREATE TABLE large(a int8 not null, b int8 not null default '0', c int8); INSERT INTO large SELECT generate_series(1, 50000000);COMMIT;
server is started with
local: numactl --membind 1 --physcpubind 10
remote: numactl --membind 0 --physcpubind 10
interleave: numactl --interleave=all --physcpubind 10
benchmark stared with:
psql -qX -f ~/tmp/prewarm.sql && \
pgbench -n -f ~/tmp/seqbench.sql -t 1 -r > /dev/null && \
perf stat -e task-clock,LLC-loads,LLC-load-misses,cycles,instructions -C
10 \
pgbench -n -f ~/tmp/seqbench.sql -t 3 -r
seqbench.sql:
SELECT count(*) FROM large WHERE c IS NOT NULL;
SELECT sum(a), sum(b), sum(c) FROM large;
SELECT sum(c) FROM large;
branch memory time s miss %
head local 31.612 74.03
david local 32.034 73.54
david+andres local 31.644 42.80
andres local 30.863 48.05
head remote 33.350 72.12
david remote 33.425 71.30
david+andres remote 32.428 49.57
andres remote 30.907 44.33
head interleave 32.465 71.33
david interleave 33.176 72.60
david+andres interleave 32.590 46.23
andres interleave 30.440 45.13
It's cool seeing how doing optimizing heapgetpage seems to pretty much remove
the performance difference between local / remote memory.
It makes some sense that David's patch doesn't help in this case - without
all-visible being set the tuple headers will have already been pulled in for
the HTSV call.
I've not yet experimented with moving the prefetch for the tuple contents from
David's location to before the HTSV. I suspect that might benefit both
workloads.
Greetings,
Andres Freund
Attachments:
[text/x-diff] prefetch-heapgetpage.diff (5.0K, ../../[email protected]/2-prefetch-heapgetpage.diff)
download | inline diff:
diff --git i/src/include/access/heapam.h w/src/include/access/heapam.h
index 9dab35551e1..dff7616abeb 100644
--- i/src/include/access/heapam.h
+++ w/src/include/access/heapam.h
@@ -74,7 +74,8 @@ typedef struct HeapScanDescData
/* these fields only used in page-at-a-time mode and for bitmap scans */
int rs_cindex; /* current tuple's index in vistuples */
int rs_ntuples; /* number of visible tuples on page */
- OffsetNumber rs_vistuples[MaxHeapTuplesPerPage]; /* their offsets */
+ OffsetNumber *rs_vistuples;
+ OffsetNumber rs_vistuples_d[MaxHeapTuplesPerPage]; /* their offsets */
} HeapScanDescData;
typedef struct HeapScanDescData *HeapScanDesc;
diff --git i/src/backend/access/heap/heapam.c w/src/backend/access/heap/heapam.c
index 12be87efed4..632f315f4e1 100644
--- i/src/backend/access/heap/heapam.c
+++ w/src/backend/access/heap/heapam.c
@@ -448,30 +448,99 @@ heapgetpage(TableScanDesc sscan, BlockNumber page)
*/
all_visible = PageIsAllVisible(dp) && !snapshot->takenDuringRecovery;
- for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(dp, lineoff);
- lineoff <= lines;
- lineoff++, lpp++)
+ if (all_visible)
{
- if (ItemIdIsNormal(lpp))
- {
- HeapTupleData loctup;
- bool valid;
+ HeapTupleData loctup;
+
+ loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
+
+ scan->rs_vistuples = scan->rs_vistuples_d;
+
+ for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(dp, lineoff);
+ lineoff <= lines;
+ lineoff++, lpp++)
+ {
+ if (!ItemIdIsNormal(lpp))
+ continue;
- loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
loctup.t_data = (HeapTupleHeader) PageGetItem((Page) dp, lpp);
loctup.t_len = ItemIdGetLength(lpp);
ItemPointerSet(&(loctup.t_self), page, lineoff);
- if (all_visible)
- valid = true;
- else
- valid = HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);
+ HeapCheckForSerializableConflictOut(true, scan->rs_base.rs_rd,
+ &loctup, buffer, snapshot);
+ scan->rs_vistuples[ntup++] = lineoff;
+ }
+ }
+ else
+ {
+ HeapTupleData loctup;
+ int normcount = 0;
+ OffsetNumber normoffsets[MaxHeapTuplesPerPage];
+
+ loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
+
+ for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(dp, lineoff);
+ lineoff <= lines;
+ lineoff++, lpp++)
+
+ /*
+ * Iterate forward over line items, they're laid out in increasing
+ * order in memory. Doing this separately allows to benefit from
+ * out-of-order capabilities of the CPU and simplifies the next loop.
+ *
+ * FIXME: Worth unrolling so that we don't fetch the same cacheline
+ * over and over, due to line items being smaller than a cacheline?
+ */
+ for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(dp, lineoff);
+ lineoff <= lines;
+ lineoff++, lpp++)
+ {
+ pg_prefetch_mem(PageGetItemId(dp, lineoff+5));
+ if (!ItemIdIsNormal(lpp))
+ continue;
+ normoffsets[normcount++] = lineoff;
+ }
+
+ /*
+ * Process tuples in reverse order. That'll most often lead to memory
+ * accesses in increasing order, which typically is more efficient for
+ * the CPUs prefetcher. To avoid affecting sort order, we store the
+ * visible tuples in decreasing order in rs_vistuples_d and then set
+ * rs_vistuple to the last tuple found.
+ *
+ * FIXME: We should likely compute rs_cindex in a smarter way, rather
+ * than changing rs_vistuples.
+ */
+ scan->rs_vistuples = scan->rs_vistuples_d + (MaxHeapTuplesPerPage);
+ for (int i = normcount - 1; i >= 0; i--)
+ {
+ bool valid;
+
+ /* doesn't appear to be beneficial */
+#if 0
+ if (i > 0)
+ pg_prefetch_mem(PageGetItem(dp, PageGetItemId(dp, normoffsets[i - 1])));
+#endif
+
+ lineoff = normoffsets[i];
+ lpp = PageGetItemId(dp, lineoff);
+
+ loctup.t_data = (HeapTupleHeader) PageGetItem((Page) dp, lpp);
+ loctup.t_len = ItemIdGetLength(lpp);
+ ItemPointerSet(&(loctup.t_self), page, lineoff);
+ valid = HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);
HeapCheckForSerializableConflictOut(valid, scan->rs_base.rs_rd,
&loctup, buffer, snapshot);
if (valid)
- scan->rs_vistuples[ntup++] = lineoff;
+ {
+ scan->rs_vistuples--;
+ *scan->rs_vistuples = lineoff;
+ ntup++;
+ }
+
}
}
diff --git i/src/backend/access/heap/heapam_handler.c w/src/backend/access/heap/heapam_handler.c
index 41f1ca65d01..f2876ecbc60 100644
--- i/src/backend/access/heap/heapam_handler.c
+++ w/src/backend/access/heap/heapam_handler.c
@@ -2162,6 +2162,8 @@ heapam_scan_bitmap_next_block(TableScanDesc scan,
*/
int curslot;
+ hscan->rs_vistuples = hscan->rs_vistuples_d;
+
for (curslot = 0; curslot < tbmres->ntuples; curslot++)
{
OffsetNumber offnum = tbmres->offsets[curslot];
@@ -2184,6 +2186,8 @@ heapam_scan_bitmap_next_block(TableScanDesc scan,
OffsetNumber maxoff = PageGetMaxOffsetNumber(dp);
OffsetNumber offnum;
+ hscan->rs_vistuples = hscan->rs_vistuples_d;
+
for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
{
ItemId lp;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
@ 2022-11-02 20:53 ` Andres Freund <[email protected]>
1 sibling, 0 replies; 24+ messages in thread
From: Andres Freund @ 2022-11-02 20:53 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
Hi,
On 2022-11-02 10:25:44 -0700, Andres Freund wrote:
> server is started with
> local: numactl --membind 1 --physcpubind 10
> remote: numactl --membind 0 --physcpubind 10
> interleave: numactl --interleave=all --physcpubind 10
Argh, forgot to say that this is with max_parallel_workers_per_gather=0,
s_b=8GB, huge_pages=on.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
@ 2022-11-22 21:58 ` David Rowley <[email protected]>
2022-11-23 07:29 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
2022-11-24 09:25 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-12-01 05:17 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
1 sibling, 3 replies; 24+ messages in thread
From: David Rowley @ 2022-11-22 21:58 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Thu, 3 Nov 2022 at 06:25, Andres Freund <[email protected]> wrote:
> Attached is an experimental patch/hack for that. It ended up being more
> beneficial to make the access ordering more optimal than prefetching the tuple
> contents, but I'm not at all sure that's the be-all-end-all.
Thanks for writing that patch. I've been experimenting with it.
I tried unrolling the loop (patch 0003) as you mentioned in:
+ * FIXME: Worth unrolling so that we don't fetch the same cacheline
+ * over and over, due to line items being smaller than a cacheline?
but didn't see any gains from doing that.
I also adjusted your patch a little so that instead of doing:
- OffsetNumber rs_vistuples[MaxHeapTuplesPerPage]; /* their offsets */
+ OffsetNumber *rs_vistuples;
+ OffsetNumber rs_vistuples_d[MaxHeapTuplesPerPage]; /* their offsets */
to work around the issue of having to populate rs_vistuples_d in
reverse, I added a new field called rs_startindex to mark where the
first element in the rs_vistuples array is. The way you wrote it seems
to require fewer code changes, but per the FIXME comment you left, I
get the idea you just did it the way you did to make it work enough
for testing.
I'm quite keen to move forward in committing the 0001 patch to add the
pg_prefetch_mem macro. What I'm a little undecided about is what the
best patch is to commit first to make use of the new macro.
I did some tests on the attached set of patches:
alter system set max_parallel_workers_per_gather = 0;
select pg_reload_conf();
create table t as select a from generate_series(1,10000000)a;
alter table t set (autovacuum_enabled=false);
$ cat bench.sql
select * from t where a = 0;
psql -c "select pg_prewarm('t');" postgres
-- Test 1 no frozen tuples in "t"
Master (@9c6ad5eaa):
$ pgbench -n -f bench.sql -M prepared -T 10 postgres | grep -E "^latency"
latency average = 383.332 ms
latency average = 375.747 ms
latency average = 376.090 ms
Master + 0001 + 0002:
$ pgbench -n -f bench.sql -M prepared -T 10 postgres | grep -E "^latency"
latency average = 370.133 ms
latency average = 370.149 ms
latency average = 370.157 ms
Master + 0001 + 0005:
$ pgbench -n -f bench.sql -M prepared -T 10 postgres | grep -E "^latency"
latency average = 372.662 ms
latency average = 371.034 ms
latency average = 372.709 ms
-- Test 2 "select count(*) from t" with all tuples frozen
$ cat bench1.sql
select count(*) from t;
psql -c "vacuum freeze t;" postgres
psql -c "select pg_prewarm('t');" postgres
Master (@9c6ad5eaa):
$ pgbench -n -f bench1.sql -M prepared -T 10 postgres | grep -E "^latency"
latency average = 406.238 ms
latency average = 407.029 ms
latency average = 406.962 ms
Master + 0001 + 0005:
$ pgbench -n -f bench1.sql -M prepared -T 10 postgres | grep -E "^latency"
latency average = 345.470 ms
latency average = 345.775 ms
latency average = 345.354 ms
My current thoughts are that it might be best to go with 0005 to start
with. I know Melanie is working on making some changes in this area,
so perhaps it's best to leave 0002 until that work is complete.
David
From 491df9d6ab87a54bbc76b876484733d02d6c94ea Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Wed, 19 Oct 2022 08:54:01 +1300
Subject: [PATCH v2 1/5] Add pg_prefetch_mem() macro to load cache lines.
Initially mapping to GCC, Clang and MSVC builtins.
Discussion: https://postgr.es/m/CAEepm%3D2y9HM9QP%2BHhRZdQ3pU6FShSMyu%3DV1uHXhQ5gG-dketHg%40mail.gmail.com
---
config/c-compiler.m4 | 17 ++++++++++++++++
configure | 40 ++++++++++++++++++++++++++++++++++++++
configure.ac | 3 +++
meson.build | 3 ++-
src/include/c.h | 8 ++++++++
src/include/pg_config.h.in | 3 +++
src/tools/msvc/Solution.pm | 1 +
7 files changed, 74 insertions(+), 1 deletion(-)
diff --git a/config/c-compiler.m4 b/config/c-compiler.m4
index 000b075312..582a47501c 100644
--- a/config/c-compiler.m4
+++ b/config/c-compiler.m4
@@ -355,6 +355,23 @@ AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
[Define to 1 if your compiler understands $1.])
fi])# PGAC_CHECK_BUILTIN_FUNC
+# PGAC_CHECK_BUILTIN_VOID_FUNC
+# -----------------------
+# Variant for void functions.
+AC_DEFUN([PGAC_CHECK_BUILTIN_VOID_FUNC],
+[AC_CACHE_CHECK(for $1, pgac_cv$1,
+[AC_LINK_IFELSE([AC_LANG_PROGRAM([
+void
+call$1($2)
+{
+ $1(x);
+}], [])],
+[pgac_cv$1=yes],
+[pgac_cv$1=no])])
+if test x"${pgac_cv$1}" = xyes ; then
+AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
+ [Define to 1 if your compiler understands $1.])
+fi])# PGAC_CHECK_BUILTIN_VOID_FUNC
# PGAC_CHECK_BUILTIN_FUNC_PTR
diff --git a/configure b/configure
index 3966368b8d..c4685b8a1e 100755
--- a/configure
+++ b/configure
@@ -15988,6 +15988,46 @@ _ACEOF
fi
+# Can we use a built-in to prefetch memory?
+{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for __builtin_prefetch" >&5
+$as_echo_n "checking for __builtin_prefetch... " >&6; }
+if ${pgac_cv__builtin_prefetch+:} false; then :
+ $as_echo_n "(cached) " >&6
+else
+ cat confdefs.h - <<_ACEOF >conftest.$ac_ext
+/* end confdefs.h. */
+
+void
+call__builtin_prefetch(void *x)
+{
+ __builtin_prefetch(x);
+}
+int
+main ()
+{
+
+ ;
+ return 0;
+}
+_ACEOF
+if ac_fn_c_try_link "$LINENO"; then :
+ pgac_cv__builtin_prefetch=yes
+else
+ pgac_cv__builtin_prefetch=no
+fi
+rm -f core conftest.err conftest.$ac_objext \
+ conftest$ac_exeext conftest.$ac_ext
+fi
+{ $as_echo "$as_me:${as_lineno-$LINENO}: result: $pgac_cv__builtin_prefetch" >&5
+$as_echo "$pgac_cv__builtin_prefetch" >&6; }
+if test x"${pgac_cv__builtin_prefetch}" = xyes ; then
+
+cat >>confdefs.h <<_ACEOF
+#define HAVE__BUILTIN_PREFETCH 1
+_ACEOF
+
+fi
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for _LARGEFILE_SOURCE value needed for large files" >&5
diff --git a/configure.ac b/configure.ac
index f76b7ee31f..2d4938d43d 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1802,6 +1802,9 @@ PGAC_CHECK_BUILTIN_FUNC([__builtin_popcount], [unsigned int x])
# so it needs a different test function.
PGAC_CHECK_BUILTIN_FUNC_PTR([__builtin_frame_address], [0])
+# Can we use a built-in to prefetch memory?
+PGAC_CHECK_BUILTIN_VOID_FUNC([__builtin_prefetch], [void *x])
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
AC_FUNC_FSEEKO
diff --git a/meson.build b/meson.build
index 058382046e..096643703c 100644
--- a/meson.build
+++ b/meson.build
@@ -1587,10 +1587,11 @@ builtins = [
'bswap32',
'bswap64',
'clz',
- 'ctz',
'constant_p',
+ 'ctz',
'frame_address',
'popcount',
+ 'prefetch',
'unreachable',
]
diff --git a/src/include/c.h b/src/include/c.h
index 98cdd285dd..a7f7531450 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -361,6 +361,14 @@ typedef void (*pg_funcptr_t) (void);
*/
#define FLEXIBLE_ARRAY_MEMBER /* empty */
+/* Do we have support for prefetching memory? */
+#if defined(HAVE__BUILTIN_PREFETCH)
+#define pg_prefetch_mem(a) __builtin_prefetch(a)
+#elif defined(_MSC_VER)
+#define pg_prefetch_mem(a) _m_prefetch(a)
+#else
+#define pg_prefetch_mem(a)
+#endif
/* ----------------------------------------------------------------
* Section 2: bool, true, false
diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in
index c5a80b829e..07a661e288 100644
--- a/src/include/pg_config.h.in
+++ b/src/include/pg_config.h.in
@@ -559,6 +559,9 @@
/* Define to 1 if your compiler understands __builtin_popcount. */
#undef HAVE__BUILTIN_POPCOUNT
+/* Define to 1 if your compiler understands __builtin_prefetch. */
+#undef HAVE__BUILTIN_PREFETCH
+
/* Define to 1 if your compiler understands __builtin_types_compatible_p. */
#undef HAVE__BUILTIN_TYPES_COMPATIBLE_P
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index c2acb58df0..95de91890e 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -227,6 +227,7 @@ sub GenerateFiles
HAVE_BACKTRACE_SYMBOLS => undef,
HAVE_BIO_GET_DATA => undef,
HAVE_BIO_METH_NEW => undef,
+ HAVE__BUILTIN_PREFETCH => undef,
HAVE_COMPUTED_GOTO => undef,
HAVE_COPYFILE => undef,
HAVE_COPYFILE_H => undef,
--
2.35.1.windows.2
From c5ec896a2df02041f08d1e41a982223781137d5f Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Thu, 17 Nov 2022 13:04:49 +1300
Subject: [PATCH v2 2/5] Perform memory prefetching in heapgetpage
---
src/backend/access/heap/heapam.c | 87 +++++++++++++++++++-----
src/backend/access/heap/heapam_handler.c | 7 +-
src/include/access/heapam.h | 1 +
3 files changed, 74 insertions(+), 21 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index d18c5ca6f5..81c7f69644 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -451,31 +451,82 @@ heapgetpage(TableScanDesc sscan, BlockNumber block)
*/
all_visible = PageIsAllVisible(page) && !snapshot->takenDuringRecovery;
- for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
- lineoff <= lines;
- lineoff++, lpp++)
+ if (all_visible)
{
- if (ItemIdIsNormal(lpp))
+ HeapTupleData loctup;
+
+ loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
+
+ for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
+ lineoff <= lines;
+ lineoff++, lpp++)
{
- HeapTupleData loctup;
- bool valid;
+ if (!ItemIdIsNormal(lpp))
+ continue;
- loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
loctup.t_data = (HeapTupleHeader) PageGetItem(page, lpp);
loctup.t_len = ItemIdGetLength(lpp);
ItemPointerSet(&(loctup.t_self), block, lineoff);
- if (all_visible)
- valid = true;
- else
- valid = HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);
+ HeapCheckForSerializableConflictOut(true, scan->rs_base.rs_rd,
+ &loctup, buffer, snapshot);
+ scan->rs_vistuples[ntup++] = lineoff;
+ }
+ scan->rs_startindex = 0;
+ }
+ else
+ {
+ HeapTupleData loctup;
+ int normcount = 0;
+ int startindex = MaxHeapTuplesPerPage;
+ OffsetNumber normoffsets[MaxHeapTuplesPerPage];
+
+ loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
+
+ /*
+ * Iterate forward over line items, they're laid out in increasing
+ * order in memory. Doing this separately allows to benefit from
+ * out-of-order capabilities of the CPU and simplifies the next loop.
+ */
+ for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
+ lineoff <= lines;
+ lineoff++, lpp++)
+ {
+ pg_prefetch_mem(PageGetItemId(page, lineoff+5));
+ if (ItemIdIsNormal(lpp))
+ normoffsets[normcount++] = lineoff;
+ }
+
+ /*
+ * Process tuples in reverse order. That'll most often lead to memory
+ * accesses in increasing order, which typically is more efficient for
+ * the CPUs prefetcher. To avoid affecting sort order, we populate the
+ * rs_vistuples[] array backwards and use rs_startindex to mark the
+ * first used element in the array.
+ */
+ for (int i = normcount - 1; i >= 0; i--)
+ {
+ bool valid;
+
+ lineoff = normoffsets[i];
+ lpp = PageGetItemId(page, lineoff);
+
+ loctup.t_data = (HeapTupleHeader) PageGetItem(page, lpp);
+ loctup.t_len = ItemIdGetLength(lpp);
+ ItemPointerSet(&(loctup.t_self), block, lineoff);
+ valid = HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);
HeapCheckForSerializableConflictOut(valid, scan->rs_base.rs_rd,
&loctup, buffer, snapshot);
if (valid)
- scan->rs_vistuples[ntup++] = lineoff;
+ {
+ scan->rs_vistuples[--startindex] = lineoff;
+ ntup++;
+ }
}
+ /* record the first used element in rs_vistuples[] */
+ scan->rs_startindex = startindex;
}
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
@@ -902,7 +953,7 @@ heapgettup_pagemode(HeapScanDesc scan,
else
block = scan->rs_startblock; /* first page */
heapgetpage((TableScanDesc) scan, block);
- lineindex = 0;
+ lineindex = scan->rs_startindex;
scan->rs_inited = true;
}
else
@@ -917,7 +968,7 @@ heapgettup_pagemode(HeapScanDesc scan,
lines = scan->rs_ntuples;
/* block and lineindex now reference the next visible tid */
- linesleft = lines - lineindex;
+ linesleft = lines - lineindex + scan->rs_startindex;
}
else if (backward)
{
@@ -968,7 +1019,7 @@ heapgettup_pagemode(HeapScanDesc scan,
if (!scan->rs_inited)
{
- lineindex = lines - 1;
+ lineindex = scan->rs_startindex + lines - 1;
scan->rs_inited = true;
}
else
@@ -977,7 +1028,7 @@ heapgettup_pagemode(HeapScanDesc scan,
}
/* block and lineindex now reference the previous visible tid */
- linesleft = lineindex + 1;
+ linesleft = lineindex + 1 - scan->rs_startindex;
}
else
{
@@ -1127,9 +1178,9 @@ heapgettup_pagemode(HeapScanDesc scan,
lines = scan->rs_ntuples;
linesleft = lines;
if (backward)
- lineindex = lines - 1;
+ lineindex = MaxHeapTuplesPerPage - 1;
else
- lineindex = 0;
+ lineindex = scan->rs_startindex;
}
}
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index ab1bcf3522..d39284465b 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -2490,15 +2490,16 @@ SampleHeapTupleVisible(TableScanDesc scan, Buffer buffer,
{
/*
* In pageatatime mode, heapgetpage() already did visibility checks,
- * so just look at the info it left in rs_vistuples[].
+ * so just look at the info it left in rs_vistuples[] starting at
+ * rs_startindex.
*
* We use a binary search over the known-sorted array. Note: we could
* save some effort if we insisted that NextSampleTuple select tuples
* in increasing order, but it's not clear that there would be enough
* gain to justify the restriction.
*/
- int start = 0,
- end = hscan->rs_ntuples - 1;
+ int start = hscan->rs_startindex,
+ end = hscan->rs_startindex + hscan->rs_ntuples - 1;
while (start <= end)
{
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index 810baaf9d0..aba0795fc6 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -74,6 +74,7 @@ typedef struct HeapScanDescData
/* these fields only used in page-at-a-time mode and for bitmap scans */
int rs_cindex; /* current tuple's index in vistuples */
int rs_ntuples; /* number of visible tuples on page */
+ int rs_startindex; /* first used element in rs_vistuples[] */
OffsetNumber rs_vistuples[MaxHeapTuplesPerPage]; /* their offsets */
} HeapScanDescData;
typedef struct HeapScanDescData *HeapScanDesc;
--
2.35.1.windows.2
From f0b8920b23b200132a4e92942c4bb281426e4f9c Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Mon, 31 Oct 2022 10:05:12 +1300
Subject: [PATCH v2 5/5] Prefetch tuple memory during forward seqscans
---
src/backend/access/heap/heapam.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 8fe4f4c837..6dc53effb4 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1115,6 +1115,17 @@ heapgettup_pagemode(HeapScanDesc scan,
tuple->t_len = ItemIdGetLength(lpp);
ItemPointerSet(&(tuple->t_self), block, lineoff);
+ /*
+ * Prefetching the memory for the next tuple has shown to improve
+ * performance on certain hardware.
+ */
+ if (!backward && linesleft > 1)
+ {
+ lineoff = scan->rs_vistuples[lineindex + 1];
+ lpp = PageGetItemId(page, lineoff);
+ pg_prefetch_mem(PageGetItem(page, lpp));
+ }
+
/*
* if current tuple qualifies, return it.
*/
--
2.35.1.windows.2
From 8f47b1e0163028ec8928aaf02ce49d34bb89c647 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Wed, 19 Oct 2022 08:55:45 +1300
Subject: [PATCH v2 4/5] heapam: WIP: cacheline prefetching for hot pruning.
Author: Andres Freund, with contributions from Dmitry Dolgov
---
src/backend/access/heap/pruneheap.c | 24 ++++++++++++++++++++++++
1 file changed, 24 insertions(+)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 91c5f5e9ef..a094ac18f5 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -302,6 +302,30 @@ heap_page_prune(Relation relation, Buffer buffer,
maxoff = PageGetMaxOffsetNumber(page);
tup.t_tableOid = RelationGetRelid(prstate.rel);
+#if 1
+ for (char *p = (char *) PageGetItemId(page, FirstOffsetNumber);
+ p < (char *) PageGetItemId(page, maxoff);
+ p += 64)
+ {
+ pg_prefetch_mem((ItemId)p);
+ }
+
+
+ for (offnum = FirstOffsetNumber;
+ offnum <= maxoff;
+ offnum = OffsetNumberNext(offnum))
+ {
+ ItemId itemid;
+
+ itemid = PageGetItemId(page, offnum);
+ if (!ItemIdIsUsed(itemid) || ItemIdIsDead(itemid) || !ItemIdHasStorage(itemid))
+ continue;
+
+ pg_prefetch_mem((HeapTupleHeader) PageGetItem(page, itemid));
+ pg_prefetch_mem(PageGetItem(page, itemid) + sizeof(HeapTupleHeaderData) - 1);
+ }
+#endif
+
/*
* Determine HTSV for all tuples.
*
--
2.35.1.windows.2
From f1b183123befc1a3f096ba36b1c3834b4c67d3ec Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Thu, 17 Nov 2022 13:24:40 +1300
Subject: [PATCH v2 3/5] Unroll loop in heapgetpage
---
src/backend/access/heap/heapam.c | 48 ++++++++++++++++++++++++++++----
1 file changed, 42 insertions(+), 6 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 81c7f69644..8fe4f4c837 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -484,15 +484,51 @@ heapgetpage(TableScanDesc sscan, BlockNumber block)
loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
/*
- * Iterate forward over line items, they're laid out in increasing
- * order in memory. Doing this separately allows to benefit from
- * out-of-order capabilities of the CPU and simplifies the next loop.
+ * Iterate forward over line items processing 16 at a time (this
+ * assumes there will be 16 ItemIds per CPU cacheline.
*/
for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
- lineoff <= lines;
- lineoff++, lpp++)
+ lineoff <= lines - 15;
+ lineoff += 16, lpp += 16)
+ {
+ pg_prefetch_mem(PageGetItemId(page, lineoff + 16));
+ if (ItemIdIsNormal(lpp))
+ normoffsets[normcount++] = lineoff;
+ if (ItemIdIsNormal(lpp + 1))
+ normoffsets[normcount++] = lineoff + 1;
+ if (ItemIdIsNormal(lpp + 2))
+ normoffsets[normcount++] = lineoff + 2;
+ if (ItemIdIsNormal(lpp + 3))
+ normoffsets[normcount++] = lineoff + 3;
+ if (ItemIdIsNormal(lpp + 4))
+ normoffsets[normcount++] = lineoff + 4;
+ if (ItemIdIsNormal(lpp + 5))
+ normoffsets[normcount++] = lineoff + 5;
+ if (ItemIdIsNormal(lpp + 6))
+ normoffsets[normcount++] = lineoff + 6;
+ if (ItemIdIsNormal(lpp + 7))
+ normoffsets[normcount++] = lineoff + 7;
+ if (ItemIdIsNormal(lpp + 8))
+ normoffsets[normcount++] = lineoff + 8;
+ if (ItemIdIsNormal(lpp + 9))
+ normoffsets[normcount++] = lineoff + 9;
+ if (ItemIdIsNormal(lpp + 10))
+ normoffsets[normcount++] = lineoff + 10;
+ if (ItemIdIsNormal(lpp + 11))
+ normoffsets[normcount++] = lineoff + 11;
+ if (ItemIdIsNormal(lpp + 12))
+ normoffsets[normcount++] = lineoff + 12;
+ if (ItemIdIsNormal(lpp + 13))
+ normoffsets[normcount++] = lineoff + 13;
+ if (ItemIdIsNormal(lpp + 14))
+ normoffsets[normcount++] = lineoff + 14;
+ if (ItemIdIsNormal(lpp + 15))
+ normoffsets[normcount++] = lineoff + 15;
+ }
+
+ /* get remainder */
+ for (; lineoff <= lines; lineoff++, lpp++)
{
- pg_prefetch_mem(PageGetItemId(page, lineoff+5));
if (ItemIdIsNormal(lpp))
normoffsets[normcount++] = lineoff;
}
--
2.35.1.windows.2
Attachments:
[text/plain] v2-0001-Add-pg_prefetch_mem-macro-to-load-cache-lines.patch (5.3K, ../../CAApHDvo1TL1Bm6AWnPwGYVya82TA3fuqRQsjvTjA+=NzHa6cuw@mail.gmail.com/2-v2-0001-Add-pg_prefetch_mem-macro-to-load-cache-lines.patch)
download | inline diff:
From 491df9d6ab87a54bbc76b876484733d02d6c94ea Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Wed, 19 Oct 2022 08:54:01 +1300
Subject: [PATCH v2 1/5] Add pg_prefetch_mem() macro to load cache lines.
Initially mapping to GCC, Clang and MSVC builtins.
Discussion: https://postgr.es/m/CAEepm%3D2y9HM9QP%2BHhRZdQ3pU6FShSMyu%3DV1uHXhQ5gG-dketHg%40mail.gmail.com
---
config/c-compiler.m4 | 17 ++++++++++++++++
configure | 40 ++++++++++++++++++++++++++++++++++++++
configure.ac | 3 +++
meson.build | 3 ++-
src/include/c.h | 8 ++++++++
src/include/pg_config.h.in | 3 +++
src/tools/msvc/Solution.pm | 1 +
7 files changed, 74 insertions(+), 1 deletion(-)
diff --git a/config/c-compiler.m4 b/config/c-compiler.m4
index 000b075312..582a47501c 100644
--- a/config/c-compiler.m4
+++ b/config/c-compiler.m4
@@ -355,6 +355,23 @@ AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
[Define to 1 if your compiler understands $1.])
fi])# PGAC_CHECK_BUILTIN_FUNC
+# PGAC_CHECK_BUILTIN_VOID_FUNC
+# -----------------------
+# Variant for void functions.
+AC_DEFUN([PGAC_CHECK_BUILTIN_VOID_FUNC],
+[AC_CACHE_CHECK(for $1, pgac_cv$1,
+[AC_LINK_IFELSE([AC_LANG_PROGRAM([
+void
+call$1($2)
+{
+ $1(x);
+}], [])],
+[pgac_cv$1=yes],
+[pgac_cv$1=no])])
+if test x"${pgac_cv$1}" = xyes ; then
+AC_DEFINE_UNQUOTED(AS_TR_CPP([HAVE$1]), 1,
+ [Define to 1 if your compiler understands $1.])
+fi])# PGAC_CHECK_BUILTIN_VOID_FUNC
# PGAC_CHECK_BUILTIN_FUNC_PTR
diff --git a/configure b/configure
index 3966368b8d..c4685b8a1e 100755
--- a/configure
+++ b/configure
@@ -15988,6 +15988,46 @@ _ACEOF
fi
+# Can we use a built-in to prefetch memory?
+{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for __builtin_prefetch" >&5
+$as_echo_n "checking for __builtin_prefetch... " >&6; }
+if ${pgac_cv__builtin_prefetch+:} false; then :
+ $as_echo_n "(cached) " >&6
+else
+ cat confdefs.h - <<_ACEOF >conftest.$ac_ext
+/* end confdefs.h. */
+
+void
+call__builtin_prefetch(void *x)
+{
+ __builtin_prefetch(x);
+}
+int
+main ()
+{
+
+ ;
+ return 0;
+}
+_ACEOF
+if ac_fn_c_try_link "$LINENO"; then :
+ pgac_cv__builtin_prefetch=yes
+else
+ pgac_cv__builtin_prefetch=no
+fi
+rm -f core conftest.err conftest.$ac_objext \
+ conftest$ac_exeext conftest.$ac_ext
+fi
+{ $as_echo "$as_me:${as_lineno-$LINENO}: result: $pgac_cv__builtin_prefetch" >&5
+$as_echo "$pgac_cv__builtin_prefetch" >&6; }
+if test x"${pgac_cv__builtin_prefetch}" = xyes ; then
+
+cat >>confdefs.h <<_ACEOF
+#define HAVE__BUILTIN_PREFETCH 1
+_ACEOF
+
+fi
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for _LARGEFILE_SOURCE value needed for large files" >&5
diff --git a/configure.ac b/configure.ac
index f76b7ee31f..2d4938d43d 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1802,6 +1802,9 @@ PGAC_CHECK_BUILTIN_FUNC([__builtin_popcount], [unsigned int x])
# so it needs a different test function.
PGAC_CHECK_BUILTIN_FUNC_PTR([__builtin_frame_address], [0])
+# Can we use a built-in to prefetch memory?
+PGAC_CHECK_BUILTIN_VOID_FUNC([__builtin_prefetch], [void *x])
+
# We require 64-bit fseeko() to be available, but run this check anyway
# in case it finds that _LARGEFILE_SOURCE has to be #define'd for that.
AC_FUNC_FSEEKO
diff --git a/meson.build b/meson.build
index 058382046e..096643703c 100644
--- a/meson.build
+++ b/meson.build
@@ -1587,10 +1587,11 @@ builtins = [
'bswap32',
'bswap64',
'clz',
- 'ctz',
'constant_p',
+ 'ctz',
'frame_address',
'popcount',
+ 'prefetch',
'unreachable',
]
diff --git a/src/include/c.h b/src/include/c.h
index 98cdd285dd..a7f7531450 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -361,6 +361,14 @@ typedef void (*pg_funcptr_t) (void);
*/
#define FLEXIBLE_ARRAY_MEMBER /* empty */
+/* Do we have support for prefetching memory? */
+#if defined(HAVE__BUILTIN_PREFETCH)
+#define pg_prefetch_mem(a) __builtin_prefetch(a)
+#elif defined(_MSC_VER)
+#define pg_prefetch_mem(a) _m_prefetch(a)
+#else
+#define pg_prefetch_mem(a)
+#endif
/* ----------------------------------------------------------------
* Section 2: bool, true, false
diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in
index c5a80b829e..07a661e288 100644
--- a/src/include/pg_config.h.in
+++ b/src/include/pg_config.h.in
@@ -559,6 +559,9 @@
/* Define to 1 if your compiler understands __builtin_popcount. */
#undef HAVE__BUILTIN_POPCOUNT
+/* Define to 1 if your compiler understands __builtin_prefetch. */
+#undef HAVE__BUILTIN_PREFETCH
+
/* Define to 1 if your compiler understands __builtin_types_compatible_p. */
#undef HAVE__BUILTIN_TYPES_COMPATIBLE_P
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index c2acb58df0..95de91890e 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -227,6 +227,7 @@ sub GenerateFiles
HAVE_BACKTRACE_SYMBOLS => undef,
HAVE_BIO_GET_DATA => undef,
HAVE_BIO_METH_NEW => undef,
+ HAVE__BUILTIN_PREFETCH => undef,
HAVE_COMPUTED_GOTO => undef,
HAVE_COPYFILE => undef,
HAVE_COPYFILE_H => undef,
--
2.35.1.windows.2
[text/plain] v2-0002-Perform-memory-prefetching-in-heapgetpage.patch (6.3K, ../../CAApHDvo1TL1Bm6AWnPwGYVya82TA3fuqRQsjvTjA+=NzHa6cuw@mail.gmail.com/3-v2-0002-Perform-memory-prefetching-in-heapgetpage.patch)
download | inline diff:
From c5ec896a2df02041f08d1e41a982223781137d5f Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Thu, 17 Nov 2022 13:04:49 +1300
Subject: [PATCH v2 2/5] Perform memory prefetching in heapgetpage
---
src/backend/access/heap/heapam.c | 87 +++++++++++++++++++-----
src/backend/access/heap/heapam_handler.c | 7 +-
src/include/access/heapam.h | 1 +
3 files changed, 74 insertions(+), 21 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index d18c5ca6f5..81c7f69644 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -451,31 +451,82 @@ heapgetpage(TableScanDesc sscan, BlockNumber block)
*/
all_visible = PageIsAllVisible(page) && !snapshot->takenDuringRecovery;
- for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
- lineoff <= lines;
- lineoff++, lpp++)
+ if (all_visible)
{
- if (ItemIdIsNormal(lpp))
+ HeapTupleData loctup;
+
+ loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
+
+ for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
+ lineoff <= lines;
+ lineoff++, lpp++)
{
- HeapTupleData loctup;
- bool valid;
+ if (!ItemIdIsNormal(lpp))
+ continue;
- loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
loctup.t_data = (HeapTupleHeader) PageGetItem(page, lpp);
loctup.t_len = ItemIdGetLength(lpp);
ItemPointerSet(&(loctup.t_self), block, lineoff);
- if (all_visible)
- valid = true;
- else
- valid = HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);
+ HeapCheckForSerializableConflictOut(true, scan->rs_base.rs_rd,
+ &loctup, buffer, snapshot);
+ scan->rs_vistuples[ntup++] = lineoff;
+ }
+ scan->rs_startindex = 0;
+ }
+ else
+ {
+ HeapTupleData loctup;
+ int normcount = 0;
+ int startindex = MaxHeapTuplesPerPage;
+ OffsetNumber normoffsets[MaxHeapTuplesPerPage];
+
+ loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
+
+ /*
+ * Iterate forward over line items, they're laid out in increasing
+ * order in memory. Doing this separately allows to benefit from
+ * out-of-order capabilities of the CPU and simplifies the next loop.
+ */
+ for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
+ lineoff <= lines;
+ lineoff++, lpp++)
+ {
+ pg_prefetch_mem(PageGetItemId(page, lineoff+5));
+ if (ItemIdIsNormal(lpp))
+ normoffsets[normcount++] = lineoff;
+ }
+
+ /*
+ * Process tuples in reverse order. That'll most often lead to memory
+ * accesses in increasing order, which typically is more efficient for
+ * the CPUs prefetcher. To avoid affecting sort order, we populate the
+ * rs_vistuples[] array backwards and use rs_startindex to mark the
+ * first used element in the array.
+ */
+ for (int i = normcount - 1; i >= 0; i--)
+ {
+ bool valid;
+
+ lineoff = normoffsets[i];
+ lpp = PageGetItemId(page, lineoff);
+
+ loctup.t_data = (HeapTupleHeader) PageGetItem(page, lpp);
+ loctup.t_len = ItemIdGetLength(lpp);
+ ItemPointerSet(&(loctup.t_self), block, lineoff);
+ valid = HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);
HeapCheckForSerializableConflictOut(valid, scan->rs_base.rs_rd,
&loctup, buffer, snapshot);
if (valid)
- scan->rs_vistuples[ntup++] = lineoff;
+ {
+ scan->rs_vistuples[--startindex] = lineoff;
+ ntup++;
+ }
}
+ /* record the first used element in rs_vistuples[] */
+ scan->rs_startindex = startindex;
}
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
@@ -902,7 +953,7 @@ heapgettup_pagemode(HeapScanDesc scan,
else
block = scan->rs_startblock; /* first page */
heapgetpage((TableScanDesc) scan, block);
- lineindex = 0;
+ lineindex = scan->rs_startindex;
scan->rs_inited = true;
}
else
@@ -917,7 +968,7 @@ heapgettup_pagemode(HeapScanDesc scan,
lines = scan->rs_ntuples;
/* block and lineindex now reference the next visible tid */
- linesleft = lines - lineindex;
+ linesleft = lines - lineindex + scan->rs_startindex;
}
else if (backward)
{
@@ -968,7 +1019,7 @@ heapgettup_pagemode(HeapScanDesc scan,
if (!scan->rs_inited)
{
- lineindex = lines - 1;
+ lineindex = scan->rs_startindex + lines - 1;
scan->rs_inited = true;
}
else
@@ -977,7 +1028,7 @@ heapgettup_pagemode(HeapScanDesc scan,
}
/* block and lineindex now reference the previous visible tid */
- linesleft = lineindex + 1;
+ linesleft = lineindex + 1 - scan->rs_startindex;
}
else
{
@@ -1127,9 +1178,9 @@ heapgettup_pagemode(HeapScanDesc scan,
lines = scan->rs_ntuples;
linesleft = lines;
if (backward)
- lineindex = lines - 1;
+ lineindex = MaxHeapTuplesPerPage - 1;
else
- lineindex = 0;
+ lineindex = scan->rs_startindex;
}
}
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index ab1bcf3522..d39284465b 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -2490,15 +2490,16 @@ SampleHeapTupleVisible(TableScanDesc scan, Buffer buffer,
{
/*
* In pageatatime mode, heapgetpage() already did visibility checks,
- * so just look at the info it left in rs_vistuples[].
+ * so just look at the info it left in rs_vistuples[] starting at
+ * rs_startindex.
*
* We use a binary search over the known-sorted array. Note: we could
* save some effort if we insisted that NextSampleTuple select tuples
* in increasing order, but it's not clear that there would be enough
* gain to justify the restriction.
*/
- int start = 0,
- end = hscan->rs_ntuples - 1;
+ int start = hscan->rs_startindex,
+ end = hscan->rs_startindex + hscan->rs_ntuples - 1;
while (start <= end)
{
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index 810baaf9d0..aba0795fc6 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -74,6 +74,7 @@ typedef struct HeapScanDescData
/* these fields only used in page-at-a-time mode and for bitmap scans */
int rs_cindex; /* current tuple's index in vistuples */
int rs_ntuples; /* number of visible tuples on page */
+ int rs_startindex; /* first used element in rs_vistuples[] */
OffsetNumber rs_vistuples[MaxHeapTuplesPerPage]; /* their offsets */
} HeapScanDescData;
typedef struct HeapScanDescData *HeapScanDesc;
--
2.35.1.windows.2
[text/plain] v2-0005-Prefetch-tuple-memory-during-forward-seqscans.patch (1.0K, ../../CAApHDvo1TL1Bm6AWnPwGYVya82TA3fuqRQsjvTjA+=NzHa6cuw@mail.gmail.com/4-v2-0005-Prefetch-tuple-memory-during-forward-seqscans.patch)
download | inline diff:
From f0b8920b23b200132a4e92942c4bb281426e4f9c Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Mon, 31 Oct 2022 10:05:12 +1300
Subject: [PATCH v2 5/5] Prefetch tuple memory during forward seqscans
---
src/backend/access/heap/heapam.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 8fe4f4c837..6dc53effb4 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1115,6 +1115,17 @@ heapgettup_pagemode(HeapScanDesc scan,
tuple->t_len = ItemIdGetLength(lpp);
ItemPointerSet(&(tuple->t_self), block, lineoff);
+ /*
+ * Prefetching the memory for the next tuple has shown to improve
+ * performance on certain hardware.
+ */
+ if (!backward && linesleft > 1)
+ {
+ lineoff = scan->rs_vistuples[lineindex + 1];
+ lpp = PageGetItemId(page, lineoff);
+ pg_prefetch_mem(PageGetItem(page, lpp));
+ }
+
/*
* if current tuple qualifies, return it.
*/
--
2.35.1.windows.2
[text/plain] v2-0004-heapam-WIP-cacheline-prefetching-for-hot-pruning.patch (1.4K, ../../CAApHDvo1TL1Bm6AWnPwGYVya82TA3fuqRQsjvTjA+=NzHa6cuw@mail.gmail.com/5-v2-0004-heapam-WIP-cacheline-prefetching-for-hot-pruning.patch)
download | inline diff:
From 8f47b1e0163028ec8928aaf02ce49d34bb89c647 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Wed, 19 Oct 2022 08:55:45 +1300
Subject: [PATCH v2 4/5] heapam: WIP: cacheline prefetching for hot pruning.
Author: Andres Freund, with contributions from Dmitry Dolgov
---
src/backend/access/heap/pruneheap.c | 24 ++++++++++++++++++++++++
1 file changed, 24 insertions(+)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 91c5f5e9ef..a094ac18f5 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -302,6 +302,30 @@ heap_page_prune(Relation relation, Buffer buffer,
maxoff = PageGetMaxOffsetNumber(page);
tup.t_tableOid = RelationGetRelid(prstate.rel);
+#if 1
+ for (char *p = (char *) PageGetItemId(page, FirstOffsetNumber);
+ p < (char *) PageGetItemId(page, maxoff);
+ p += 64)
+ {
+ pg_prefetch_mem((ItemId)p);
+ }
+
+
+ for (offnum = FirstOffsetNumber;
+ offnum <= maxoff;
+ offnum = OffsetNumberNext(offnum))
+ {
+ ItemId itemid;
+
+ itemid = PageGetItemId(page, offnum);
+ if (!ItemIdIsUsed(itemid) || ItemIdIsDead(itemid) || !ItemIdHasStorage(itemid))
+ continue;
+
+ pg_prefetch_mem((HeapTupleHeader) PageGetItem(page, itemid));
+ pg_prefetch_mem(PageGetItem(page, itemid) + sizeof(HeapTupleHeaderData) - 1);
+ }
+#endif
+
/*
* Determine HTSV for all tuples.
*
--
2.35.1.windows.2
[text/plain] v2-0003-Unroll-loop-in-heapgetpage.patch (2.7K, ../../CAApHDvo1TL1Bm6AWnPwGYVya82TA3fuqRQsjvTjA+=NzHa6cuw@mail.gmail.com/6-v2-0003-Unroll-loop-in-heapgetpage.patch)
download | inline diff:
From f1b183123befc1a3f096ba36b1c3834b4c67d3ec Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Thu, 17 Nov 2022 13:24:40 +1300
Subject: [PATCH v2 3/5] Unroll loop in heapgetpage
---
src/backend/access/heap/heapam.c | 48 ++++++++++++++++++++++++++++----
1 file changed, 42 insertions(+), 6 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 81c7f69644..8fe4f4c837 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -484,15 +484,51 @@ heapgetpage(TableScanDesc sscan, BlockNumber block)
loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
/*
- * Iterate forward over line items, they're laid out in increasing
- * order in memory. Doing this separately allows to benefit from
- * out-of-order capabilities of the CPU and simplifies the next loop.
+ * Iterate forward over line items processing 16 at a time (this
+ * assumes there will be 16 ItemIds per CPU cacheline.
*/
for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(page, lineoff);
- lineoff <= lines;
- lineoff++, lpp++)
+ lineoff <= lines - 15;
+ lineoff += 16, lpp += 16)
+ {
+ pg_prefetch_mem(PageGetItemId(page, lineoff + 16));
+ if (ItemIdIsNormal(lpp))
+ normoffsets[normcount++] = lineoff;
+ if (ItemIdIsNormal(lpp + 1))
+ normoffsets[normcount++] = lineoff + 1;
+ if (ItemIdIsNormal(lpp + 2))
+ normoffsets[normcount++] = lineoff + 2;
+ if (ItemIdIsNormal(lpp + 3))
+ normoffsets[normcount++] = lineoff + 3;
+ if (ItemIdIsNormal(lpp + 4))
+ normoffsets[normcount++] = lineoff + 4;
+ if (ItemIdIsNormal(lpp + 5))
+ normoffsets[normcount++] = lineoff + 5;
+ if (ItemIdIsNormal(lpp + 6))
+ normoffsets[normcount++] = lineoff + 6;
+ if (ItemIdIsNormal(lpp + 7))
+ normoffsets[normcount++] = lineoff + 7;
+ if (ItemIdIsNormal(lpp + 8))
+ normoffsets[normcount++] = lineoff + 8;
+ if (ItemIdIsNormal(lpp + 9))
+ normoffsets[normcount++] = lineoff + 9;
+ if (ItemIdIsNormal(lpp + 10))
+ normoffsets[normcount++] = lineoff + 10;
+ if (ItemIdIsNormal(lpp + 11))
+ normoffsets[normcount++] = lineoff + 11;
+ if (ItemIdIsNormal(lpp + 12))
+ normoffsets[normcount++] = lineoff + 12;
+ if (ItemIdIsNormal(lpp + 13))
+ normoffsets[normcount++] = lineoff + 13;
+ if (ItemIdIsNormal(lpp + 14))
+ normoffsets[normcount++] = lineoff + 14;
+ if (ItemIdIsNormal(lpp + 15))
+ normoffsets[normcount++] = lineoff + 15;
+ }
+
+ /* get remainder */
+ for (; lineoff <= lines; lineoff++, lpp++)
{
- pg_prefetch_mem(PageGetItemId(page, lineoff+5));
if (ItemIdIsNormal(lpp))
normoffsets[normcount++] = lineoff;
}
--
2.35.1.windows.2
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-11-23 07:29 ` sirisha chamarthi <[email protected]>
2022-11-23 07:44 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2 siblings, 1 reply; 24+ messages in thread
From: sirisha chamarthi @ 2022-11-23 07:29 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Tue, Nov 22, 2022 at 1:58 PM David Rowley <[email protected]> wrote:
> On Thu, 3 Nov 2022 at 06:25, Andres Freund <[email protected]> wrote:
> > Attached is an experimental patch/hack for that. It ended up being more
> > beneficial to make the access ordering more optimal than prefetching the
> tuple
> > contents, but I'm not at all sure that's the be-all-end-all.
>
> Thanks for writing that patch. I've been experimenting with it.
>
> I tried unrolling the loop (patch 0003) as you mentioned in:
>
> + * FIXME: Worth unrolling so that we don't fetch the same cacheline
> + * over and over, due to line items being smaller than a cacheline?
>
> but didn't see any gains from doing that.
>
> I also adjusted your patch a little so that instead of doing:
>
> - OffsetNumber rs_vistuples[MaxHeapTuplesPerPage]; /* their offsets */
> + OffsetNumber *rs_vistuples;
> + OffsetNumber rs_vistuples_d[MaxHeapTuplesPerPage]; /* their offsets */
>
> to work around the issue of having to populate rs_vistuples_d in
> reverse, I added a new field called rs_startindex to mark where the
> first element in the rs_vistuples array is. The way you wrote it seems
> to require fewer code changes, but per the FIXME comment you left, I
> get the idea you just did it the way you did to make it work enough
> for testing.
>
> I'm quite keen to move forward in committing the 0001 patch to add the
> pg_prefetch_mem macro. What I'm a little undecided about is what the
> best patch is to commit first to make use of the new macro.
>
> I did some tests on the attached set of patches:
>
> alter system set max_parallel_workers_per_gather = 0;
> select pg_reload_conf();
>
> create table t as select a from generate_series(1,10000000)a;
> alter table t set (autovacuum_enabled=false);
>
> $ cat bench.sql
> select * from t where a = 0;
>
> psql -c "select pg_prewarm('t');" postgres
>
> -- Test 1 no frozen tuples in "t"
>
> Master (@9c6ad5eaa):
> $ pgbench -n -f bench.sql -M prepared -T 10 postgres | grep -E "^latency"
> latency average = 383.332 ms
> latency average = 375.747 ms
> latency average = 376.090 ms
>
> Master + 0001 + 0002:
> $ pgbench -n -f bench.sql -M prepared -T 10 postgres | grep -E "^latency"
> latency average = 370.133 ms
> latency average = 370.149 ms
> latency average = 370.157 ms
>
> Master + 0001 + 0005:
> $ pgbench -n -f bench.sql -M prepared -T 10 postgres | grep -E "^latency"
> latency average = 372.662 ms
> latency average = 371.034 ms
> latency average = 372.709 ms
>
> -- Test 2 "select count(*) from t" with all tuples frozen
>
> $ cat bench1.sql
> select count(*) from t;
>
> psql -c "vacuum freeze t;" postgres
> psql -c "select pg_prewarm('t');" postgres
>
> Master (@9c6ad5eaa):
> $ pgbench -n -f bench1.sql -M prepared -T 10 postgres | grep -E "^latency"
> latency average = 406.238 ms
> latency average = 407.029 ms
> latency average = 406.962 ms
>
> Master + 0001 + 0005:
> $ pgbench -n -f bench1.sql -M prepared -T 10 postgres | grep -E "^latency"
> latency average = 345.470 ms
> latency average = 345.775 ms
> latency average = 345.354 ms
>
> My current thoughts are that it might be best to go with 0005 to start
> with. I know Melanie is working on making some changes in this area,
> so perhaps it's best to leave 0002 until that work is complete.
>
I ran your test1 exactly like your setup except the row count is 3000000
(with 13275 blocks). Shared_buffers is 128MB and the hardware configuration
details at the bottom of the mail. It appears *Master + 0001 + 0005 *regressed
compared to master slightly .
*Master (@56d0ed3b756b2e3799a7bbc0ac89bc7657ca2c33)*
Before vacuum:
/usr/local/pgsql/bin/pgbench -n -f bench.sql -M prepared -T 30 -P 10
postgres | grep -E "^latency"
latency average = 430.287 ms
After Vacuum:
/usr/local/pgsql/bin/pgbench -n -f bench.sql -M prepared -T 30 -P 10
postgres | grep -E "^latency"
latency average = 369.046 ms
*Master + 0001 + 0002:*
Before vacuum:
/usr/local/pgsql/bin/pgbench -n -f bench.sql -M prepared -T 30 -P 10
postgres | grep -E "^latency"
latency average = 427.983 ms
After Vacuum:
/usr/local/pgsql/bin/pgbench -n -f bench.sql -M prepared -T 30 -P 10
postgres | grep -E "^latency"
latency average = 367.185 ms
*Master + 0001 + 0005:*
Before vacuum:
/usr/local/pgsql/bin/pgbench -n -f bench.sql -M prepared -T 30 -P 10
postgres | grep -E "^latency"
latency average = 447.045 ms
After Vacuum:
/usr/local/pgsql/bin/pgbench -n -f bench.sql -M prepared -T 30 -P 10
postgres | grep -E "^latency"
latency average = 374.484 ms
lscpu output
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
Address sizes: 46 bits physical, 48 bits virtual
CPU(s): 1
On-line CPU(s) list: 0
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 63
Model name: Intel(R) Xeon(R) CPU E5-2673 v3 @ 2.40GHz
Stepping: 2
CPU MHz: 2397.224
BogoMIPS: 4794.44
Hypervisor vendor: Microsoft
Virtualization type: full
L1d cache: 32 KiB
L1i cache: 32 KiB
L2 cache: 256 KiB
L3 cache: 30 MiB
NUMA node0 CPU(s): 0
Vulnerability Itlb multihit: KVM: Mitigation: VMX unsupported
Vulnerability L1tf: Mitigation; PTE Inversion
Vulnerability Mds: Mitigation; Clear CPU buffers; SMT Host
state unknown
Vulnerability Meltdown: Mitigation; PTI
Vulnerability Mmio stale data: Vulnerable: Clear CPU buffers attempted,
no microcode; SMT Host state unknown
Vulnerability Spec store bypass: Vulnerable
Vulnerability Spectre v1: Mitigation; usercopy/swapgs barriers and
__user pointer sanitization
Vulnerability Spectre v2: Mitigation; Retpolines, STIBP disabled,
RSB filling
Vulnerability Srbds: Not affected
Vulnerability Tsx async abort: Not affected
Flags: fpu vme de pse tsc msr pae mce cx8 apic
sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx
pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology cpuid pni
pclmulqdq ssse3 fma cx16 pcid sse4_1
sse4_2 movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_lm abm
invpcid_single pti fsgsbase bmi1 avx2 smep bmi2 erms invpcid xsaveopt m
d_clear
>
> David
>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-23 07:29 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
@ 2022-11-23 07:44 ` David Rowley <[email protected]>
2022-11-23 08:27 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: David Rowley @ 2022-11-23 07:44 UTC (permalink / raw)
To: sirisha chamarthi <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Wed, 23 Nov 2022 at 20:29, sirisha chamarthi
<[email protected]> wrote:
> I ran your test1 exactly like your setup except the row count is 3000000 (with 13275 blocks). Shared_buffers is 128MB and the hardware configuration details at the bottom of the mail. It appears Master + 0001 + 0005 regressed compared to master slightly .
Thank you for running these tests.
Can you share if the plans used for these queries was a parallel plan?
I had set max_parallel_workers_per_gather to 0 to remove the
additional variability from parallel query.
Also, 13275 blocks is 104MBs, does EXPLAIN (ANALYZE, BUFFERS) indicate
that all pages were in shared buffers? I used pg_prewarm() to ensure
they were so that the runs were consistent.
David
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-23 07:29 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
2022-11-23 07:44 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-11-23 08:27 ` sirisha chamarthi <[email protected]>
2022-11-23 13:15 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: sirisha chamarthi @ 2022-11-23 08:27 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Tue, Nov 22, 2022 at 11:44 PM David Rowley <[email protected]> wrote:
> On Wed, 23 Nov 2022 at 20:29, sirisha chamarthi
> <[email protected]> wrote:
> > I ran your test1 exactly like your setup except the row count is 3000000
> (with 13275 blocks). Shared_buffers is 128MB and the hardware configuration
> details at the bottom of the mail. It appears Master + 0001 + 0005
> regressed compared to master slightly .
>
> Thank you for running these tests.
>
> Can you share if the plans used for these queries was a parallel plan?
> I had set max_parallel_workers_per_gather to 0 to remove the
> additional variability from parallel query.
>
> Also, 13275 blocks is 104MBs, does EXPLAIN (ANALYZE, BUFFERS) indicate
> that all pages were in shared buffers? I used pg_prewarm() to ensure
> they were so that the runs were consistent.
>
I reran the test with setting max_parallel_workers_per_gather = 0 and with
pg_prewarm. Appears I missed some step while testing on the master, thanks
for sharing the details. New numbers show master has higher latency
than *Master +
0001 + 0005*.
*Master*
Before vacuum:
latency average = 452.881 ms
After vacuum:
latency average = 393.880 ms
*Master + 0001 + 0005*
Before vacuum:
latency average = 441.832 ms
After vacuum:
latency average = 369.591 ms
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-23 07:29 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
2022-11-23 07:44 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-23 08:27 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
@ 2022-11-23 13:15 ` David Rowley <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: David Rowley @ 2022-11-23 13:15 UTC (permalink / raw)
To: sirisha chamarthi <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Wed, 23 Nov 2022 at 21:26, sirisha chamarthi
<[email protected]> wrote:
> Master
> After vacuum:
> latency average = 393.880 ms
>
> Master + 0001 + 0005
> After vacuum:
> latency average = 369.591 ms
Thank you for running those again. Those results make more sense.
Would you mind also testing the count(*) query too?
David
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-11-24 09:25 ` David Rowley <[email protected]>
2 siblings, 0 replies; 24+ messages in thread
From: David Rowley @ 2022-11-24 09:25 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Wed, 23 Nov 2022 at 10:58, David Rowley <[email protected]> wrote:
> My current thoughts are that it might be best to go with 0005 to start
> with. I know Melanie is working on making some changes in this area,
> so perhaps it's best to leave 0002 until that work is complete.
I tried running TPC-H @ scale 5 with master (@d09dbeb9) vs master +
0001 + 0005 patch. The results look quite promising. Query 15 seems
to run 15% faster and overall it's 4.23% faster.
Full results are attached.
David
query master Master + 0001 + 0005 compare
1 25999.5 25793.6 100.8%
2 1171.0 1152.0 101.6%
3 6180.5 5456.5 113.3%
4 1167.1 1107.0 105.4%
5 4968.3 4604.8 107.9%
6 3696.6 3306.4 111.8%
7 5501.4 4905.6 112.1%
8 1394.8 1345.2 103.7%
9 10861.2 11159.8 97.3%
10 4354.3 4356.4 100.0%
11 382.5 386.3 99.0%
12 3888.6 3838.0 101.3%
13 6905.0 6622.5 104.3%
14 3886.1 3429.8 113.3%
15 8009.7 6927.8 115.6%
16 2406.2 2363.9 101.8%
17 14.6 14.9 98.1%
18 11735.6 11453.9 102.5%
19 44.9 44.7 100.4%
20 262.8 246.6 106.6%
21 3014.1 3027.6 99.6%
22 176.4 179.3 98.4%
Attachments:
[text/plain] tpch_results.txt (571B, ../../CAApHDvomrfE0e+ct2gj+c=1z5J+8V2HNo4YOjARfN=PuyaTD=g@mail.gmail.com/2-tpch_results.txt)
download | inline:
query master Master + 0001 + 0005 compare
1 25999.5 25793.6 100.8%
2 1171.0 1152.0 101.6%
3 6180.5 5456.5 113.3%
4 1167.1 1107.0 105.4%
5 4968.3 4604.8 107.9%
6 3696.6 3306.4 111.8%
7 5501.4 4905.6 112.1%
8 1394.8 1345.2 103.7%
9 10861.2 11159.8 97.3%
10 4354.3 4356.4 100.0%
11 382.5 386.3 99.0%
12 3888.6 3838.0 101.3%
13 6905.0 6622.5 104.3%
14 3886.1 3429.8 113.3%
15 8009.7 6927.8 115.6%
16 2406.2 2363.9 101.8%
17 14.6 14.9 98.1%
18 11735.6 11453.9 102.5%
19 44.9 44.7 100.4%
20 262.8 246.6 106.6%
21 3014.1 3027.6 99.6%
22 176.4 179.3 98.4%
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
@ 2022-12-01 05:17 ` John Naylor <[email protected]>
2022-12-02 01:47 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2 siblings, 1 reply; 24+ messages in thread
From: John Naylor @ 2022-12-01 05:17 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Wed, Nov 23, 2022 at 4:58 AM David Rowley <[email protected]> wrote:
> My current thoughts are that it might be best to go with 0005 to start
> with.
+1
> I know Melanie is working on making some changes in this area,
> so perhaps it's best to leave 0002 until that work is complete.
There seem to be some open questions about that one as well.
I reran the same test in [1] (except I don't have the ability to lock clock
speed or affect huge pages) on an older CPU from 2014 (Intel(R) Xeon(R) CPU
E5-2695 v3 @ 2.30GHz, kernel 3.10 gcc 4.8) with good results:
HEAD:
Testing a1
latency average = 965.462 ms
Testing a2
latency average = 1054.608 ms
Testing a3
latency average = 1078.263 ms
Testing a4
latency average = 1120.933 ms
Testing a5
latency average = 1162.753 ms
Testing a6
latency average = 1298.876 ms
Testing a7
latency average = 1228.775 ms
Testing a8
latency average = 1293.535 ms
0001+0005:
Testing a1
latency average = 791.224 ms
Testing a2
latency average = 876.421 ms
Testing a3
latency average = 911.039 ms
Testing a4
latency average = 981.693 ms
Testing a5
latency average = 998.176 ms
Testing a6
latency average = 979.954 ms
Testing a7
latency average = 1066.523 ms
Testing a8
latency average = 1030.235 ms
I then tested a Power8 machine (also kernel 3.10 gcc 4.8). Configure
reports "checking for __builtin_prefetch... yes", but I don't think it does
anything here, as the results are within noise level. A quick search didn't
turn up anything informative on this platform, and I'm not motivated to dig
deeper. In any case, it doesn't make things worse.
HEAD:
Testing a1
latency average = 1402.163 ms
Testing a2
latency average = 1442.971 ms
Testing a3
latency average = 1599.188 ms
Testing a4
latency average = 1664.397 ms
Testing a5
latency average = 1782.091 ms
Testing a6
latency average = 1860.655 ms
Testing a7
latency average = 1929.120 ms
Testing a8
latency average = 2021.100 ms
0001+0005:
Testing a1
latency average = 1433.080 ms
Testing a2
latency average = 1428.369 ms
Testing a3
latency average = 1542.406 ms
Testing a4
latency average = 1642.452 ms
Testing a5
latency average = 1737.173 ms
Testing a6
latency average = 1828.239 ms
Testing a7
latency average = 1920.909 ms
Testing a8
latency average = 2036.922 ms
[1]
https://www.postgresql.org/message-id/CAFBsxsHqmH_S%3D4apc5agKsJsF6xZ9f6NaH0Z83jUYv3EgySHfw%40mail.g...
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Prefetch the next tuple's memory during seqscans
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-12-01 05:17 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
@ 2022-12-02 01:47 ` David Rowley <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: David Rowley @ 2022-12-02 01:47 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Developers <[email protected]>; Thomas Munro <[email protected]>
On Thu, 1 Dec 2022 at 18:18, John Naylor <[email protected]> wrote:
> I then tested a Power8 machine (also kernel 3.10 gcc 4.8). Configure reports "checking for __builtin_prefetch... yes", but I don't think it does anything here, as the results are within noise level. A quick search didn't turn up anything informative on this platform, and I'm not motivated to dig deeper. In any case, it doesn't make things worse.
Thanks for testing the power8 hardware.
Andres just let me test on some Apple M1 hardware (those cores are
insanely fast!)
Using the table and running the script from [1], with trimmed-down
output, I see:
Master @ edf12e7bbd
Testing a -> 158.037 ms
Testing a2 -> 164.442 ms
Testing a3 -> 171.523 ms
Testing a4 -> 189.892 ms
Testing a5 -> 217.197 ms
Testing a6 -> 186.790 ms
Testing a7 -> 189.491 ms
Testing a8 -> 195.384 ms
Testing a9 -> 200.547 ms
Testing a10 -> 206.149 ms
Testing a11 -> 211.708 ms
Testing a12 -> 217.976 ms
Testing a13 -> 224.565 ms
Testing a14 -> 230.642 ms
Testing a15 -> 237.372 ms
Testing a16 -> 244.110 ms
(checking for __builtin_prefetch... yes)
Master + v2-0001 + v2-0005
Testing a -> 157.477 ms
Testing a2 -> 163.720 ms
Testing a3 -> 171.159 ms
Testing a4 -> 186.837 ms
Testing a5 -> 205.220 ms
Testing a6 -> 184.585 ms
Testing a7 -> 189.879 ms
Testing a8 -> 195.650 ms
Testing a9 -> 201.220 ms
Testing a10 -> 207.162 ms
Testing a11 -> 213.255 ms
Testing a12 -> 219.313 ms
Testing a13 -> 225.763 ms
Testing a14 -> 237.337 ms
Testing a15 -> 239.440 ms
Testing a16 -> 245.740 ms
It does not seem like there's any improvement on this architecture.
There is a very small increase from "a" to "a6", but a very small
decrease in performance from "a7" to "a16". It's likely within the
expected noise level.
David
[1] https://postgr.es/m/CAApHDvqWexy_6jGmB39Vr3OqxZ_w6stAFkq52hODvwaW-19aiA@mail.gmail.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* [PATCH v20 2/6] Add REPACK command
@ 2025-07-26 17:57 Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Álvaro Herrera @ 2025-07-26 17:57 UTC (permalink / raw)
REPACK absorbs the functionality of VACUUM FULL and CLUSTER in a single
command. Because this functionality is completely different from
regular VACUUM, having it separate from VACUUM makes it easier for users
to understand; as for CLUSTER, the term is heavily overloaded in the
TI world and even in Postgres itself, so it's good that we can avoid it.
This also adds pg_repackdb, a new utility that can invoke the new
commands. This is heavily based on vacuumdb. We may still change the
implementation, depending on how does Windows like this one.
Author: Antonin Houska <[email protected]>
Reviewed-by: To fill in
Discussion: https://postgr.es/m/82651.1720540558@antos
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/monitoring.sgml | 223 ++++++-
doc/src/sgml/ref/allfiles.sgml | 2 +
doc/src/sgml/ref/cluster.sgml | 97 +--
doc/src/sgml/ref/clusterdb.sgml | 5 +
doc/src/sgml/ref/pg_repackdb.sgml | 479 ++++++++++++++
doc/src/sgml/ref/repack.sgml | 284 +++++++++
doc/src/sgml/ref/vacuum.sgml | 33 +-
doc/src/sgml/reference.sgml | 2 +
src/backend/access/heap/heapam_handler.c | 32 +-
src/backend/catalog/index.c | 2 +-
src/backend/catalog/system_views.sql | 26 +
src/backend/commands/cluster.c | 758 +++++++++++++++--------
src/backend/commands/vacuum.c | 3 +-
src/backend/parser/gram.y | 88 ++-
src/backend/tcop/utility.c | 20 +-
src/backend/utils/adt/pgstatfuncs.c | 2 +
src/bin/psql/tab-complete.in.c | 33 +-
src/bin/scripts/Makefile | 4 +-
src/bin/scripts/meson.build | 2 +
src/bin/scripts/pg_repackdb.c | 226 +++++++
src/bin/scripts/t/103_repackdb.pl | 24 +
src/bin/scripts/vacuuming.c | 60 +-
src/bin/scripts/vacuuming.h | 11 +-
src/include/commands/cluster.h | 8 +-
src/include/commands/progress.h | 61 +-
src/include/nodes/parsenodes.h | 20 +-
src/include/parser/kwlist.h | 1 +
src/include/tcop/cmdtaglist.h | 1 +
src/include/utils/backend_progress.h | 1 +
src/test/regress/expected/cluster.out | 125 +++-
src/test/regress/expected/rules.out | 23 +
src/test/regress/sql/cluster.sql | 59 ++
src/tools/pgindent/typedefs.list | 3 +
33 files changed, 2271 insertions(+), 447 deletions(-)
create mode 100644 doc/src/sgml/ref/pg_repackdb.sgml
create mode 100644 doc/src/sgml/ref/repack.sgml
create mode 100644 src/bin/scripts/pg_repackdb.c
create mode 100644 src/bin/scripts/t/103_repackdb.pl
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3f4a27a736e..12e103d319d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -405,6 +405,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_progress_repack</structname><indexterm><primary>pg_stat_progress_repack</primary></indexterm></entry>
+ <entry>One row for each backend running
+ <command>REPACK</command>, showing current progress. See
+ <xref linkend="repack-progress-reporting"/>.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_progress_basebackup</structname><indexterm><primary>pg_stat_progress_basebackup</primary></indexterm></entry>
<entry>One row for each WAL sender process streaming a base backup,
@@ -5506,7 +5514,8 @@ FROM pg_stat_get_backend_idset() AS backendid;
certain commands during command execution. Currently, the only commands
which support progress reporting are <command>ANALYZE</command>,
<command>CLUSTER</command>,
- <command>CREATE INDEX</command>, <command>VACUUM</command>,
+ <command>CREATE INDEX</command>, <command>REPACK</command>,
+ <command>VACUUM</command>,
<command>COPY</command>,
and <xref linkend="protocol-replication-base-backup"/> (i.e., replication
command that <xref linkend="app-pgbasebackup"/> issues to take
@@ -5965,6 +5974,218 @@ FROM pg_stat_get_backend_idset() AS backendid;
</table>
</sect2>
+ <sect2 id="repack-progress-reporting">
+ <title>REPACK Progress Reporting</title>
+
+ <indexterm>
+ <primary>pg_stat_progress_repack</primary>
+ </indexterm>
+
+ <para>
+ Whenever <command>REPACK</command> is running,
+ the <structname>pg_stat_progress_repack</structname> view will contain a
+ row for each backend that is currently running the command. The tables
+ below describe the information that will be reported and provide
+ information about how to interpret it.
+ </para>
+
+ <table id="pg-stat-progress-repack-view" xreflabel="pg_stat_progress_repack">
+ <title><structname>pg_stat_progress_repack</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pid</structfield> <type>integer</type>
+ </para>
+ <para>
+ Process ID of backend.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>datid</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of the database to which this backend is connected.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>datname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the database to which this backend is connected.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relid</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of the table being repacked.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>phase</structfield> <type>text</type>
+ </para>
+ <para>
+ Current processing phase. See <xref linkend="repack-phases"/>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>repack_index_relid</structfield> <type>oid</type>
+ </para>
+ <para>
+ If the table is being scanned using an index, this is the OID of the
+ index being used; otherwise, it is zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>heap_tuples_scanned</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of heap tuples scanned.
+ This counter only advances when the phase is
+ <literal>seq scanning heap</literal>,
+ <literal>index scanning heap</literal>
+ or <literal>writing new heap</literal>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>heap_tuples_written</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of heap tuples written.
+ This counter only advances when the phase is
+ <literal>seq scanning heap</literal>,
+ <literal>index scanning heap</literal>
+ or <literal>writing new heap</literal>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>heap_blks_total</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of heap blocks in the table. This number is reported
+ as of the beginning of <literal>seq scanning heap</literal>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>heap_blks_scanned</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of heap blocks scanned. This counter only advances when the
+ phase is <literal>seq scanning heap</literal>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>index_rebuild_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of indexes rebuilt. This counter only advances when the phase
+ is <literal>rebuilding index</literal>.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="repack-phases">
+ <title>REPACK Phases</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Phase</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>initializing</literal></entry>
+ <entry>
+ The command is preparing to begin scanning the heap. This phase is
+ expected to be very brief.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>seq scanning heap</literal></entry>
+ <entry>
+ The command is currently scanning the table using a sequential scan.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>index scanning heap</literal></entry>
+ <entry>
+ <command>REPACK</command> is currently scanning the table using an index scan.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>sorting tuples</literal></entry>
+ <entry>
+ <command>REPACK</command> is currently sorting tuples.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>writing new heap</literal></entry>
+ <entry>
+ <command>REPACK</command> is currently writing the new heap.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>swapping relation files</literal></entry>
+ <entry>
+ The command is currently swapping newly-built files into place.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>rebuilding index</literal></entry>
+ <entry>
+ The command is currently rebuilding an index.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>performing final cleanup</literal></entry>
+ <entry>
+ The command is performing final cleanup. When this phase is
+ completed, <command>REPACK</command> will end.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2 id="copy-progress-reporting">
<title>COPY Progress Reporting</title>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index f5be638867a..eabf92e3536 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -167,6 +167,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY refreshMaterializedView SYSTEM "refresh_materialized_view.sgml">
<!ENTITY reindex SYSTEM "reindex.sgml">
<!ENTITY releaseSavepoint SYSTEM "release_savepoint.sgml">
+<!ENTITY repack SYSTEM "repack.sgml">
<!ENTITY reset SYSTEM "reset.sgml">
<!ENTITY revoke SYSTEM "revoke.sgml">
<!ENTITY rollback SYSTEM "rollback.sgml">
@@ -212,6 +213,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY pgIsready SYSTEM "pg_isready.sgml">
<!ENTITY pgReceivewal SYSTEM "pg_receivewal.sgml">
<!ENTITY pgRecvlogical SYSTEM "pg_recvlogical.sgml">
+<!ENTITY pgRepackdb SYSTEM "pg_repackdb.sgml">
<!ENTITY pgResetwal SYSTEM "pg_resetwal.sgml">
<!ENTITY pgRestore SYSTEM "pg_restore.sgml">
<!ENTITY pgRewind SYSTEM "pg_rewind.sgml">
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index 8811f169ea0..cfcfb65e349 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -33,51 +33,13 @@ CLUSTER [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
<title>Description</title>
<para>
- <command>CLUSTER</command> instructs <productname>PostgreSQL</productname>
- to cluster the table specified
- by <replaceable class="parameter">table_name</replaceable>
- based on the index specified by
- <replaceable class="parameter">index_name</replaceable>. The index must
- already have been defined on
- <replaceable class="parameter">table_name</replaceable>.
+ The <command>CLUSTER</command> command is equivalent to
+ <xref linkend="sql-repack"/> with an <literal>USING INDEX</literal>
+ clause. See there for more details.
</para>
- <para>
- When a table is clustered, it is physically reordered
- based on the index information. Clustering is a one-time operation:
- when the table is subsequently updated, the changes are
- not clustered. That is, no attempt is made to store new or
- updated rows according to their index order. (If one wishes, one can
- periodically recluster by issuing the command again. Also, setting
- the table's <literal>fillfactor</literal> storage parameter to less than
- 100% can aid in preserving cluster ordering during updates, since updated
- rows are kept on the same page if enough space is available there.)
- </para>
+<!-- Do we need to describe exactly which options map to what? They seem obvious to me. -->
- <para>
- When a table is clustered, <productname>PostgreSQL</productname>
- remembers which index it was clustered by. The form
- <command>CLUSTER <replaceable class="parameter">table_name</replaceable></command>
- reclusters the table using the same index as before. You can also
- use the <literal>CLUSTER</literal> or <literal>SET WITHOUT CLUSTER</literal>
- forms of <link linkend="sql-altertable"><command>ALTER TABLE</command></link> to set the index to be used for
- future cluster operations, or to clear any previous setting.
- </para>
-
- <para>
- <command>CLUSTER</command> without a
- <replaceable class="parameter">table_name</replaceable> reclusters all the
- previously-clustered tables in the current database that the calling user
- has privileges for. This form of <command>CLUSTER</command> cannot be
- executed inside a transaction block.
- </para>
-
- <para>
- When a table is being clustered, an <literal>ACCESS
- EXCLUSIVE</literal> lock is acquired on it. This prevents any other
- database operations (both reads and writes) from operating on the
- table until the <command>CLUSTER</command> is finished.
- </para>
</refsect1>
<refsect1>
@@ -136,63 +98,12 @@ CLUSTER [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
on the table.
</para>
- <para>
- In cases where you are accessing single rows randomly
- within a table, the actual order of the data in the
- table is unimportant. However, if you tend to access some
- data more than others, and there is an index that groups
- them together, you will benefit from using <command>CLUSTER</command>.
- If you are requesting a range of indexed values from a table, or a
- single indexed value that has multiple rows that match,
- <command>CLUSTER</command> will help because once the index identifies the
- table page for the first row that matches, all other rows
- that match are probably already on the same table page,
- and so you save disk accesses and speed up the query.
- </para>
-
- <para>
- <command>CLUSTER</command> can re-sort the table using either an index scan
- on the specified index, or (if the index is a b-tree) a sequential
- scan followed by sorting. It will attempt to choose the method that
- will be faster, based on planner cost parameters and available statistical
- information.
- </para>
-
<para>
While <command>CLUSTER</command> is running, the <xref
linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
pg_temp</literal>.
</para>
- <para>
- When an index scan is used, a temporary copy of the table is created that
- contains the table data in the index order. Temporary copies of each
- index on the table are created as well. Therefore, you need free space on
- disk at least equal to the sum of the table size and the index sizes.
- </para>
-
- <para>
- When a sequential scan and sort is used, a temporary sort file is
- also created, so that the peak temporary space requirement is as much
- as double the table size, plus the index sizes. This method is often
- faster than the index scan method, but if the disk space requirement is
- intolerable, you can disable this choice by temporarily setting <xref
- linkend="guc-enable-sort"/> to <literal>off</literal>.
- </para>
-
- <para>
- It is advisable to set <xref linkend="guc-maintenance-work-mem"/> to
- a reasonably large value (but not more than the amount of RAM you can
- dedicate to the <command>CLUSTER</command> operation) before clustering.
- </para>
-
- <para>
- Because the planner records statistics about the ordering of
- tables, it is advisable to run <link linkend="sql-analyze"><command>ANALYZE</command></link>
- on the newly clustered table.
- Otherwise, the planner might make poor choices of query plans.
- </para>
-
<para>
Because <command>CLUSTER</command> remembers which indexes are clustered,
one can cluster the tables one wants clustered manually the first time,
diff --git a/doc/src/sgml/ref/clusterdb.sgml b/doc/src/sgml/ref/clusterdb.sgml
index 0d2051bf6f1..546c1289c31 100644
--- a/doc/src/sgml/ref/clusterdb.sgml
+++ b/doc/src/sgml/ref/clusterdb.sgml
@@ -64,6 +64,11 @@ PostgreSQL documentation
this utility and via other methods for accessing the server.
</para>
+ <para>
+ <application>clusterdb</application> has been superceded by
+ <application>pg_repackdb</application>.
+ </para>
+
</refsect1>
diff --git a/doc/src/sgml/ref/pg_repackdb.sgml b/doc/src/sgml/ref/pg_repackdb.sgml
new file mode 100644
index 00000000000..32570d071cb
--- /dev/null
+++ b/doc/src/sgml/ref/pg_repackdb.sgml
@@ -0,0 +1,479 @@
+<!--
+doc/src/sgml/ref/pg_repackdb.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="app-pgrepackdb">
+ <indexterm zone="app-pgrepackdb">
+ <primary>pg_repackdb</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle><application>pg_repackdb</application></refentrytitle>
+ <manvolnum>1</manvolnum>
+ <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>pg_repackdb</refname>
+ <refpurpose>repack and analyze a <productname>PostgreSQL</productname>
+ database</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+ <cmdsynopsis>
+ <command>pg_repackdb</command>
+ <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
+ <arg rep="repeat"><replaceable>option</replaceable></arg>
+
+ <arg choice="plain" rep="repeat">
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><option>-t</option></arg>
+ <arg choice="plain"><option>--table</option></arg>
+ </group>
+ <replaceable>table</replaceable>
+ <arg choice="opt">( <replaceable class="parameter">column</replaceable> [,...] )</arg>
+ </arg>
+ </arg>
+
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
+ </cmdsynopsis>
+
+ <cmdsynopsis>
+ <command>pg_repackdb</command>
+ <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
+ <arg rep="repeat"><replaceable>option</replaceable></arg>
+
+ <arg choice="plain" rep="repeat">
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><option>-n</option></arg>
+ <arg choice="plain"><option>--schema</option></arg>
+ </group>
+ <replaceable>schema</replaceable>
+ </arg>
+ </arg>
+
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
+ </cmdsynopsis>
+
+ <cmdsynopsis>
+ <command>pg_repackdb</command>
+ <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
+ <arg rep="repeat"><replaceable>option</replaceable></arg>
+
+ <arg choice="plain" rep="repeat">
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><option>-N</option></arg>
+ <arg choice="plain"><option>--exclude-schema</option></arg>
+ </group>
+ <replaceable>schema</replaceable>
+ </arg>
+ </arg>
+
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
+ </cmdsynopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <application>pg_repackdb</application> is a utility for repacking a
+ <productname>PostgreSQL</productname> database.
+ <application>pg_repackdb</application> will also generate internal
+ statistics used by the <productname>PostgreSQL</productname> query
+ optimizer.
+ </para>
+
+ <para>
+ <application>pg_repackdb</application> is a wrapper around the SQL
+ command <link linkend="sql-repack"><command>REPACK</command></link> There
+ is no effective difference between repacking and analyzing databases via
+ this utility and via other methods for accessing the server.
+ </para>
+
+ </refsect1>
+
+
+ <refsect1>
+ <title>Options</title>
+
+ <para>
+ <application>pg_repackdb</application> accepts the following command-line arguments:
+ <variablelist>
+ <varlistentry>
+ <term><option>-a</option></term>
+ <term><option>--all</option></term>
+ <listitem>
+ <para>
+ Repack all databases.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option><optional>-d</optional> <replaceable class="parameter">dbname</replaceable></option></term>
+ <term><option><optional>--dbname=</optional><replaceable class="parameter">dbname</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the name of the database to be repacked or analyzed,
+ when <option>-a</option>/<option>--all</option> is not used. If this
+ is not specified, the database name is read from the environment
+ variable <envar>PGDATABASE</envar>. If that is not set, the user name
+ specified for the connection is used.
+ The <replaceable>dbname</replaceable> can be
+ a <link linkend="libpq-connstring">connection string</link>. If so,
+ connection string parameters will override any conflicting command
+ line options.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-e</option></term>
+ <term><option>--echo</option></term>
+ <listitem>
+ <para>
+ Echo the commands that <application>pg_repackdb</application>
+ generates and sends to the server.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-j <replaceable class="parameter">njobs</replaceable></option></term>
+ <term><option>--jobs=<replaceable class="parameter">njobs</replaceable></option></term>
+ <listitem>
+ <para>
+ Execute the repack or analyze commands in parallel by running
+ <replaceable class="parameter">njobs</replaceable>
+ commands simultaneously. This option may reduce the processing time
+ but it also increases the load on the database server.
+ </para>
+ <para>
+ <application>pg_repackdb</application> will open
+ <replaceable class="parameter">njobs</replaceable> connections to the
+ database, so make sure your <xref linkend="guc-max-connections"/>
+ setting is high enough to accommodate all connections.
+ </para>
+ <para>
+ Note that using this mode might cause deadlock failures if certain
+ system catalogs are processed in parallel.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
+ <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
+ <listitem>
+ <para>
+ Repack or analyze all tables in
+ <replaceable class="parameter">schema</replaceable> only. Multiple
+ schemas can be repacked by writing multiple <option>-n</option>
+ switches.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-N <replaceable class="parameter">schema</replaceable></option></term>
+ <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
+ <listitem>
+ <para>
+ Do not repack or analyze any tables in
+ <replaceable class="parameter">schema</replaceable>. Multiple schemas
+ can be excluded by writing multiple <option>-N</option> switches.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-q</option></term>
+ <term><option>--quiet</option></term>
+ <listitem>
+ <para>
+ Do not display progress messages.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-t <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
+ <term><option>--table=<replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
+ <listitem>
+ <para>
+ Repack or analyze <replaceable class="parameter">table</replaceable>
+ only. Column names can be specified only in conjunction with
+ the <option>--analyze</option> option. Multiple tables can be
+ repacked by writing multiple
+ <option>-t</option> switches.
+ </para>
+ <tip>
+ <para>
+ If you specify columns, you probably have to escape the parentheses
+ from the shell. (See examples below.)
+ </para>
+ </tip>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-v</option></term>
+ <term><option>--verbose</option></term>
+ <listitem>
+ <para>
+ Print detailed information during processing.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-V</option></term>
+ <term><option>--version</option></term>
+ <listitem>
+ <para>
+ Print the <application>pg_repackdb</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-z</option></term>
+ <term><option>--analyze</option></term>
+ <listitem>
+ <para>
+ Also calculate statistics for use by the optimizer.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</option></term>
+ <term><option>--help</option></term>
+ <listitem>
+ <para>
+ Show help about <application>pg_repackdb</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ <application>pg_repackdb</application> also accepts
+ the following command-line arguments for connection parameters:
+ <variablelist>
+ <varlistentry>
+ <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
+ <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the host name of the machine on which the server
+ is running. If the value begins with a slash, it is used
+ as the directory for the Unix domain socket.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
+ <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the TCP port or local Unix domain socket file
+ extension on which the server
+ is listening for connections.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-U <replaceable class="parameter">username</replaceable></option></term>
+ <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
+ <listitem>
+ <para>
+ User name to connect as.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-w</option></term>
+ <term><option>--no-password</option></term>
+ <listitem>
+ <para>
+ Never issue a password prompt. If the server requires
+ password authentication and a password is not available by
+ other means such as a <filename>.pgpass</filename> file, the
+ connection attempt will fail. This option can be useful in
+ batch jobs and scripts where no user is present to enter a
+ password.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-W</option></term>
+ <term><option>--password</option></term>
+ <listitem>
+ <para>
+ Force <application>pg_repackdb</application> to prompt for a
+ password before connecting to a database.
+ </para>
+
+ <para>
+ This option is never essential, since
+ <application>pg_repackdb</application> will automatically prompt
+ for a password if the server demands password authentication.
+ However, <application>pg_repackdb</application> will waste a
+ connection attempt finding out that the server wants a password.
+ In some cases it is worth typing <option>-W</option> to avoid the extra
+ connection attempt.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></option></term>
+ <listitem>
+ <para>
+ When the <option>-a</option>/<option>--all</option> is used, connect
+ to this database to gather the list of databases to repack.
+ If not specified, the <literal>postgres</literal> database will be used,
+ or if that does not exist, <literal>template1</literal> will be used.
+ This can be a <link linkend="libpq-connstring">connection
+ string</link>. If so, connection string parameters will override any
+ conflicting command line options. Also, connection string parameters
+ other than the database name itself will be re-used when connecting
+ to other databases.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+
+
+ <refsect1>
+ <title>Environment</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><envar>PGDATABASE</envar></term>
+ <term><envar>PGHOST</envar></term>
+ <term><envar>PGPORT</envar></term>
+ <term><envar>PGUSER</envar></term>
+
+ <listitem>
+ <para>
+ Default connection parameters
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><envar>PG_COLOR</envar></term>
+ <listitem>
+ <para>
+ Specifies whether to use color in diagnostic messages. Possible values
+ are <literal>always</literal>, <literal>auto</literal> and
+ <literal>never</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ This utility, like most other <productname>PostgreSQL</productname> utilities,
+ also uses the environment variables supported by <application>libpq</application>
+ (see <xref linkend="libpq-envars"/>).
+ </para>
+
+ </refsect1>
+
+
+ <refsect1>
+ <title>Diagnostics</title>
+
+ <para>
+ In case of difficulty, see
+ <xref linkend="sql-repack"/> and <xref linkend="app-psql"/> for
+ discussions of potential problems and error messages.
+ The database server must be running at the
+ targeted host. Also, any default connection settings and environment
+ variables used by the <application>libpq</application> front-end
+ library will apply.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To repack the database <literal>test</literal>:
+<screen>
+<prompt>$ </prompt><userinput>pg_repackdb test</userinput>
+</screen>
+ </para>
+
+ <para>
+ To repack and analyze for the optimizer a database named
+ <literal>bigdb</literal>:
+<screen>
+<prompt>$ </prompt><userinput>pg_repackdb --analyze bigdb</userinput>
+</screen>
+ </para>
+
+ <para>
+ To repack a single table
+ <literal>foo</literal> in a database named
+ <literal>xyzzy</literal>, and analyze a single column
+ <literal>bar</literal> of the table for the optimizer:
+<screen>
+<prompt>$ </prompt><userinput>pg_repackdb --analyze --verbose --table='foo(bar)' xyzzy</userinput>
+</screen></para>
+
+ <para>
+ To repack all tables in the <literal>foo</literal> and <literal>bar</literal> schemas
+ in a database named <literal>xyzzy</literal>:
+<screen>
+<prompt>$ </prompt><userinput>pg_repackdb --schema='foo' --schema='bar' xyzzy</userinput>
+</screen></para>
+
+
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-repack"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/repack.sgml b/doc/src/sgml/ref/repack.sgml
new file mode 100644
index 00000000000..fd9d89f8aaa
--- /dev/null
+++ b/doc/src/sgml/ref/repack.sgml
@@ -0,0 +1,284 @@
+<!--
+doc/src/sgml/ref/repack.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-repack">
+ <indexterm zone="sql-repack">
+ <primary>REPACK</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>REPACK</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>REPACK</refname>
+ <refpurpose>rewrite a table to reclaim disk space</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+REPACK [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_name</replaceable> [ USING INDEX [ <replaceable class="parameter">index_name</replaceable> ] ] ]
+
+<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
+
+ VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+ ANALYSE | ANALYZE
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>REPACK</command> reclaims storage occupied by dead
+ tuples. Unlike <command>VACUUM</command>, it does so by rewriting the
+ entire contents of the table specified
+ by <replaceable class="parameter">table_name</replaceable> into a new disk
+ file with no extra space (except for the space guaranteed by
+ the <literal>fillfactor</literal> storage parameter), allowing unused space
+ to be returned to the operating system.
+ </para>
+
+ <para>
+ Without
+ a <replaceable class="parameter">table_name</replaceable>, <command>REPACK</command>
+ processes every table and materialized view in the current database that
+ the current user has the <literal>MAINTAIN</literal> privilege on. This
+ form of <command>REPACK</command> cannot be executed inside a transaction
+ block.
+ </para>
+
+ <para>
+ If a <literal>USING INDEX</literal> clause is specified, the rows are
+ physically reordered based on information from an index. Please see the
+ notes on clustering below.
+ </para>
+
+ <para>
+ When a table is being repacked, an <literal>ACCESS EXCLUSIVE</literal> lock
+ is acquired on it. This prevents any other database operations (both reads
+ and writes) from operating on the table until the <command>REPACK</command>
+ is finished.
+ </para>
+
+ <refsect2 id="sql-repack-notes-on-clustering" xreflabel="Notes on Clustering">
+ <title>Notes on Clustering</title>
+
+ <para>
+ If the <literal>USING INDEX</literal> clause is specified, the rows in
+ the table are physically reordered following an index: if an index name
+ is specified in the command, then that index is used; if no index name
+ is specified, then the index that has been configured as the index to
+ cluster on. If no index has been configured in this way, an error is
+ thrown. The index given in the <literal>USING INDEX</literal> clause
+ is configured as the index to cluster on, as well as an index given
+ to the <command>CLUSTER</command> command. An index can be set
+ manually using <command>ALTER TABLE ... CLUSTER ON</command>, and reset
+ with <command>ALTER TABLE ... SET WITHOUT CLUSTER</command>.
+ </para>
+
+ <para>
+ If no table name is specified in <command>REPACK USING INDEX</command>,
+ all tables which have a clustering index defined and which the calling
+ user has privileges for are processed.
+ </para>
+
+ <para>
+ Clustering is a one-time operation: when the table is
+ subsequently updated, the changes are not clustered. That is, no attempt
+ is made to store new or updated rows according to their index order. (If
+ one wishes, one can periodically recluster by issuing the command again.
+ Also, setting the table's <literal>fillfactor</literal> storage parameter
+ to less than 100% can aid in preserving cluster ordering during updates,
+ since updated rows are kept on the same page if enough space is available
+ there.)
+ </para>
+
+ <para>
+ In cases where you are accessing single rows randomly within a table, the
+ actual order of the data in the table is unimportant. However, if you tend
+ to access some data more than others, and there is an index that groups
+ them together, you will benefit from using clustering. If
+ you are requesting a range of indexed values from a table, or a single
+ indexed value that has multiple rows that match,
+ <command>REPACK</command> will help because once the index identifies the
+ table page for the first row that matches, all other rows that match are
+ probably already on the same table page, and so you save disk accesses and
+ speed up the query.
+ </para>
+
+ <para>
+ <command>REPACK</command> can re-sort the table using either an index scan
+ on the specified index (if the index is a b-tree), or a sequential scan
+ followed by sorting. It will attempt to choose the method that will be
+ faster, based on planner cost parameters and available statistical
+ information.
+ </para>
+
+ <para>
+ Because the planner records statistics about the ordering of tables, it is
+ advisable to
+ run <link linkend="sql-analyze"><command>ANALYZE</command></link> on the
+ newly repacked table. Otherwise, the planner might make poor choices of
+ query plans.
+ </para>
+ </refsect2>
+
+ <refsect2 id="sql-repack-notes-on-resources" xreflabel="Notes on Resources">
+ <title>Notes on Resources</title>
+
+ <para>
+ When an index scan or a sequential scan without sort is used, a temporary
+ copy of the table is created that contains the table data in the index
+ order. Temporary copies of each index on the table are created as well.
+ Therefore, you need free space on disk at least equal to the sum of the
+ table size and the index sizes.
+ </para>
+
+ <para>
+ When a sequential scan and sort is used, a temporary sort file is also
+ created, so that the peak temporary space requirement is as much as double
+ the table size, plus the index sizes. This method is often faster than
+ the index scan method, but if the disk space requirement is intolerable,
+ you can disable this choice by temporarily setting
+ <xref linkend="guc-enable-sort"/> to <literal>off</literal>.
+ </para>
+
+ <para>
+ It is advisable to set <xref linkend="guc-maintenance-work-mem"/> to a
+ reasonably large value (but not more than the amount of RAM you can
+ dedicate to the <command>REPACK</command> operation) before repacking.
+ </para>
+ </refsect2>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">table_name</replaceable></term>
+ <listitem>
+ <para>
+ The name (possibly schema-qualified) of a table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">index_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of an index.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>VERBOSE</literal></term>
+ <listitem>
+ <para>
+ Prints a progress report as each table is repacked
+ at <literal>INFO</literal> level.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ANALYZE</literal></term>
+ <term><literal>ANALYSE</literal></term>
+ <listitem>
+ <para>
+ Applies <xref linkend="sql-analyze"/> on the table after repacking. This is
+ currently only supported when a single (non-partitioned) table is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">boolean</replaceable></term>
+ <listitem>
+ <para>
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</literal>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ To repack a table, one must have the <literal>MAINTAIN</literal> privilege
+ on the table.
+ </para>
+
+ <para>
+ While <command>REPACK</command> is running, the <xref
+ linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
+ pg_temp</literal>.
+ </para>
+
+ <para>
+ Each backend running <command>REPACK</command> will report its progress
+ in the <structname>pg_stat_progress_repack</structname> view. See
+ <xref linkend="repack-progress-reporting"/> for details.
+ </para>
+
+ <para>
+ Repacking a partitioned table repacks each of its partitions. If an index
+ is specified, each partition is repacked using the partition of that
+ index. <command>REPACK</command> on a partitioned table cannot be executed
+ inside a transaction block.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Repack the table <literal>employees</literal>:
+<programlisting>
+REPACK employees;
+</programlisting>
+ </para>
+
+ <para>
+ Repack the table <literal>employees</literal> on the basis of its
+ index <literal>employees_ind</literal> (Since index is used here, this is
+ effectively clustering):
+<programlisting>
+REPACK employees USING INDEX employees_ind;
+</programlisting>
+ </para>
+
+ <para>
+ Repack all tables in the database on which you have
+ the <literal>MAINTAIN</literal> privilege:
+<programlisting>
+REPACK;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ There is no <command>REPACK</command> statement in the SQL standard.
+ </para>
+
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index bd5dcaf86a5..062b658cfcd 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -25,7 +25,6 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
- FULL [ <replaceable class="parameter">boolean</replaceable> ]
FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
@@ -39,6 +38,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
BUFFER_USAGE_LIMIT <replaceable class="parameter">size</replaceable>
+ FULL [ <replaceable class="parameter">boolean</replaceable> ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -95,20 +95,6 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
<title>Parameters</title>
<variablelist>
- <varlistentry>
- <term><literal>FULL</literal></term>
- <listitem>
- <para>
- Selects <quote>full</quote> vacuum, which can reclaim more
- space, but takes much longer and exclusively locks the table.
- This method also requires extra disk space, since it writes a
- new copy of the table and doesn't release the old copy until
- the operation is complete. Usually this should only be used when a
- significant amount of space needs to be reclaimed from within the table.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry>
<term><literal>FREEZE</literal></term>
<listitem>
@@ -362,6 +348,23 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>FULL</literal></term>
+ <listitem>
+ <para>
+ This option, which is deprecated, makes <command>VACUUM</command>
+ behave like <command>REPACK</command> without a
+ <literal>USING INDEX</literal> clause.
+ This method of compacting the table takes much longer than
+ <command>VACUUM</command> and exclusively locks the table.
+ This method also requires extra disk space, since it writes a
+ new copy of the table and doesn't release the old copy until
+ the operation is complete. Usually this should only be used when a
+ significant amount of space needs to be reclaimed from within the table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index ff85ace83fc..2ee08e21f41 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -195,6 +195,7 @@
&refreshMaterializedView;
&reindex;
&releaseSavepoint;
+ &repack;
&reset;
&revoke;
&rollback;
@@ -257,6 +258,7 @@
&pgIsready;
&pgReceivewal;
&pgRecvlogical;
+ &pgRepackdb;
&pgRestore;
&pgVerifyBackup;
&psqlRef;
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index bcbac844bb6..79f9de5d760 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -741,13 +741,13 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
if (OldIndex != NULL && !use_sort)
{
const int ci_index[] = {
- PROGRESS_CLUSTER_PHASE,
- PROGRESS_CLUSTER_INDEX_RELID
+ PROGRESS_REPACK_PHASE,
+ PROGRESS_REPACK_INDEX_RELID
};
int64 ci_val[2];
/* Set phase and OIDOldIndex to columns */
- ci_val[0] = PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP;
+ ci_val[0] = PROGRESS_REPACK_PHASE_INDEX_SCAN_HEAP;
ci_val[1] = RelationGetRelid(OldIndex);
pgstat_progress_update_multi_param(2, ci_index, ci_val);
@@ -759,15 +759,15 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
else
{
/* In scan-and-sort mode and also VACUUM FULL, set phase */
- pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
- PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP);
+ pgstat_progress_update_param(PROGRESS_REPACK_PHASE,
+ PROGRESS_REPACK_PHASE_SEQ_SCAN_HEAP);
tableScan = table_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL);
heapScan = (HeapScanDesc) tableScan;
indexScan = NULL;
/* Set total heap blocks */
- pgstat_progress_update_param(PROGRESS_CLUSTER_TOTAL_HEAP_BLKS,
+ pgstat_progress_update_param(PROGRESS_REPACK_TOTAL_HEAP_BLKS,
heapScan->rs_nblocks);
}
@@ -809,7 +809,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
* is manually updated to the correct value when the table
* scan finishes.
*/
- pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_BLKS_SCANNED,
+ pgstat_progress_update_param(PROGRESS_REPACK_HEAP_BLKS_SCANNED,
heapScan->rs_nblocks);
break;
}
@@ -825,7 +825,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
*/
if (prev_cblock != heapScan->rs_cblock)
{
- pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_BLKS_SCANNED,
+ pgstat_progress_update_param(PROGRESS_REPACK_HEAP_BLKS_SCANNED,
(heapScan->rs_cblock +
heapScan->rs_nblocks -
heapScan->rs_startblock
@@ -912,14 +912,14 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
* In scan-and-sort mode, report increase in number of tuples
* scanned
*/
- pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED,
+ pgstat_progress_update_param(PROGRESS_REPACK_HEAP_TUPLES_SCANNED,
*num_tuples);
}
else
{
const int ct_index[] = {
- PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED,
- PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN
+ PROGRESS_REPACK_HEAP_TUPLES_SCANNED,
+ PROGRESS_REPACK_HEAP_TUPLES_WRITTEN
};
int64 ct_val[2];
@@ -952,14 +952,14 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
double n_tuples = 0;
/* Report that we are now sorting tuples */
- pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
- PROGRESS_CLUSTER_PHASE_SORT_TUPLES);
+ pgstat_progress_update_param(PROGRESS_REPACK_PHASE,
+ PROGRESS_REPACK_PHASE_SORT_TUPLES);
tuplesort_performsort(tuplesort);
/* Report that we are now writing new heap */
- pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
- PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP);
+ pgstat_progress_update_param(PROGRESS_REPACK_PHASE,
+ PROGRESS_REPACK_PHASE_WRITE_NEW_HEAP);
for (;;)
{
@@ -977,7 +977,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
values, isnull,
rwstate);
/* Report n_tuples */
- pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN,
+ pgstat_progress_update_param(PROGRESS_REPACK_HEAP_TUPLES_WRITTEN,
n_tuples);
}
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index c4029a4f3d3..3063abff9a5 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -4079,7 +4079,7 @@ reindex_relation(const ReindexStmt *stmt, Oid relid, int flags,
Assert(!ReindexIsProcessingIndex(indexOid));
/* Set index rebuild count */
- pgstat_progress_update_param(PROGRESS_CLUSTER_INDEX_REBUILD_COUNT,
+ pgstat_progress_update_param(PROGRESS_REPACK_INDEX_REBUILD_COUNT,
i);
i++;
}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1b3c5a55882..b2b7b10c2be 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1279,6 +1279,32 @@ CREATE VIEW pg_stat_progress_cluster AS
FROM pg_stat_get_progress_info('CLUSTER') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
+CREATE VIEW pg_stat_progress_repack AS
+ SELECT
+ S.pid AS pid,
+ S.datid AS datid,
+ D.datname AS datname,
+ S.relid AS relid,
+ -- param1 is currently unused
+ CASE S.param2 WHEN 0 THEN 'initializing'
+ WHEN 1 THEN 'seq scanning heap'
+ WHEN 2 THEN 'index scanning heap'
+ WHEN 3 THEN 'sorting tuples'
+ WHEN 4 THEN 'writing new heap'
+ WHEN 5 THEN 'swapping relation files'
+ WHEN 6 THEN 'rebuilding index'
+ WHEN 7 THEN 'performing final cleanup'
+ END AS phase,
+ CAST(S.param3 AS oid) AS repack_index_relid,
+ S.param4 AS heap_tuples_scanned,
+ S.param5 AS heap_tuples_written,
+ S.param6 AS heap_blks_total,
+ S.param7 AS heap_blks_scanned,
+ S.param8 AS index_rebuild_count
+ FROM pg_stat_get_progress_info('REPACK') AS S
+ LEFT JOIN pg_database D ON S.datid = D.oid;
+
+
CREATE VIEW pg_stat_progress_create_index AS
SELECT
S.pid AS pid, S.datid AS datid, D.datname AS datname,
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index b55221d44cd..8b64f9e6795 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -67,18 +67,41 @@ typedef struct
Oid indexOid;
} RelToCluster;
-
-static void cluster_multiple_rels(List *rtcs, ClusterParams *params);
-static void rebuild_relation(Relation OldHeap, Relation index, bool verbose);
+static bool cluster_rel_recheck(RepackCommand cmd, Relation OldHeap,
+ Oid indexOid, Oid userid, int options);
+static void rebuild_relation(RepackCommand cmd, bool usingindex,
+ Relation OldHeap, Relation index, bool verbose);
static void copy_table_data(Relation NewHeap, Relation OldHeap, Relation OldIndex,
bool verbose, bool *pSwapToastByContent,
TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
-static List *get_tables_to_cluster(MemoryContext cluster_context);
-static List *get_tables_to_cluster_partitioned(MemoryContext cluster_context,
- Oid indexOid);
-static bool cluster_is_permitted_for_relation(Oid relid, Oid userid);
+static List *get_tables_to_repack(RepackCommand cmd, bool usingindex,
+ MemoryContext permcxt);
+static List *get_tables_to_repack_partitioned(RepackCommand cmd,
+ MemoryContext cluster_context,
+ Oid relid, bool rel_is_index);
+static bool cluster_is_permitted_for_relation(RepackCommand cmd,
+ Oid relid, Oid userid);
+static Relation process_single_relation(RepackStmt *stmt,
+ ClusterParams *params);
+static Oid determine_clustered_index(Relation rel, bool usingindex,
+ const char *indexname);
+static const char *
+RepackCommandAsString(RepackCommand cmd)
+{
+ switch (cmd)
+ {
+ case REPACK_COMMAND_REPACK:
+ return "REPACK";
+ case REPACK_COMMAND_VACUUMFULL:
+ return "VACUUM";
+ case REPACK_COMMAND_CLUSTER:
+ return "CLUSTER";
+ }
+ return "???";
+}
+
/*---------------------------------------------------------------------------
* This cluster code allows for clustering multiple tables at once. Because
* of this, we cannot just run everything on a single transaction, or we
@@ -104,191 +127,155 @@ static bool cluster_is_permitted_for_relation(Oid relid, Oid userid);
*---------------------------------------------------------------------------
*/
void
-cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
+ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel)
{
- ListCell *lc;
ClusterParams params = {0};
- bool verbose = false;
Relation rel = NULL;
- Oid indexOid = InvalidOid;
- MemoryContext cluster_context;
+ MemoryContext repack_context;
List *rtcs;
/* Parse option list */
- foreach(lc, stmt->params)
+ foreach_node(DefElem, opt, stmt->params)
{
- DefElem *opt = (DefElem *) lfirst(lc);
-
if (strcmp(opt->defname, "verbose") == 0)
- verbose = defGetBoolean(opt);
+ params.options |= defGetBoolean(opt) ? CLUOPT_VERBOSE : 0;
+ else if (strcmp(opt->defname, "analyze") == 0 ||
+ strcmp(opt->defname, "analyse") == 0)
+ params.options |= defGetBoolean(opt) ? CLUOPT_ANALYZE : 0;
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("unrecognized CLUSTER option \"%s\"",
+ errmsg("unrecognized %s option \"%s\"",
+ RepackCommandAsString(stmt->command),
opt->defname),
parser_errposition(pstate, opt->location)));
}
- params.options = (verbose ? CLUOPT_VERBOSE : 0);
-
+ /*
+ * If a single relation is specified, process it and we're done ... unless
+ * the relation is a partitioned table, in which case we fall through.
+ */
if (stmt->relation != NULL)
{
- /* This is the single-relation case. */
- Oid tableOid;
-
- /*
- * Find, lock, and check permissions on the table. We obtain
- * AccessExclusiveLock right away to avoid lock-upgrade hazard in the
- * single-transaction case.
- */
- tableOid = RangeVarGetRelidExtended(stmt->relation,
- AccessExclusiveLock,
- 0,
- RangeVarCallbackMaintainsTable,
- NULL);
- rel = table_open(tableOid, NoLock);
-
- /*
- * Reject clustering a remote temp table ... their local buffer
- * manager is not going to cope.
- */
- if (RELATION_IS_OTHER_TEMP(rel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot cluster temporary tables of other sessions")));
-
- if (stmt->indexname == NULL)
- {
- ListCell *index;
-
- /* We need to find the index that has indisclustered set. */
- foreach(index, RelationGetIndexList(rel))
- {
- indexOid = lfirst_oid(index);
- if (get_index_isclustered(indexOid))
- break;
- indexOid = InvalidOid;
- }
-
- if (!OidIsValid(indexOid))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("there is no previously clustered index for table \"%s\"",
- stmt->relation->relname)));
- }
- else
- {
- /*
- * The index is expected to be in the same namespace as the
- * relation.
- */
- indexOid = get_relname_relid(stmt->indexname,
- rel->rd_rel->relnamespace);
- if (!OidIsValid(indexOid))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("index \"%s\" for table \"%s\" does not exist",
- stmt->indexname, stmt->relation->relname)));
- }
-
- /* For non-partitioned tables, do what we came here to do. */
- if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
- {
- cluster_rel(rel, indexOid, ¶ms);
- /* cluster_rel closes the relation, but keeps lock */
-
+ rel = process_single_relation(stmt, ¶ms);
+ if (rel == NULL)
return;
- }
}
+ /* Don't allow this for now. Maybe we can add support for this later */
+ if (params.options & CLUOPT_ANALYZE)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot ANALYZE multiple tables"));
+
/*
* By here, we know we are in a multi-table situation. In order to avoid
* holding locks for too long, we want to process each table in its own
* transaction. This forces us to disallow running inside a user
* transaction block.
*/
- PreventInTransactionBlock(isTopLevel, "CLUSTER");
+ PreventInTransactionBlock(isTopLevel, RepackCommandAsString(stmt->command));
/* Also, we need a memory context to hold our list of relations */
- cluster_context = AllocSetContextCreate(PortalContext,
- "Cluster",
- ALLOCSET_DEFAULT_SIZES);
+ repack_context = AllocSetContextCreate(PortalContext,
+ "Repack",
+ ALLOCSET_DEFAULT_SIZES);
+
+ params.options |= CLUOPT_RECHECK;
/*
- * Either we're processing a partitioned table, or we were not given any
- * table name at all. In either case, obtain a list of relations to
- * process.
- *
- * In the former case, an index name must have been given, so we don't
- * need to recheck its "indisclustered" bit, but we have to check that it
- * is an index that we can cluster on. In the latter case, we set the
- * option bit to have indisclustered verified.
- *
- * Rechecking the relation itself is necessary here in all cases.
+ * If we don't have a relation yet, determine a relation list. If we do,
+ * then it must be a partitioned table, and we want to process its
+ * partitions.
*/
- params.options |= CLUOPT_RECHECK;
- if (rel != NULL)
+ if (rel == NULL)
{
- Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
- check_index_is_clusterable(rel, indexOid, AccessShareLock);
- rtcs = get_tables_to_cluster_partitioned(cluster_context, indexOid);
-
- /* close relation, releasing lock on parent table */
- table_close(rel, AccessExclusiveLock);
+ Assert(stmt->indexname == NULL);
+ rtcs = get_tables_to_repack(stmt->command, stmt->usingindex,
+ repack_context);
}
else
{
- rtcs = get_tables_to_cluster(cluster_context);
- params.options |= CLUOPT_RECHECK_ISCLUSTERED;
+ Oid relid;
+ bool rel_is_index;
+
+ Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
+
+ /*
+ * If an index name was specified, resolve it now and pass it down.
+ */
+ if (stmt->usingindex)
+ {
+ /*
+ * XXX how should this behave? Passing no index to a partitioned
+ * table could be useful to have certain partitions clustered by
+ * some index, and other partitions by a different index.
+ */
+ if (!stmt->indexname)
+ ereport(ERROR,
+ errmsg("there is no previously clustered index for table \"%s\"",
+ RelationGetRelationName(rel)));
+
+ relid = determine_clustered_index(rel, true, stmt->indexname);
+ if (!OidIsValid(relid))
+ elog(ERROR, "unable to determine index to cluster on");
+ /* XXX is this the right place for this check? */
+ check_index_is_clusterable(rel, relid, AccessExclusiveLock);
+ rel_is_index = true;
+ }
+ else
+ {
+ relid = RelationGetRelid(rel);
+ rel_is_index = false;
+ }
+
+ rtcs = get_tables_to_repack_partitioned(stmt->command, repack_context,
+ relid, rel_is_index);
+
+ /* close parent relation, releasing lock on it */
+ table_close(rel, AccessExclusiveLock);
+ rel = NULL;
}
- /* Do the job. */
- cluster_multiple_rels(rtcs, ¶ms);
-
- /* Start a new transaction for the cleanup work. */
- StartTransactionCommand();
-
- /* Clean up working storage */
- MemoryContextDelete(cluster_context);
-}
-
-/*
- * Given a list of relations to cluster, process each of them in a separate
- * transaction.
- *
- * We expect to be in a transaction at start, but there isn't one when we
- * return.
- */
-static void
-cluster_multiple_rels(List *rtcs, ClusterParams *params)
-{
- ListCell *lc;
-
/* Commit to get out of starting transaction */
PopActiveSnapshot();
CommitTransactionCommand();
/* Cluster the tables, each in a separate transaction */
- foreach(lc, rtcs)
+ Assert(rel == NULL);
+ foreach_ptr(RelToCluster, rtc, rtcs)
{
- RelToCluster *rtc = (RelToCluster *) lfirst(lc);
- Relation rel;
-
/* Start a new transaction for each relation. */
StartTransactionCommand();
+ /*
+ * Open the target table, coping with the case where it has been
+ * dropped.
+ */
+ rel = try_table_open(rtc->tableOid, AccessExclusiveLock);
+ if (rel == NULL)
+ {
+ CommitTransactionCommand();
+ continue;
+ }
+
/* functions in indexes may want a snapshot set */
PushActiveSnapshot(GetTransactionSnapshot());
- rel = table_open(rtc->tableOid, AccessExclusiveLock);
-
/* Process this table */
- cluster_rel(rel, rtc->indexOid, params);
+ cluster_rel(stmt->command, stmt->usingindex,
+ rel, rtc->indexOid, ¶ms);
/* cluster_rel closes the relation, but keeps lock */
PopActiveSnapshot();
CommitTransactionCommand();
}
+
+ /* Start a new transaction for the cleanup work. */
+ StartTransactionCommand();
+
+ /* Clean up working storage */
+ MemoryContextDelete(repack_context);
}
/*
@@ -304,11 +291,14 @@ cluster_multiple_rels(List *rtcs, ClusterParams *params)
* them incrementally while we load the table.
*
* If indexOid is InvalidOid, the table will be rewritten in physical order
- * instead of index order. This is the new implementation of VACUUM FULL,
- * and error messages should refer to the operation as VACUUM not CLUSTER.
+ * instead of index order.
+ *
+ * 'cmd' indicates which command is being executed, to be used for error
+ * messages.
*/
void
-cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params)
+cluster_rel(RepackCommand cmd, bool usingindex,
+ Relation OldHeap, Oid indexOid, ClusterParams *params)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid save_userid;
@@ -323,13 +313,25 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params)
/* Check for user-requested abort. */
CHECK_FOR_INTERRUPTS();
- pgstat_progress_start_command(PROGRESS_COMMAND_CLUSTER, tableOid);
- if (OidIsValid(indexOid))
- pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND,
- PROGRESS_CLUSTER_COMMAND_CLUSTER);
+ if (cmd == REPACK_COMMAND_REPACK)
+ pgstat_progress_start_command(PROGRESS_COMMAND_REPACK, tableOid);
else
- pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND,
+ pgstat_progress_start_command(PROGRESS_COMMAND_CLUSTER, tableOid);
+
+ if (cmd == REPACK_COMMAND_REPACK)
+ pgstat_progress_update_param(PROGRESS_REPACK_COMMAND,
+ PROGRESS_REPACK_COMMAND_REPACK);
+ else if (cmd == REPACK_COMMAND_CLUSTER)
+ {
+ pgstat_progress_update_param(PROGRESS_REPACK_COMMAND,
+ PROGRESS_CLUSTER_COMMAND_CLUSTER);
+ }
+ else
+ {
+ Assert(cmd == REPACK_COMMAND_VACUUMFULL);
+ pgstat_progress_update_param(PROGRESS_REPACK_COMMAND,
PROGRESS_CLUSTER_COMMAND_VACUUM_FULL);
+ }
/*
* Switch to the table owner's userid, so that any index functions are run
@@ -351,63 +353,21 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params)
* to cluster a not-previously-clustered index.
*/
if (recheck)
- {
- /* Check that the user still has privileges for the relation */
- if (!cluster_is_permitted_for_relation(tableOid, save_userid))
- {
- relation_close(OldHeap, AccessExclusiveLock);
+ if (!cluster_rel_recheck(cmd, OldHeap, indexOid, save_userid,
+ params->options))
goto out;
- }
-
- /*
- * Silently skip a temp table for a remote session. Only doing this
- * check in the "recheck" case is appropriate (which currently means
- * somebody is executing a database-wide CLUSTER or on a partitioned
- * table), because there is another check in cluster() which will stop
- * any attempt to cluster remote temp tables by name. There is
- * another check in cluster_rel which is redundant, but we leave it
- * for extra safety.
- */
- if (RELATION_IS_OTHER_TEMP(OldHeap))
- {
- relation_close(OldHeap, AccessExclusiveLock);
- goto out;
- }
-
- if (OidIsValid(indexOid))
- {
- /*
- * Check that the index still exists
- */
- if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(indexOid)))
- {
- relation_close(OldHeap, AccessExclusiveLock);
- goto out;
- }
-
- /*
- * Check that the index is still the one with indisclustered set,
- * if needed.
- */
- if ((params->options & CLUOPT_RECHECK_ISCLUSTERED) != 0 &&
- !get_index_isclustered(indexOid))
- {
- relation_close(OldHeap, AccessExclusiveLock);
- goto out;
- }
- }
- }
/*
- * We allow VACUUM FULL, but not CLUSTER, on shared catalogs. CLUSTER
- * would work in most respects, but the index would only get marked as
- * indisclustered in the current database, leading to unexpected behavior
- * if CLUSTER were later invoked in another database.
+ * We allow repacking shared catalogs only when not using an index. It
+ * would work to use an index in most respects, but the index would only
+ * get marked as indisclustered in the current database, leading to
+ * unexpected behavior if CLUSTER were later invoked in another database.
*/
- if (OidIsValid(indexOid) && OldHeap->rd_rel->relisshared)
+ if (usingindex && OldHeap->rd_rel->relisshared)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot cluster a shared catalog")));
+ errmsg("cannot run \"%s\" on a shared catalog",
+ RepackCommandAsString(cmd))));
/*
* Don't process temp tables of other backends ... their local buffer
@@ -415,21 +375,30 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params)
*/
if (RELATION_IS_OTHER_TEMP(OldHeap))
{
- if (OidIsValid(indexOid))
+ if (cmd == REPACK_COMMAND_CLUSTER)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot cluster temporary tables of other sessions")));
+ else if (cmd == REPACK_COMMAND_REPACK)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot repack temporary tables of other sessions")));
+ }
else
+ {
+ Assert(cmd == REPACK_COMMAND_VACUUMFULL);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot vacuum temporary tables of other sessions")));
+ }
}
/*
* Also check for active uses of the relation in the current transaction,
* including open scans and pending AFTER trigger events.
*/
- CheckTableNotInUse(OldHeap, OidIsValid(indexOid) ? "CLUSTER" : "VACUUM");
+ CheckTableNotInUse(OldHeap, RepackCommandAsString(cmd));
/* Check heap and index are valid to cluster on */
if (OidIsValid(indexOid))
@@ -469,7 +438,7 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params)
TransferPredicateLocksToHeapRelation(OldHeap);
/* rebuild_relation does all the dirty work */
- rebuild_relation(OldHeap, index, verbose);
+ rebuild_relation(cmd, usingindex, OldHeap, index, verbose);
/* rebuild_relation closes OldHeap, and index if valid */
out:
@@ -482,6 +451,63 @@ out:
pgstat_progress_end_command();
}
+/*
+ * Check if the table (and its index) still meets the requirements of
+ * cluster_rel().
+ */
+static bool
+cluster_rel_recheck(RepackCommand cmd, Relation OldHeap, Oid indexOid,
+ Oid userid, int options)
+{
+ Oid tableOid = RelationGetRelid(OldHeap);
+
+ /* Check that the user still has privileges for the relation */
+ if (!cluster_is_permitted_for_relation(cmd, tableOid, userid))
+ {
+ relation_close(OldHeap, AccessExclusiveLock);
+ return false;
+ }
+
+ /*
+ * Silently skip a temp table for a remote session. Only doing this check
+ * in the "recheck" case is appropriate (which currently means somebody is
+ * executing a database-wide CLUSTER or on a partitioned table), because
+ * there is another check in cluster() which will stop any attempt to
+ * cluster remote temp tables by name. There is another check in
+ * cluster_rel which is redundant, but we leave it for extra safety.
+ */
+ if (RELATION_IS_OTHER_TEMP(OldHeap))
+ {
+ relation_close(OldHeap, AccessExclusiveLock);
+ return false;
+ }
+
+ if (OidIsValid(indexOid))
+ {
+ /*
+ * Check that the index still exists
+ */
+ if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(indexOid)))
+ {
+ relation_close(OldHeap, AccessExclusiveLock);
+ return false;
+ }
+
+ /*
+ * Check that the index is still the one with indisclustered set, if
+ * needed.
+ */
+ if ((options & CLUOPT_RECHECK_ISCLUSTERED) != 0 &&
+ !get_index_isclustered(indexOid))
+ {
+ relation_close(OldHeap, AccessExclusiveLock);
+ return false;
+ }
+ }
+
+ return true;
+}
+
/*
* Verify that the specified heap and index are valid to cluster on
*
@@ -626,7 +652,8 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal)
* On exit, they are closed, but locks on them are not released.
*/
static void
-rebuild_relation(Relation OldHeap, Relation index, bool verbose)
+rebuild_relation(RepackCommand cmd, bool usingindex,
+ Relation OldHeap, Relation index, bool verbose)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid accessMethod = OldHeap->rd_rel->relam;
@@ -642,8 +669,8 @@ rebuild_relation(Relation OldHeap, Relation index, bool verbose)
Assert(CheckRelationLockedByMe(OldHeap, AccessExclusiveLock, false) &&
(index == NULL || CheckRelationLockedByMe(index, AccessExclusiveLock, false)));
- if (index)
- /* Mark the correct index as clustered */
+ /* for CLUSTER or REPACK USING INDEX, mark the index as the one to use */
+ if (usingindex)
mark_index_clustered(OldHeap, RelationGetRelid(index), true);
/* Remember info about rel before closing OldHeap */
@@ -1458,8 +1485,8 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
int i;
/* Report that we are now swapping relation files */
- pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
- PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES);
+ pgstat_progress_update_param(PROGRESS_REPACK_PHASE,
+ PROGRESS_REPACK_PHASE_SWAP_REL_FILES);
/* Zero out possible results from swapped_relation_files */
memset(mapped_tables, 0, sizeof(mapped_tables));
@@ -1509,14 +1536,14 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
reindex_flags |= REINDEX_REL_FORCE_INDEXES_PERMANENT;
/* Report that we are now reindexing relations */
- pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
- PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
+ pgstat_progress_update_param(PROGRESS_REPACK_PHASE,
+ PROGRESS_REPACK_PHASE_REBUILD_INDEX);
reindex_relation(NULL, OIDOldHeap, reindex_flags, &reindex_params);
/* Report that we are now doing clean up */
- pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
- PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP);
+ pgstat_progress_update_param(PROGRESS_REPACK_PHASE,
+ PROGRESS_REPACK_PHASE_FINAL_CLEANUP);
/*
* If the relation being rebuilt is pg_class, swap_relation_files()
@@ -1632,69 +1659,137 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
}
}
-
/*
- * Get a list of tables that the current user has privileges on and
- * have indisclustered set. Return the list in a List * of RelToCluster
- * (stored in the specified memory context), each one giving the tableOid
- * and the indexOid on which the table is already clustered.
+ * Determine which relations to process, when REPACK/CLUSTER is called
+ * without specifying a table name. The exact process depends on whether
+ * USING INDEX was given or not, and in any case we only return tables and
+ * materialized views that the current user has privileges to repack/cluster.
+ *
+ * If USING INDEX was given, we scan pg_index to find those that have
+ * indisclustered set; if it was not given, scan pg_class and return all
+ * tables.
+ *
+ * Return it as a list of RelToCluster in the given memory context.
*/
static List *
-get_tables_to_cluster(MemoryContext cluster_context)
+get_tables_to_repack(RepackCommand command, bool usingindex,
+ MemoryContext permcxt)
{
- Relation indRelation;
+ Relation catalog;
TableScanDesc scan;
- ScanKeyData entry;
- HeapTuple indexTuple;
- Form_pg_index index;
+ HeapTuple tuple;
MemoryContext old_context;
List *rtcs = NIL;
- /*
- * Get all indexes that have indisclustered set and that the current user
- * has the appropriate privileges for.
- */
- indRelation = table_open(IndexRelationId, AccessShareLock);
- ScanKeyInit(&entry,
- Anum_pg_index_indisclustered,
- BTEqualStrategyNumber, F_BOOLEQ,
- BoolGetDatum(true));
- scan = table_beginscan_catalog(indRelation, 1, &entry);
- while ((indexTuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ if (usingindex)
{
- RelToCluster *rtc;
+ ScanKeyData entry;
- index = (Form_pg_index) GETSTRUCT(indexTuple);
+ catalog = table_open(IndexRelationId, AccessShareLock);
+ ScanKeyInit(&entry,
+ Anum_pg_index_indisclustered,
+ BTEqualStrategyNumber, F_BOOLEQ,
+ BoolGetDatum(true));
+ scan = table_beginscan_catalog(catalog, 1, &entry);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ RelToCluster *rtc;
+ Form_pg_index index;
- if (!cluster_is_permitted_for_relation(index->indrelid, GetUserId()))
- continue;
+ index = (Form_pg_index) GETSTRUCT(tuple);
- /* Use a permanent memory context for the result list */
- old_context = MemoryContextSwitchTo(cluster_context);
+ /*
+ * XXX I think the only reason there's no test failure here is
+ * that we seldom have clustered indexes that would be affected by
+ * concurrency. Maybe we should also do the
+ * ConditionalLockRelationOid+SearchSysCacheExists dance that we
+ * do below.
+ */
+ if (!cluster_is_permitted_for_relation(command, index->indrelid,
+ GetUserId()))
+ continue;
- rtc = (RelToCluster *) palloc(sizeof(RelToCluster));
- rtc->tableOid = index->indrelid;
- rtc->indexOid = index->indexrelid;
- rtcs = lappend(rtcs, rtc);
+ /* Use a permanent memory context for the result list */
+ old_context = MemoryContextSwitchTo(permcxt);
- MemoryContextSwitchTo(old_context);
+ rtc = (RelToCluster *) palloc(sizeof(RelToCluster));
+ rtc->tableOid = index->indrelid;
+ rtc->indexOid = index->indexrelid;
+ rtcs = lappend(rtcs, rtc);
+
+ MemoryContextSwitchTo(old_context);
+ }
}
- table_endscan(scan);
+ else
+ {
+ catalog = table_open(RelationRelationId, AccessShareLock);
+ scan = table_beginscan_catalog(catalog, 0, NULL);
- relation_close(indRelation, AccessShareLock);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ RelToCluster *rtc;
+ Form_pg_class class;
+
+ class = (Form_pg_class) GETSTRUCT(tuple);
+
+ /*
+ * Try to obtain a light lock on the table, to ensure it doesn't
+ * go away while we collect the list. If we cannot, just
+ * disregard the table. XXX we could release at the bottom of the
+ * loop, but for now just hold it until this transaction is
+ * finished.
+ */
+ if (!ConditionalLockRelationOid(class->oid, AccessShareLock))
+ continue;
+
+ /* Verify that the table still exists. */
+ if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(class->oid)))
+ {
+ /* Release useless lock */
+ UnlockRelationOid(class->oid, AccessShareLock);
+ continue;
+ }
+
+ /* Can only process plain tables and matviews */
+ if (class->relkind != RELKIND_RELATION &&
+ class->relkind != RELKIND_MATVIEW)
+ continue;
+
+ if (!cluster_is_permitted_for_relation(command, class->oid,
+ GetUserId()))
+ continue;
+
+ /* Use a permanent memory context for the result list */
+ old_context = MemoryContextSwitchTo(permcxt);
+
+ rtc = (RelToCluster *) palloc(sizeof(RelToCluster));
+ rtc->tableOid = class->oid;
+ rtc->indexOid = InvalidOid;
+ rtcs = lappend(rtcs, rtc);
+
+ MemoryContextSwitchTo(old_context);
+ }
+ }
+
+ table_endscan(scan);
+ relation_close(catalog, AccessShareLock);
return rtcs;
}
/*
- * Given an index on a partitioned table, return a list of RelToCluster for
+ * Given a partitioned table or its index, return a list of RelToCluster for
* all the children leaves tables/indexes.
*
* Like expand_vacuum_rel, but here caller must hold AccessExclusiveLock
* on the table containing the index.
+ *
+ * 'rel_is_index' tells whether 'relid' is that of an index (true) or of the
+ * owning relation.
*/
static List *
-get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)
+get_tables_to_repack_partitioned(RepackCommand cmd, MemoryContext cluster_context,
+ Oid relid, bool rel_is_index)
{
List *inhoids;
ListCell *lc;
@@ -1702,17 +1797,33 @@ get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)
MemoryContext old_context;
/* Do not lock the children until they're processed */
- inhoids = find_all_inheritors(indexOid, NoLock, NULL);
+ inhoids = find_all_inheritors(relid, NoLock, NULL);
foreach(lc, inhoids)
{
- Oid indexrelid = lfirst_oid(lc);
- Oid relid = IndexGetRelation(indexrelid, false);
+ Oid inhoid = lfirst_oid(lc);
+ Oid inhrelid,
+ inhindid;
RelToCluster *rtc;
- /* consider only leaf indexes */
- if (get_rel_relkind(indexrelid) != RELKIND_INDEX)
- continue;
+ if (rel_is_index)
+ {
+ /* consider only leaf indexes */
+ if (get_rel_relkind(inhoid) != RELKIND_INDEX)
+ continue;
+
+ inhrelid = IndexGetRelation(inhoid, false);
+ inhindid = inhoid;
+ }
+ else
+ {
+ /* consider only leaf relations */
+ if (get_rel_relkind(inhoid) != RELKIND_RELATION)
+ continue;
+
+ inhrelid = inhoid;
+ inhindid = InvalidOid;
+ }
/*
* It's possible that the user does not have privileges to CLUSTER the
@@ -1720,15 +1831,15 @@ get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)
* table. We skip any partitions which the user is not permitted to
* CLUSTER.
*/
- if (!cluster_is_permitted_for_relation(relid, GetUserId()))
+ if (!cluster_is_permitted_for_relation(cmd, inhrelid, GetUserId()))
continue;
/* Use a permanent memory context for the result list */
old_context = MemoryContextSwitchTo(cluster_context);
rtc = (RelToCluster *) palloc(sizeof(RelToCluster));
- rtc->tableOid = relid;
- rtc->indexOid = indexrelid;
+ rtc->tableOid = inhrelid;
+ rtc->indexOid = inhindid;
rtcs = lappend(rtcs, rtc);
MemoryContextSwitchTo(old_context);
@@ -1742,13 +1853,148 @@ get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)
* function emits a WARNING.
*/
static bool
-cluster_is_permitted_for_relation(Oid relid, Oid userid)
+cluster_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid)
{
if (pg_class_aclcheck(relid, userid, ACL_MAINTAIN) == ACLCHECK_OK)
return true;
+ Assert(cmd == REPACK_COMMAND_CLUSTER || cmd == REPACK_COMMAND_REPACK);
ereport(WARNING,
- (errmsg("permission denied to cluster \"%s\", skipping it",
- get_rel_name(relid))));
+ errmsg("permission denied to execute %s on \"%s\", skipping it",
+ cmd == REPACK_COMMAND_CLUSTER ? "CLUSTER" : "REPACK",
+ get_rel_name(relid)));
+
return false;
}
+
+
+/*
+ * Given a RepackStmt with an indicated relation name, resolve the relation
+ * name, obtain lock on it, then determine what to do based on the relation
+ * type: if it's not a partitioned table, repack it as indicated (using an
+ * existing clustered index, or following the indicated index), and return
+ * NULL.
+ *
+ * On the other hand, if the table is partitioned, do nothing further and
+ * instead return the opened relcache entry, so that caller can process the
+ * partitions using the multiple-table handling code. The index name is not
+ * resolve in this case.
+ */
+static Relation
+process_single_relation(RepackStmt *stmt, ClusterParams *params)
+{
+ Relation rel;
+ Oid tableOid;
+
+ Assert(stmt->relation != NULL);
+ Assert(stmt->command == REPACK_COMMAND_CLUSTER ||
+ stmt->command == REPACK_COMMAND_REPACK);
+
+ /*
+ * Find, lock, and check permissions on the table. We obtain
+ * AccessExclusiveLock right away to avoid lock-upgrade hazard in the
+ * single-transaction case.
+ */
+ tableOid = RangeVarGetRelidExtended(stmt->relation,
+ AccessExclusiveLock,
+ 0,
+ RangeVarCallbackMaintainsTable,
+ NULL);
+ rel = table_open(tableOid, NoLock);
+
+ /*
+ * Reject clustering a remote temp table ... their local buffer manager is
+ * not going to cope.
+ */
+ if (RELATION_IS_OTHER_TEMP(rel))
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot execute %s on temporary tables of other sessions",
+ RepackCommandAsString(stmt->command)));
+ }
+
+ /*
+ * For partitioned tables, let caller handle this. Otherwise, process it
+ * here and we're done.
+ */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ return rel;
+ else
+ {
+ Oid indexOid;
+
+ indexOid = determine_clustered_index(rel, stmt->usingindex,
+ stmt->indexname);
+ if (OidIsValid(indexOid))
+ check_index_is_clusterable(rel, indexOid, AccessExclusiveLock);
+ cluster_rel(stmt->command, stmt->usingindex, rel, indexOid, params);
+
+ /* Do an analyze, if requested */
+ if (params->options & CLUOPT_ANALYZE)
+ {
+ VacuumParams vac_params = {0};
+
+ vac_params.options |= VACOPT_ANALYZE;
+ if (params->options & CLUOPT_VERBOSE)
+ vac_params.options |= VACOPT_VERBOSE;
+ analyze_rel(RelationGetRelid(rel), NULL, vac_params, NIL, true,
+ NULL);
+ }
+
+ return NULL;
+ }
+}
+
+/*
+ * Given a relation and the usingindex/indexname options in a
+ * REPACK USING INDEX or CLUSTER command, return the OID of the index to use
+ * for clustering the table.
+ *
+ * Caller must hold lock on the relation so that the set of indexes doesn't
+ * change, and must call check_index_is_clusterable.
+ */
+static Oid
+determine_clustered_index(Relation rel, bool usingindex, const char *indexname)
+{
+ Oid indexOid;
+
+ if (indexname == NULL && usingindex)
+ {
+ ListCell *lc;
+
+ /* Find an index with indisclustered set, or report error */
+ foreach(lc, RelationGetIndexList(rel))
+ {
+ indexOid = lfirst_oid(lc);
+
+ if (get_index_isclustered(indexOid))
+ break;
+ indexOid = InvalidOid;
+ }
+
+ if (!OidIsValid(indexOid))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("there is no previously clustered index for table \"%s\"",
+ RelationGetRelationName(rel)));
+ }
+ else if (indexname != NULL)
+ {
+ /*
+ * An index was specified; figure out its OID. It must be in the same
+ * namespace as the relation.
+ */
+ indexOid = get_relname_relid(indexname,
+ rel->rd_rel->relnamespace);
+ if (!OidIsValid(indexOid))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("index \"%s\" for table \"%s\" does not exist",
+ indexname, RelationGetRelationName(rel)));
+ }
+ else
+ indexOid = InvalidOid;
+
+ return indexOid;
+}
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 733ef40ae7c..8863ad0e8bd 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -2287,7 +2287,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params,
cluster_params.options |= CLUOPT_VERBOSE;
/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
- cluster_rel(rel, InvalidOid, &cluster_params);
+ cluster_rel(REPACK_COMMAND_VACUUMFULL, false, rel, InvalidOid,
+ &cluster_params);
/* cluster_rel closes the relation, but keeps lock */
rel = NULL;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index db43034b9db..f9152728021 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -280,7 +280,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
AlterCompositeTypeStmt AlterUserMappingStmt
AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt AlterStatsStmt
AlterDefaultPrivilegesStmt DefACLAction
- AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt
+ AnalyzeStmt CallStmt ClosePortalStmt CommentStmt
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt
CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt
@@ -297,7 +297,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
- RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt RevokeRoleStmt
+ RemoveFuncStmt RemoveOperStmt RenameStmt RepackStmt ReturnStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
SecLabelStmt SelectStmt TransactionStmt TransactionStmtLegacy TruncateStmt
UnlistenStmt UpdateStmt VacuumStmt
@@ -316,7 +316,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> opt_single_name
%type <list> opt_qualified_name
-%type <boolean> opt_concurrently
+%type <boolean> opt_concurrently opt_usingindex
%type <dbehavior> opt_drop_behavior
%type <list> opt_utility_option_list
%type <list> utility_option_list
@@ -763,7 +763,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
QUOTE QUOTES
RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
- REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
+ REFRESH REINDEX RELATIVE_P RELEASE RENAME REPACK REPEATABLE REPLACE REPLICA
RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
@@ -1025,7 +1025,6 @@ stmt:
| CallStmt
| CheckPointStmt
| ClosePortalStmt
- | ClusterStmt
| CommentStmt
| ConstraintsSetStmt
| CopyStmt
@@ -1099,6 +1098,7 @@ stmt:
| RemoveFuncStmt
| RemoveOperStmt
| RenameStmt
+ | RepackStmt
| RevokeStmt
| RevokeRoleStmt
| RuleStmt
@@ -1135,6 +1135,11 @@ opt_concurrently:
| /*EMPTY*/ { $$ = false; }
;
+opt_usingindex:
+ USING INDEX { $$ = true; }
+ | /* EMPTY */ { $$ = false; }
+ ;
+
opt_drop_behavior:
CASCADE { $$ = DROP_CASCADE; }
| RESTRICT { $$ = DROP_RESTRICT; }
@@ -11912,38 +11917,91 @@ CreateConversionStmt:
/*****************************************************************************
*
* QUERY:
+ * REPACK [ (options) ] [ <qualified_name> [ USING INDEX <index_name> ] ]
+ *
+ * obsolete variants:
* CLUSTER (options) [ <qualified_name> [ USING <index_name> ] ]
* CLUSTER [VERBOSE] [ <qualified_name> [ USING <index_name> ] ]
* CLUSTER [VERBOSE] <index_name> ON <qualified_name> (for pre-8.3)
*
*****************************************************************************/
-ClusterStmt:
- CLUSTER '(' utility_option_list ')' qualified_name cluster_index_specification
+RepackStmt:
+ REPACK opt_utility_option_list qualified_name USING INDEX name
{
- ClusterStmt *n = makeNode(ClusterStmt);
+ RepackStmt *n = makeNode(RepackStmt);
+ n->command = REPACK_COMMAND_REPACK;
+ n->relation = $3;
+ n->indexname = $6;
+ n->usingindex = true;
+ n->params = $2;
+ $$ = (Node *) n;
+ }
+ | REPACK opt_utility_option_list qualified_name opt_usingindex
+ {
+ RepackStmt *n = makeNode(RepackStmt);
+
+ n->command = REPACK_COMMAND_REPACK;
+ n->relation = $3;
+ n->indexname = NULL;
+ n->usingindex = $4;
+ n->params = $2;
+ $$ = (Node *) n;
+ }
+ | REPACK '(' utility_option_list ')'
+ {
+ RepackStmt *n = makeNode(RepackStmt);
+
+ n->command = REPACK_COMMAND_REPACK;
+ n->relation = NULL;
+ n->indexname = NULL;
+ n->usingindex = false;
+ n->params = $3;
+ $$ = (Node *) n;
+ }
+ | REPACK opt_usingindex
+ {
+ RepackStmt *n = makeNode(RepackStmt);
+
+ n->command = REPACK_COMMAND_REPACK;
+ n->relation = NULL;
+ n->indexname = NULL;
+ n->usingindex = $2;
+ n->params = NIL;
+ $$ = (Node *) n;
+ }
+ | CLUSTER '(' utility_option_list ')' qualified_name cluster_index_specification
+ {
+ RepackStmt *n = makeNode(RepackStmt);
+
+ n->command = REPACK_COMMAND_CLUSTER;
n->relation = $5;
n->indexname = $6;
+ n->usingindex = true;
n->params = $3;
$$ = (Node *) n;
}
| CLUSTER opt_utility_option_list
{
- ClusterStmt *n = makeNode(ClusterStmt);
+ RepackStmt *n = makeNode(RepackStmt);
+ n->command = REPACK_COMMAND_CLUSTER;
n->relation = NULL;
n->indexname = NULL;
+ n->usingindex = true;
n->params = $2;
$$ = (Node *) n;
}
/* unparenthesized VERBOSE kept for pre-14 compatibility */
| CLUSTER opt_verbose qualified_name cluster_index_specification
{
- ClusterStmt *n = makeNode(ClusterStmt);
+ RepackStmt *n = makeNode(RepackStmt);
+ n->command = REPACK_COMMAND_CLUSTER;
n->relation = $3;
n->indexname = $4;
+ n->usingindex = true;
if ($2)
n->params = list_make1(makeDefElem("verbose", NULL, @2));
$$ = (Node *) n;
@@ -11951,20 +12009,24 @@ ClusterStmt:
/* unparenthesized VERBOSE kept for pre-17 compatibility */
| CLUSTER VERBOSE
{
- ClusterStmt *n = makeNode(ClusterStmt);
+ RepackStmt *n = makeNode(RepackStmt);
+ n->command = REPACK_COMMAND_CLUSTER;
n->relation = NULL;
n->indexname = NULL;
+ n->usingindex = true;
n->params = list_make1(makeDefElem("verbose", NULL, @2));
$$ = (Node *) n;
}
/* kept for pre-8.3 compatibility */
| CLUSTER opt_verbose name ON qualified_name
{
- ClusterStmt *n = makeNode(ClusterStmt);
+ RepackStmt *n = makeNode(RepackStmt);
+ n->command = REPACK_COMMAND_CLUSTER;
n->relation = $5;
n->indexname = $3;
+ n->usingindex = true;
if ($2)
n->params = list_make1(makeDefElem("verbose", NULL, @2));
$$ = (Node *) n;
@@ -17960,6 +18022,7 @@ unreserved_keyword:
| RELATIVE_P
| RELEASE
| RENAME
+ | REPACK
| REPEATABLE
| REPLACE
| REPLICA
@@ -18592,6 +18655,7 @@ bare_label_keyword:
| RELATIVE_P
| RELEASE
| RENAME
+ | REPACK
| REPEATABLE
| REPLACE
| REPLICA
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 5f442bc3bd4..cf6db581007 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -277,9 +277,9 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
return COMMAND_OK_IN_RECOVERY | COMMAND_OK_IN_READ_ONLY_TXN;
}
- case T_ClusterStmt:
case T_ReindexStmt:
case T_VacuumStmt:
+ case T_RepackStmt:
{
/*
* These commands write WAL, so they're not strictly
@@ -854,14 +854,14 @@ standard_ProcessUtility(PlannedStmt *pstmt,
ExecuteCallStmt(castNode(CallStmt, parsetree), params, isAtomicContext, dest);
break;
- case T_ClusterStmt:
- cluster(pstate, (ClusterStmt *) parsetree, isTopLevel);
- break;
-
case T_VacuumStmt:
ExecVacuum(pstate, (VacuumStmt *) parsetree, isTopLevel);
break;
+ case T_RepackStmt:
+ ExecRepack(pstate, (RepackStmt *) parsetree, isTopLevel);
+ break;
+
case T_ExplainStmt:
ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest);
break;
@@ -2851,10 +2851,6 @@ CreateCommandTag(Node *parsetree)
tag = CMDTAG_CALL;
break;
- case T_ClusterStmt:
- tag = CMDTAG_CLUSTER;
- break;
-
case T_VacuumStmt:
if (((VacuumStmt *) parsetree)->is_vacuumcmd)
tag = CMDTAG_VACUUM;
@@ -2862,6 +2858,10 @@ CreateCommandTag(Node *parsetree)
tag = CMDTAG_ANALYZE;
break;
+ case T_RepackStmt:
+ tag = CMDTAG_REPACK;
+ break;
+
case T_ExplainStmt:
tag = CMDTAG_EXPLAIN;
break;
@@ -3499,7 +3499,7 @@ GetCommandLogLevel(Node *parsetree)
lev = LOGSTMT_ALL;
break;
- case T_ClusterStmt:
+ case T_RepackStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c756c2bebaa..a1e10e8c2f6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -268,6 +268,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
cmdtype = PROGRESS_COMMAND_ANALYZE;
else if (pg_strcasecmp(cmd, "CLUSTER") == 0)
cmdtype = PROGRESS_COMMAND_CLUSTER;
+ else if (pg_strcasecmp(cmd, "REPACK") == 0)
+ cmdtype = PROGRESS_COMMAND_REPACK;
else if (pg_strcasecmp(cmd, "CREATE INDEX") == 0)
cmdtype = PROGRESS_COMMAND_CREATE_INDEX;
else if (pg_strcasecmp(cmd, "BASEBACKUP") == 0)
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8b10f2313f3..59ff6e0923b 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1247,7 +1247,7 @@ static const char *const sql_commands[] = {
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN",
"FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LISTEN", "LOAD", "LOCK",
"MERGE INTO", "MOVE", "NOTIFY", "PREPARE",
- "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE",
+ "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", "REPACK",
"RESET", "REVOKE", "ROLLBACK",
"SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START",
"TABLE", "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", "VALUES", "WITH",
@@ -4997,6 +4997,37 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
}
+/* REPACK */
+ else if (Matches("REPACK"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_clusterables);
+ else if (Matches("REPACK", "(*)"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_clusterables);
+ /* If we have REPACK <sth>, then add "USING INDEX" */
+ else if (Matches("REPACK", MatchAnyExcept("(")))
+ COMPLETE_WITH("USING INDEX");
+ /* If we have REPACK (*) <sth>, then add "USING INDEX" */
+ else if (Matches("REPACK", "(*)", MatchAny))
+ COMPLETE_WITH("USING INDEX");
+ /* If we have REPACK <sth> USING, then add the index as well */
+ else if (Matches("REPACK", MatchAny, "USING", "INDEX"))
+ {
+ set_completion_reference(prev3_wd);
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_index_of_table);
+ }
+ else if (HeadMatches("REPACK", "(*") &&
+ !HeadMatches("REPACK", "(*)"))
+ {
+ /*
+ * This fires if we're in an unfinished parenthesized option list.
+ * get_previous_words treats a completed parenthesized option list as
+ * one word, so the above test is correct.
+ */
+ if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
+ COMPLETE_WITH("VERBOSE");
+ else if (TailMatches("VERBOSE"))
+ COMPLETE_WITH("ON", "OFF");
+ }
+
/* SECURITY LABEL */
else if (Matches("SECURITY"))
COMPLETE_WITH("LABEL");
diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile
index 019ca06455d..f0c1bd4175c 100644
--- a/src/bin/scripts/Makefile
+++ b/src/bin/scripts/Makefile
@@ -16,7 +16,7 @@ subdir = src/bin/scripts
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
-PROGRAMS = createdb createuser dropdb dropuser clusterdb vacuumdb reindexdb pg_isready
+PROGRAMS = createdb createuser dropdb dropuser clusterdb vacuumdb reindexdb pg_isready pg_repackdb
override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS)
LDFLAGS_INTERNAL += -L$(top_builddir)/src/fe_utils -lpgfeutils $(libpq_pgport)
@@ -31,6 +31,7 @@ clusterdb: clusterdb.o common.o $(WIN32RES) | submake-libpq submake-libpgport su
vacuumdb: vacuumdb.o vacuuming.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils
reindexdb: reindexdb.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils
pg_isready: pg_isready.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils
+pg_repackdb: pg_repackdb.o vacuuming.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils
install: all installdirs
$(INSTALL_PROGRAM) createdb$(X) '$(DESTDIR)$(bindir)'/createdb$(X)
@@ -41,6 +42,7 @@ install: all installdirs
$(INSTALL_PROGRAM) vacuumdb$(X) '$(DESTDIR)$(bindir)'/vacuumdb$(X)
$(INSTALL_PROGRAM) reindexdb$(X) '$(DESTDIR)$(bindir)'/reindexdb$(X)
$(INSTALL_PROGRAM) pg_isready$(X) '$(DESTDIR)$(bindir)'/pg_isready$(X)
+ $(INSTALL_PROGRAM) pg_repackdb$(X) '$(DESTDIR)$(bindir)'/pg_repackdb$(X)
installdirs:
$(MKDIR_P) '$(DESTDIR)$(bindir)'
diff --git a/src/bin/scripts/meson.build b/src/bin/scripts/meson.build
index a4fed59d1c9..be573cae682 100644
--- a/src/bin/scripts/meson.build
+++ b/src/bin/scripts/meson.build
@@ -42,6 +42,7 @@ vacuuming_common = static_library('libvacuuming_common',
binaries = [
'vacuumdb',
+ 'pg_repackdb',
]
foreach binary : binaries
binary_sources = files('@[email protected]'.format(binary))
@@ -80,6 +81,7 @@ tests += {
't/100_vacuumdb.pl',
't/101_vacuumdb_all.pl',
't/102_vacuumdb_stages.pl',
+ 't/103_repackdb.pl',
't/200_connstr.pl',
],
},
diff --git a/src/bin/scripts/pg_repackdb.c b/src/bin/scripts/pg_repackdb.c
new file mode 100644
index 00000000000..23326372a77
--- /dev/null
+++ b/src/bin/scripts/pg_repackdb.c
@@ -0,0 +1,226 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_repackdb
+ * An utility to run REPACK
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * FIXME: this is missing a way to specify the index to use to repack one
+ * table, or whether to pass a WITH INDEX clause when multiple tables are
+ * used. Something like --index[=indexname]. Adding that bleeds into
+ * vacuuming.c as well.
+ *
+ * src/bin/scripts/pg_repackdb.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres_fe.h"
+
+#include <limits.h>
+
+#include "common.h"
+#include "common/logging.h"
+#include "fe_utils/option_utils.h"
+#include "vacuuming.h"
+
+static void help(const char *progname);
+void check_objfilter(void);
+
+int
+main(int argc, char *argv[])
+{
+ static struct option long_options[] = {
+ {"host", required_argument, NULL, 'h'},
+ {"port", required_argument, NULL, 'p'},
+ {"username", required_argument, NULL, 'U'},
+ {"no-password", no_argument, NULL, 'w'},
+ {"password", no_argument, NULL, 'W'},
+ {"echo", no_argument, NULL, 'e'},
+ {"quiet", no_argument, NULL, 'q'},
+ {"dbname", required_argument, NULL, 'd'},
+ {"all", no_argument, NULL, 'a'},
+ {"table", required_argument, NULL, 't'},
+ {"verbose", no_argument, NULL, 'v'},
+ {"jobs", required_argument, NULL, 'j'},
+ {"schema", required_argument, NULL, 'n'},
+ {"exclude-schema", required_argument, NULL, 'N'},
+ {"maintenance-db", required_argument, NULL, 2},
+ {NULL, 0, NULL, 0}
+ };
+
+ const char *progname;
+ int optindex;
+ int c;
+ const char *dbname = NULL;
+ const char *maintenance_db = NULL;
+ ConnParams cparams;
+ bool echo = false;
+ bool quiet = false;
+ vacuumingOptions vacopts;
+ SimpleStringList objects = {NULL, NULL};
+ int concurrentCons = 1;
+ int tbl_count = 0;
+
+ /* initialize options */
+ memset(&vacopts, 0, sizeof(vacopts));
+ vacopts.mode = MODE_REPACK;
+
+ /* the same for connection parameters */
+ memset(&cparams, 0, sizeof(cparams));
+ cparams.prompt_password = TRI_DEFAULT;
+
+ pg_logging_init(argv[0]);
+ progname = get_progname(argv[0]);
+ set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
+
+ handle_help_version_opts(argc, argv, progname, help);
+
+ while ((c = getopt_long(argc, argv, "ad:eh:j:n:N:p:qt:U:vwW",
+ long_options, &optindex)) != -1)
+ {
+ switch (c)
+ {
+ case 'a':
+ objfilter |= OBJFILTER_ALL_DBS;
+ break;
+ case 'd':
+ objfilter |= OBJFILTER_DATABASE;
+ dbname = pg_strdup(optarg);
+ break;
+ case 'e':
+ echo = true;
+ break;
+ case 'h':
+ cparams.pghost = pg_strdup(optarg);
+ break;
+ case 'j':
+ if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
+ &concurrentCons))
+ exit(1);
+ break;
+ case 'n':
+ objfilter |= OBJFILTER_SCHEMA;
+ simple_string_list_append(&objects, optarg);
+ break;
+ case 'N':
+ objfilter |= OBJFILTER_SCHEMA_EXCLUDE;
+ simple_string_list_append(&objects, optarg);
+ break;
+ case 'p':
+ cparams.pgport = pg_strdup(optarg);
+ break;
+ case 'q':
+ quiet = true;
+ break;
+ case 't':
+ objfilter |= OBJFILTER_TABLE;
+ simple_string_list_append(&objects, optarg);
+ tbl_count++;
+ break;
+ case 'U':
+ cparams.pguser = pg_strdup(optarg);
+ break;
+ case 'v':
+ vacopts.verbose = true;
+ break;
+ case 'w':
+ cparams.prompt_password = TRI_NO;
+ break;
+ case 'W':
+ cparams.prompt_password = TRI_YES;
+ break;
+ case 2:
+ maintenance_db = pg_strdup(optarg);
+ break;
+ default:
+ /* getopt_long already emitted a complaint */
+ pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+ exit(1);
+ }
+ }
+
+ /*
+ * Non-option argument specifies database name as long as it wasn't
+ * already specified with -d / --dbname
+ */
+ if (optind < argc && dbname == NULL)
+ {
+ objfilter |= OBJFILTER_DATABASE;
+ dbname = argv[optind];
+ optind++;
+ }
+
+ if (optind < argc)
+ {
+ pg_log_error("too many command-line arguments (first is \"%s\")",
+ argv[optind]);
+ pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+ exit(1);
+ }
+
+ /*
+ * Validate the combination of filters specified in the command-line
+ * options.
+ */
+ check_objfilter();
+
+ vacuuming_main(&cparams, dbname, maintenance_db, &vacopts, &objects,
+ false, tbl_count, concurrentCons,
+ progname, echo, quiet);
+ exit(0);
+}
+
+/*
+ * Verify that the filters used at command line are compatible.
+ */
+void
+check_objfilter(void)
+{
+ if ((objfilter & OBJFILTER_ALL_DBS) &&
+ (objfilter & OBJFILTER_DATABASE))
+ pg_fatal("cannot repack all databases and a specific one at the same time");
+
+ if ((objfilter & OBJFILTER_TABLE) &&
+ (objfilter & OBJFILTER_SCHEMA))
+ pg_fatal("cannot repack all tables in schema(s) and specific table(s) at the same time");
+
+ if ((objfilter & OBJFILTER_TABLE) &&
+ (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
+ pg_fatal("cannot repack specific table(s) and exclude schema(s) at the same time");
+
+ if ((objfilter & OBJFILTER_SCHEMA) &&
+ (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
+ pg_fatal("cannot repack all tables in schema(s) and exclude schema(s) at the same time");
+}
+
+static void
+help(const char *progname)
+{
+ printf(_("%s repacks a PostgreSQL database.\n\n"), progname);
+ printf(_("Usage:\n"));
+ printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
+ printf(_("\nOptions:\n"));
+ printf(_(" -a, --all repack all databases\n"));
+ printf(_(" -d, --dbname=DBNAME database to repack\n"));
+ printf(_(" -e, --echo show the commands being sent to the server\n"));
+ printf(_(" -j, --jobs=NUM use this many concurrent connections to repack\n"));
+ printf(_(" -n, --schema=SCHEMA repack tables in the specified schema(s) only\n"));
+ printf(_(" -N, --exclude-schema=SCHEMA do not repack tables in the specified schema(s)\n"));
+ printf(_(" -q, --quiet don't write any messages\n"));
+ printf(_(" -t, --table='TABLE' repack specific table(s) only\n"));
+ printf(_(" -v, --verbose write a lot of output\n"));
+ printf(_(" -V, --version output version information, then exit\n"));
+ printf(_(" -?, --help show this help, then exit\n"));
+ printf(_("\nConnection options:\n"));
+ printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
+ printf(_(" -p, --port=PORT database server port\n"));
+ printf(_(" -U, --username=USERNAME user name to connect as\n"));
+ printf(_(" -w, --no-password never prompt for password\n"));
+ printf(_(" -W, --password force password prompt\n"));
+ printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
+ printf(_("\nRead the description of the SQL command REPACK for details.\n"));
+ printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
+ printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
+}
diff --git a/src/bin/scripts/t/103_repackdb.pl b/src/bin/scripts/t/103_repackdb.pl
new file mode 100644
index 00000000000..51de4d7ab34
--- /dev/null
+++ b/src/bin/scripts/t/103_repackdb.pl
@@ -0,0 +1,24 @@
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+program_help_ok('pg_repackdb');
+program_version_ok('pg_repackdb');
+program_options_handling_ok('pg_repackdb');
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+$node->issues_sql_like(
+ [ 'pg_repackdb', 'postgres' ],
+ qr/statement: REPACK.*;/,
+ 'SQL REPACK run');
+
+
+done_testing();
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 9be37fcc45a..e07071c38ee 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -1,6 +1,6 @@
/*-------------------------------------------------------------------------
* vacuuming.c
- * Common routines for vacuumdb
+ * Common routines for vacuumdb and pg_repackdb
*
* Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
@@ -166,6 +166,14 @@ vacuum_one_database(ConnParams *cparams,
conn = connectDatabase(cparams, progname, echo, false, true);
+ if (vacopts->mode == MODE_REPACK && PQserverVersion(conn) < 190000)
+ {
+ /* XXX arguably, here we should use VACUUM FULL instead of failing */
+ PQfinish(conn);
+ pg_fatal("cannot use the \"%s\" command on server versions older than PostgreSQL %s",
+ "REPACK", "19");
+ }
+
if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
{
PQfinish(conn);
@@ -258,9 +266,15 @@ vacuum_one_database(ConnParams *cparams,
if (stage != ANALYZE_NO_STAGE)
printf(_("%s: processing database \"%s\": %s\n"),
progname, PQdb(conn), _(stage_messages[stage]));
- else
+ else if (vacopts->mode == MODE_VACUUM)
printf(_("%s: vacuuming database \"%s\"\n"),
progname, PQdb(conn));
+ else
+ {
+ Assert(vacopts->mode == MODE_REPACK);
+ printf(_("%s: repacking database \"%s\"\n"),
+ progname, PQdb(conn));
+ }
fflush(stdout);
}
@@ -350,7 +364,7 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
+ run_vacuum_command(free_slot->connection, vacopts, sql.data,
echo, tabname);
cell = cell->next;
@@ -363,7 +377,7 @@ vacuum_one_database(ConnParams *cparams,
}
/* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
- if (vacopts->skip_database_stats &&
+ if (vacopts->mode == MODE_VACUUM && vacopts->skip_database_stats &&
stage == ANALYZE_NO_STAGE)
{
const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
@@ -376,7 +390,7 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ run_vacuum_command(free_slot->connection, vacopts, cmd, echo, NULL);
if (!ParallelSlotsWaitCompletion(sa))
failed = true;
@@ -708,6 +722,12 @@ vacuum_all_databases(ConnParams *cparams,
int i;
conn = connectMaintenanceDatabase(cparams, progname, echo);
+ if (vacopts->mode == MODE_REPACK && PQserverVersion(conn) < 190000)
+ {
+ PQfinish(conn);
+ pg_fatal("cannot use the \"%s\" command on server versions older than PostgreSQL %s",
+ "REPACK", "19");
+ }
result = executeQuery(conn,
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
echo);
@@ -761,7 +781,7 @@ vacuum_all_databases(ConnParams *cparams,
}
/*
- * Construct a vacuum/analyze command to run based on the given
+ * Construct a vacuum/analyze/repack command to run based on the given
* options, in the given string buffer, which may contain previous garbage.
*
* The table name used must be already properly quoted. The command generated
@@ -777,7 +797,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
resetPQExpBuffer(sql);
- if (vacopts->analyze_only)
+ if (vacopts->mode == MODE_REPACK)
+ {
+ appendPQExpBufferStr(sql, "REPACK");
+ if (vacopts->verbose)
+ appendPQExpBufferStr(sql, " (VERBOSE)");
+ }
+ else if (vacopts->analyze_only)
{
appendPQExpBufferStr(sql, "ANALYZE");
@@ -938,8 +964,8 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
* Any errors during command execution are reported to stderr.
*/
void
-run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table)
+run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+ const char *sql, bool echo, const char *table)
{
bool status;
@@ -952,13 +978,21 @@ run_vacuum_command(PGconn *conn, const char *sql, bool echo,
{
if (table)
{
- pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
- table, PQdb(conn), PQerrorMessage(conn));
+ if (vacopts->mode == MODE_VACUUM)
+ pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
+ table, PQdb(conn), PQerrorMessage(conn));
+ else
+ pg_log_error("repacking of table \"%s\" in database \"%s\" failed: %s",
+ table, PQdb(conn), PQerrorMessage(conn));
}
else
{
- pg_log_error("vacuuming of database \"%s\" failed: %s",
- PQdb(conn), PQerrorMessage(conn));
+ if (vacopts->mode == MODE_VACUUM)
+ pg_log_error("vacuuming of database \"%s\" failed: %s",
+ PQdb(conn), PQerrorMessage(conn));
+ else
+ pg_log_error("repacking of database \"%s\" failed: %s",
+ PQdb(conn), PQerrorMessage(conn));
}
}
}
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index d3f000840fa..154bc9925c0 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -17,6 +17,12 @@
#include "fe_utils/connect_utils.h"
#include "fe_utils/simple_list.h"
+typedef enum
+{
+ MODE_VACUUM,
+ MODE_REPACK
+} RunMode;
+
/* For analyze-in-stages mode */
#define ANALYZE_NO_STAGE -1
#define ANALYZE_NUM_STAGES 3
@@ -24,6 +30,7 @@
/* vacuum options controlled by user flags */
typedef struct vacuumingOptions
{
+ RunMode mode;
bool analyze_only;
bool verbose;
bool and_analyze;
@@ -87,8 +94,8 @@ extern void vacuum_all_databases(ConnParams *cparams,
extern void prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
vacuumingOptions *vacopts, const char *table);
-extern void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table);
+extern void run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+ const char *sql, bool echo, const char *table);
extern char *escape_quotes(const char *src);
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index 60088a64cbb..890998d84bb 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -24,6 +24,7 @@
#define CLUOPT_RECHECK 0x02 /* recheck relation state */
#define CLUOPT_RECHECK_ISCLUSTERED 0x04 /* recheck relation state for
* indisclustered */
+#define CLUOPT_ANALYZE 0x08 /* do an ANALYZE */
/* options for CLUSTER */
typedef struct ClusterParams
@@ -31,8 +32,11 @@ typedef struct ClusterParams
bits32 options; /* bitmask of CLUOPT_* */
} ClusterParams;
-extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel);
-extern void cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params);
+
+extern void ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel);
+
+extern void cluster_rel(RepackCommand command, bool usingindex,
+ Relation OldHeap, Oid indexOid, ClusterParams *params);
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index 1cde4bd9bcf..5b6639c114c 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -56,24 +56,51 @@
#define PROGRESS_ANALYZE_PHASE_COMPUTE_EXT_STATS 4
#define PROGRESS_ANALYZE_PHASE_FINALIZE_ANALYZE 5
-/* Progress parameters for cluster */
-#define PROGRESS_CLUSTER_COMMAND 0
-#define PROGRESS_CLUSTER_PHASE 1
-#define PROGRESS_CLUSTER_INDEX_RELID 2
-#define PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED 3
-#define PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN 4
-#define PROGRESS_CLUSTER_TOTAL_HEAP_BLKS 5
-#define PROGRESS_CLUSTER_HEAP_BLKS_SCANNED 6
-#define PROGRESS_CLUSTER_INDEX_REBUILD_COUNT 7
+/*
+ * Progress parameters for REPACK.
+ *
+ * Note: Since REPACK shares some code with CLUSTER, these values are also
+ * used by CLUSTER. (CLUSTER is now deprecated, so it makes little sense to
+ * introduce a separate set of constants.)
+ */
+#define PROGRESS_REPACK_COMMAND 0
+#define PROGRESS_REPACK_PHASE 1
+#define PROGRESS_REPACK_INDEX_RELID 2
+#define PROGRESS_REPACK_HEAP_TUPLES_SCANNED 3
+#define PROGRESS_REPACK_HEAP_TUPLES_WRITTEN 4
+#define PROGRESS_REPACK_TOTAL_HEAP_BLKS 5
+#define PROGRESS_REPACK_HEAP_BLKS_SCANNED 6
+#define PROGRESS_REPACK_INDEX_REBUILD_COUNT 7
-/* Phases of cluster (as advertised via PROGRESS_CLUSTER_PHASE) */
-#define PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP 1
-#define PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP 2
-#define PROGRESS_CLUSTER_PHASE_SORT_TUPLES 3
-#define PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP 4
-#define PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES 5
-#define PROGRESS_CLUSTER_PHASE_REBUILD_INDEX 6
-#define PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP 7
+/*
+ * Phases of repack (as advertised via PROGRESS_REPACK_PHASE).
+ */
+#define PROGRESS_REPACK_PHASE_SEQ_SCAN_HEAP 1
+#define PROGRESS_REPACK_PHASE_INDEX_SCAN_HEAP 2
+#define PROGRESS_REPACK_PHASE_SORT_TUPLES 3
+#define PROGRESS_REPACK_PHASE_WRITE_NEW_HEAP 4
+#define PROGRESS_REPACK_PHASE_SWAP_REL_FILES 5
+#define PROGRESS_REPACK_PHASE_REBUILD_INDEX 6
+#define PROGRESS_REPACK_PHASE_FINAL_CLEANUP 7
+
+/*
+ * Commands of PROGRESS_REPACK
+ *
+ * Currently we only have one command, so the PROGRESS_REPACK_COMMAND
+ * parameter is not necessary. However it makes cluster.c simpler if we have
+ * the same set of parameters for CLUSTER and REPACK - see the note on REPACK
+ * parameters above.
+ */
+#define PROGRESS_REPACK_COMMAND_REPACK 1
+
+/*
+ * Progress parameters for cluster.
+ *
+ * Although we need to report REPACK and CLUSTER in separate views, the
+ * parameters and phases of CLUSTER are a subset of those of REPACK. Therefore
+ * we just use the appropriate values defined for REPACK above instead of
+ * defining a separate set of constants here.
+ */
/* Commands of PROGRESS_CLUSTER */
#define PROGRESS_CLUSTER_COMMAND_CLUSTER 1
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..fcc25a0c592 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3949,16 +3949,26 @@ typedef struct AlterSystemStmt
} AlterSystemStmt;
/* ----------------------
- * Cluster Statement (support pbrown's cluster index implementation)
+ * Repack Statement
* ----------------------
*/
-typedef struct ClusterStmt
+typedef enum RepackCommand
+{
+ REPACK_COMMAND_CLUSTER,
+ REPACK_COMMAND_REPACK,
+ REPACK_COMMAND_VACUUMFULL,
+} RepackCommand;
+
+typedef struct RepackStmt
{
NodeTag type;
- RangeVar *relation; /* relation being indexed, or NULL if all */
- char *indexname; /* original index defined */
+ RepackCommand command; /* type of command being run */
+ RangeVar *relation; /* relation being repacked */
+ char *indexname; /* order tuples by this index */
+ bool usingindex; /* whether USING INDEX is specified */
List *params; /* list of DefElem nodes */
-} ClusterStmt;
+} RepackStmt;
+
/* ----------------------
* Vacuum and Analyze Statements
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a1..22559369e2c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -374,6 +374,7 @@ PG_KEYWORD("reindex", REINDEX, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("relative", RELATIVE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("release", RELEASE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("repack", REPACK, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index d250a714d59..cceb312f2b3 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -196,6 +196,7 @@ PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false)
PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false)
PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
+PG_CMDTAG(CMDTAG_REPACK, "REPACK", false, false, false)
PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false)
PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false)
PG_CMDTAG(CMDTAG_REVOKE_ROLE, "REVOKE ROLE", false, false, false)
diff --git a/src/include/utils/backend_progress.h b/src/include/utils/backend_progress.h
index dda813ab407..e69e366dcdc 100644
--- a/src/include/utils/backend_progress.h
+++ b/src/include/utils/backend_progress.h
@@ -28,6 +28,7 @@ typedef enum ProgressCommandType
PROGRESS_COMMAND_CREATE_INDEX,
PROGRESS_COMMAND_BASEBACKUP,
PROGRESS_COMMAND_COPY,
+ PROGRESS_COMMAND_REPACK,
} ProgressCommandType;
#define PGSTAT_NUM_PROGRESS_PARAM 20
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index 4d40a6809ab..5256628b51d 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -254,6 +254,63 @@ ORDER BY 1;
clstr_tst_pkey
(3 rows)
+-- REPACK handles individual tables identically to CLUSTER, but it's worth
+-- checking if it handles table hierarchies identically as well.
+REPACK clstr_tst USING INDEX clstr_tst_c;
+-- Verify that inheritance link still works
+INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table 2');
+SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
+ a | b | c | substring | length
+----+-----+------------------+--------------------------------+--------
+ 10 | 14 | catorce | |
+ 18 | 5 | cinco | |
+ 9 | 4 | cuatro | |
+ 26 | 19 | diecinueve | |
+ 12 | 18 | dieciocho | |
+ 30 | 16 | dieciseis | |
+ 24 | 17 | diecisiete | |
+ 2 | 10 | diez | |
+ 23 | 12 | doce | |
+ 11 | 2 | dos | |
+ 25 | 9 | nueve | |
+ 31 | 8 | ocho | |
+ 1 | 11 | once | |
+ 28 | 15 | quince | |
+ 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
+ 29 | 7 | siete | |
+ 15 | 13 | trece | |
+ 22 | 30 | treinta | |
+ 17 | 32 | treinta y dos | |
+ 3 | 31 | treinta y uno | |
+ 5 | 3 | tres | |
+ 20 | 1 | uno | |
+ 6 | 20 | veinte | |
+ 14 | 25 | veinticinco | |
+ 21 | 24 | veinticuatro | |
+ 4 | 22 | veintidos | |
+ 19 | 29 | veintinueve | |
+ 16 | 28 | veintiocho | |
+ 27 | 26 | veintiseis | |
+ 13 | 27 | veintisiete | |
+ 7 | 23 | veintitres | |
+ 8 | 21 | veintiuno | |
+ 0 | 100 | in child table | |
+ 0 | 100 | in child table 2 | |
+(34 rows)
+
+-- Verify that foreign key link still works
+INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
+ERROR: insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con"
+DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s".
+SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
+ORDER BY 1;
+ conname
+----------------------
+ clstr_tst_a_not_null
+ clstr_tst_con
+ clstr_tst_pkey
+(3 rows)
+
SELECT relname, relkind,
EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
@@ -381,6 +438,35 @@ SELECT * FROM clstr_1;
2
(2 rows)
+-- REPACK w/o argument performs no ordering, so we can only check which tables
+-- have the relfilenode changed.
+RESET SESSION AUTHORIZATION;
+CREATE TEMP TABLE relnodes_old AS
+(SELECT relname, relfilenode
+FROM pg_class
+WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3'));
+SET SESSION AUTHORIZATION regress_clstr_user;
+SET client_min_messages = ERROR; -- order of "skipping" warnings may vary
+REPACK;
+RESET client_min_messages;
+RESET SESSION AUTHORIZATION;
+CREATE TEMP TABLE relnodes_new AS
+(SELECT relname, relfilenode
+FROM pg_class
+WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3'));
+-- Do the actual comparison. Unlike CLUSTER, clstr_3 should have been
+-- processed because there is nothing like clustering index here.
+SELECT o.relname FROM relnodes_old o
+JOIN relnodes_new n ON o.relname = n.relname
+WHERE o.relfilenode <> n.relfilenode
+ORDER BY o.relname;
+ relname
+---------
+ clstr_1
+ clstr_3
+(2 rows)
+
+SET SESSION AUTHORIZATION regress_clstr_user;
-- Test MVCC-safety of cluster. There isn't much we can do to verify the
-- results with a single backend...
CREATE TABLE clustertest (key int PRIMARY KEY);
@@ -495,6 +581,43 @@ ALTER TABLE clstrpart SET WITHOUT CLUSTER;
ERROR: cannot mark index clustered in partitioned table
ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
ERROR: cannot mark index clustered in partitioned table
+-- Check that REPACK sets new relfilenodes: it should process exactly the same
+-- tables as CLUSTER did.
+DROP TABLE old_cluster_info;
+DROP TABLE new_cluster_info;
+CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+REPACK clstrpart USING INDEX clstrpart_idx;
+CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
+ relname | level | relkind | ?column?
+-------------+-------+---------+----------
+ clstrpart | 0 | p | t
+ clstrpart1 | 1 | p | t
+ clstrpart11 | 2 | r | f
+ clstrpart12 | 2 | p | t
+ clstrpart2 | 1 | r | f
+ clstrpart3 | 1 | p | t
+ clstrpart33 | 2 | r | f
+(7 rows)
+
+-- And finally the same for REPACK w/o index.
+DROP TABLE old_cluster_info;
+DROP TABLE new_cluster_info;
+CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+REPACK clstrpart;
+CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
+ relname | level | relkind | ?column?
+-------------+-------+---------+----------
+ clstrpart | 0 | p | t
+ clstrpart1 | 1 | p | t
+ clstrpart11 | 2 | r | f
+ clstrpart12 | 2 | p | t
+ clstrpart2 | 1 | r | f
+ clstrpart3 | 1 | p | t
+ clstrpart33 | 2 | r | f
+(7 rows)
+
DROP TABLE clstrpart;
-- Ownership of partitions is checked
CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i);
@@ -513,7 +636,7 @@ CREATE TEMP TABLE ptnowner_oldnodes AS
JOIN pg_class AS c ON c.oid=tree.relid;
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
-WARNING: permission denied to cluster "ptnowner2", skipping it
+WARNING: permission denied to execute CLUSTER on "ptnowner2", skipping it
RESET SESSION AUTHORIZATION;
SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a
JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 35e8aad7701..3a1d1d28282 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2071,6 +2071,29 @@ pg_stat_progress_create_index| SELECT s.pid,
s.param15 AS partitions_done
FROM (pg_stat_get_progress_info('CREATE INDEX'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
+pg_stat_progress_repack| SELECT s.pid,
+ s.datid,
+ d.datname,
+ s.relid,
+ CASE s.param2
+ WHEN 0 THEN 'initializing'::text
+ WHEN 1 THEN 'seq scanning heap'::text
+ WHEN 2 THEN 'index scanning heap'::text
+ WHEN 3 THEN 'sorting tuples'::text
+ WHEN 4 THEN 'writing new heap'::text
+ WHEN 5 THEN 'swapping relation files'::text
+ WHEN 6 THEN 'rebuilding index'::text
+ WHEN 7 THEN 'performing final cleanup'::text
+ ELSE NULL::text
+ END AS phase,
+ (s.param3)::oid AS repack_index_relid,
+ s.param4 AS heap_tuples_scanned,
+ s.param5 AS heap_tuples_written,
+ s.param6 AS heap_blks_total,
+ s.param7 AS heap_blks_scanned,
+ s.param8 AS index_rebuild_count
+ FROM (pg_stat_get_progress_info('REPACK'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
+ LEFT JOIN pg_database d ON ((s.datid = d.oid)));
pg_stat_progress_vacuum| SELECT s.pid,
s.datid,
d.datname,
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index b7115f86104..cfcc3dc9761 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -76,6 +76,19 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
ORDER BY 1;
+-- REPACK handles individual tables identically to CLUSTER, but it's worth
+-- checking if it handles table hierarchies identically as well.
+REPACK clstr_tst USING INDEX clstr_tst_c;
+
+-- Verify that inheritance link still works
+INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table 2');
+SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
+
+-- Verify that foreign key link still works
+INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
+
+SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
+ORDER BY 1;
SELECT relname, relkind,
EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
@@ -159,6 +172,34 @@ INSERT INTO clstr_1 VALUES (1);
CLUSTER clstr_1;
SELECT * FROM clstr_1;
+-- REPACK w/o argument performs no ordering, so we can only check which tables
+-- have the relfilenode changed.
+RESET SESSION AUTHORIZATION;
+CREATE TEMP TABLE relnodes_old AS
+(SELECT relname, relfilenode
+FROM pg_class
+WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3'));
+
+SET SESSION AUTHORIZATION regress_clstr_user;
+SET client_min_messages = ERROR; -- order of "skipping" warnings may vary
+REPACK;
+RESET client_min_messages;
+
+RESET SESSION AUTHORIZATION;
+CREATE TEMP TABLE relnodes_new AS
+(SELECT relname, relfilenode
+FROM pg_class
+WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3'));
+
+-- Do the actual comparison. Unlike CLUSTER, clstr_3 should have been
+-- processed because there is nothing like clustering index here.
+SELECT o.relname FROM relnodes_old o
+JOIN relnodes_new n ON o.relname = n.relname
+WHERE o.relfilenode <> n.relfilenode
+ORDER BY o.relname;
+
+SET SESSION AUTHORIZATION regress_clstr_user;
+
-- Test MVCC-safety of cluster. There isn't much we can do to verify the
-- results with a single backend...
@@ -229,6 +270,24 @@ SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM o
CLUSTER clstrpart;
ALTER TABLE clstrpart SET WITHOUT CLUSTER;
ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
+
+-- Check that REPACK sets new relfilenodes: it should process exactly the same
+-- tables as CLUSTER did.
+DROP TABLE old_cluster_info;
+DROP TABLE new_cluster_info;
+CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+REPACK clstrpart USING INDEX clstrpart_idx;
+CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
+
+-- And finally the same for REPACK w/o index.
+DROP TABLE old_cluster_info;
+DROP TABLE new_cluster_info;
+CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+REPACK clstrpart;
+CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
+
DROP TABLE clstrpart;
-- Ownership of partitions is checked
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a13e8162890..98242e25432 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2537,6 +2537,8 @@ ReorderBufferTupleCidKey
ReorderBufferUpdateProgressTxnCB
ReorderTuple
RepOriginId
+RepackCommand
+RepackStmt
ReparameterizeForeignPathByChild_function
ReplaceVarsFromTargetList_context
ReplaceVarsNoMatchOption
@@ -2603,6 +2605,7 @@ RtlNtStatusToDosError_t
RuleInfo
RuleLock
RuleStmt
+RunMode
RunningTransactions
RunningTransactionsData
SASLStatus
--
2.39.5
--3q6txozlgl6t37td
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
filename="v20-0003-Refactor-index_concurrently_create_copy-for-use-.patch"
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2025-07-26 17:57 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-13 07:58 [PATCH v49 1/7] sequential scan for dshash Kyotaro Horiguchi <[email protected]>
2022-10-31 03:52 Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-10-31 14:12 ` Re: Prefetch the next tuple's memory during seqscans Aleksander Alekseev <[email protected]>
2022-10-31 22:17 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-01 11:08 ` Re: Prefetch the next tuple's memory during seqscans Andy Fan <[email protected]>
2022-11-01 11:42 ` Re: Prefetch the next tuple's memory during seqscans Thomas Munro <[email protected]>
2022-11-23 16:03 ` Re: Prefetch the next tuple's memory during seqscans Bruce Momjian <[email protected]>
2022-11-23 16:14 ` Re: Prefetch the next tuple's memory during seqscans Bruce Momjian <[email protected]>
2022-11-01 11:50 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-03 09:09 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
2022-11-22 22:00 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-23 01:35 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
2022-11-02 03:00 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 17:25 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-02 20:53 ` Re: Prefetch the next tuple's memory during seqscans Andres Freund <[email protected]>
2022-11-22 21:58 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-23 07:29 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
2022-11-23 07:44 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-23 08:27 ` Re: Prefetch the next tuple's memory during seqscans sirisha chamarthi <[email protected]>
2022-11-23 13:15 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-11-24 09:25 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2022-12-01 05:17 ` Re: Prefetch the next tuple's memory during seqscans John Naylor <[email protected]>
2022-12-02 01:47 ` Re: Prefetch the next tuple's memory during seqscans David Rowley <[email protected]>
2025-07-26 17:57 [PATCH v20 2/6] Add REPACK command Álvaro Herrera <[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