diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2023-01-06 14:17:25 -0500 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2023-01-06 14:17:25 -0500 |
| commit | a46a7011b27188af526047a111969f257aaf4db8 (patch) | |
| tree | 816e22b0b77bcc10da44ed043eef2879615bc399 /src/bin/psql/tab-complete.c | |
| parent | cd4b2334db4980bbf86a8ba1d446db17e62ca342 (diff) | |
Add options to control whether VACUUM runs vac_update_datfrozenxid.
VACUUM normally ends by running vac_update_datfrozenxid(), which
requires a scan of pg_class. Therefore, if one attempts to vacuum a
database one table at a time --- as vacuumdb has done since v12 ---
we will spend O(N^2) time in vac_update_datfrozenxid(). That causes
serious performance problems in databases with tens of thousands of
tables, and indeed the effect is measurable with only a few hundred.
To add insult to injury, only one process can run
vac_update_datfrozenxid at the same time per DB, so this behavior
largely defeats vacuumdb's -j option.
Hence, invent options SKIP_DATABASE_STATS and ONLY_DATABASE_STATS
to allow applications to postpone vac_update_datfrozenxid() until the
end of a series of VACUUM requests, and teach vacuumdb to use them.
Per bug #17717 from Gunnar L. Sadly, this answer doesn't seem
like something we'd consider back-patching, so the performance
problem will remain in v12-v15.
Tom Lane and Nathan Bossart
Discussion: https://postgr.es/m/17717-6c50eb1c7d23a886@postgresql.org
Diffstat (limited to 'src/bin/psql/tab-complete.c')
| -rw-r--r-- | src/bin/psql/tab-complete.c | 5 |
1 files changed, 3 insertions, 2 deletions
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 3942bea72d3..23750ea5fbd 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -4598,8 +4598,9 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE", "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED", "INDEX_CLEANUP", "PROCESS_TOAST", - "TRUNCATE", "PARALLEL"); - else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE")) + "TRUNCATE", "PARALLEL", "SKIP_DATABASE_STATS", + "ONLY_DATABASE_STATS"); + else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS")) COMPLETE_WITH("ON", "OFF"); else if (TailMatches("INDEX_CLEANUP")) COMPLETE_WITH("AUTO", "ON", "OFF"); |