Horton

Re: Statistics Import and Export

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>, Nathan Bossart <nathandbossart@gmail.com>, Magnus Hagander <magnus@hagander.net>, Stephen Frost <sfrost@snowman.net>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Peter Smith <smithpb2250@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, alvherre@alvh.no-ip.org
Date: 2024-06-04T03:34:51Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Change pg_dump default for statistics export.

  2. pg_dump: Adjust reltuples from 0 to -1 for dumps of older versions.

  3. vacuumdb: Don't skip empty relations in --missing-stats-only mode.

  4. pg_dump: Fix query for gathering attribute stats on older versions.

  5. Prevent redeclaration of typedef TocEntry.

  6. Remove unused function parameters in pg_backup_archiver.c.

  7. pg_dump: Retrieve attribute statistics in batches.

  8. pg_dump: Reduce memory usage of dumps with statistics.

  9. Skip second WriteToc() call for custom-format dumps without data.

  10. Add relallfrozen to pg_dump statistics.

  11. Matview statistics depend on matview data.

  12. Add pg_dump --with-{schema|data|statistics} options.

  13. Stats: use schemaname/relname instead of regclass.

  14. CREATE INDEX: do update index stats if autovacuum=off.

  15. Don't convert to and from floats in pg_dump.

  16. CREATE INDEX: don't update table stats if autovacuum=off.

  17. Organize and deduplicate statistics import tests.

  18. Address stats export review comments.

  19. Address stats import review comments.

  20. Add relallfrozen to pg_class

  21. Fix pg_strtof() to not crash on NULL endptr.

  22. Use attnum to identify index columns in pg_restore_attribute_stats().

  23. pg_dump: prepare attribute stats query.

  24. Avoid unnecessary relation stats query in pg_dump.

  25. Remove redundant pg_set_*_stats() variants.

  26. Do not use in-place updates for statistics import.

  27. Fix confusion about data type of pg_class.relpages and relallvisible.

  28. Documentation fixups for dumping statistics.

  29. Trial fix for old cross-version upgrades.

  30. Transfer statistics during pg_upgrade.

  31. Lock table in ShareUpdateExclusive when importing index stats.

  32. Use in-place updates for pg_restore_relation_stats().

  33. Improve error message for replication of generated columns.

  34. pg_dump: Add dumpSchema and dumpData derivative flags.

  35. Disallow modifying statistics on system columns.

  36. Add missing CommandCounterIncrement() in stats import functions.

  37. Add functions pg_restore_relation_stats(), pg_restore_attribute_stats().

  38. Documentation fixup.

  39. Add functions pg_set_attribute_stats() and pg_clear_attribute_stats().

  40. Change pg_*_relation_stats() functions to return type to void.

  41. Disable autovacuum for tables in stats import tests.

  42. Allow pg_set_relation_stats() to set relpages to -1.

  43. Fixup for pg_set_relation_stats().

  44. Create functions pg_set_relation_stats, pg_clear_relation_stats.

  45. Add memory/disk usage for Window aggregate nodes in EXPLAIN.

  46. Improve performance of dumpSequenceData().

  47. Add INJECTION_POINT_CACHED() to run injection points directly from cache

  48. Improve performance of binary_upgrade_set_pg_class_oids().

  49. Improve assertion in mdwritev()

  50. CREATE INDEX: do not update stats during binary upgrade.

  51. Redefine pg_class.reltuples to be -1 before the first VACUUM or ANALYZE.

Attachments

On Thu, May 16, 2024 at 2:26 PM Jeff Davis <pgsql@j-davis.com> wrote:

> On Thu, 2024-05-16 at 05:25 -0400, Corey Huinker wrote:
> >
> > Per previous comments, it was suggested by others that:
> >
> > - having them in SECTION_NONE was a grave mistake
> > - Everything that could belong in SECTION_DATA should, and the rest
> > should be in SECTION_POST_DATA
>
> I don't understand the gravity of the choice here: what am I missing?
>
> To be clear: I'm not arguing against it, but I'd like to understand it
> better. Perhaps it has to do with the relationship between the sections
> and the dependencies?
>

I'm with you, I don't understand the choice and would like to, but at the
same time it now works in the way others strongly suggested that it should,
so I'm still curious about the why.

There were several people expressing interest in this patch at pgconf.dev,
so I thought I'd post a rebase and give a summary of things to date.

THE INITIAL GOAL

The initial goal of this effort was to reduce upgrade downtimes by
eliminating the need for the vacuumdb --analyze-in-stages call that is
recommended (but often not done) after a pg_upgrade. The analyze-in-stages
steps is usually by far the longest part of a binary upgrade and is a
significant part of a restore from dump, so eliminating this step will save
users time, and eliminate or greatly reduce a potential pitfall to
upgrade...and thus reduce upgrade friction (read: excuses to not upgrade).

THE FUNCTIONS

These patches introduce two functions, pg_set_relation_stats() and
pg_set_attribute_stats(), which allow the caller to modify the statistics
of any relation, provided that they own that relation or have maintainer
privilege.

The function pg_set_relation_stats looks like this:

SELECT pg_set_relation_stats('stats_export_import.test'::regclass,
                             150000::integer,
                             'relpages', 17::integer,
                             'reltuples', 400.0::real,
                             'relallvisible', 4::integer);

The function takes an oid of the relation to have stats imported, a version
number (SERVER_VERSION_NUM) for the source of the statistics, and then a
series of varargs organized as name-value pairs. Currently, three arg pairs
are required to properly set (relpages, reltuples, and relallvisible). If
all three are not present, the function will issue a warning, and the row
will not be updated.

The choice of varargs is a defensive one, basically ensuring that a
pgdump that includes statistics import calls will not fail on a future
version that does not have one or more of these values. The call itself
would fail to modify the relation row, but it wouldn't cause the whole
restore to fail. I'm personally not against having a fixed arg version of
this function, nor am I against having both at the same time, the varargs
version basically teeing up the fixed-param call appropriate for the
destination server version.

This function does an in-place update of the pg_class row to avoid bloat
pg_class, just like ANALYZE does. This means that this function call is
NON-transactional.

The function pg_set_attribute_stats looks like this:

SELECT pg_catalog.pg_set_attribute_stats(
    'stats_export_import.test'::regclass,
    'id'::name,
    false::boolean,
    150000::integer,
    'null_frac', 0.5::real,
    'avg_width', 2::integer,
    'n_distinct', -0.1::real,
    'most_common_vals', '{2,1,3}'::text,
    'most_common_freqs', '{0.3,0.25,0.05}'::real[]
    );

Like the first function, it takes a relation oid and a source server
version though that is in the 4th position. It also takes the name of an
attribute, and a boolean as to whether these stats are for inherited
statistics (true) or regular (false). Again what follows is a vararg list
of name-value pairs, each name corresponding to an attribute of pg_stats,
and expecting a value appropriate for said attribute of pg_stats. Note that
ANYARRAY values are passed in as text. This is done for a few reasons.
First, if the attribute is an array type, then the most_common_elements
value will be an array of that array type, and there is no way to represent
that in SQL (it instead gives a higher order array of the same base type).
Second, it allows us to import the values with a simple array_in() call.
Last, it allows for situations where the type name changed from source
system to destination (example: a schema-qualified extension type gets
moved to core).

There are lots of ways that this function call can go wrong. An invalid
attribute name, an invalid parameter name in a name-value pair, invalid
data type of parameter being passed in the value of a name-value pair, or
type coercion errors in array_in() to name just a few. All of these errors
result in a warning and the import failing, but the function completes
normally. Internal typecasting and array_in are all done with the _safe()
equivalents, and any such errors are re-emitted as warnings. The central
goal here is to not make a restore fail just because the statistics are
wonky.

Calls to pg_set_attribute_stats() are transactional. This wouldn't warrant
mentioning if not for pg_set_relation_stats() being non-transactional.

DUMP / RESTORE / UPGRADE

The code for pg_dump/restore/upgrade has been modified to allow for
statistics to be exported/imported by default. There are flags to prevent
this (--no-statistics) and there are flags to ONLY do statistics
(--statistics-only) the utility of which will be discussed later.

pg_dump will make queries of the source database, adjusting the syntax to
reflect the version of the source system. There is very little variance in
those queries, so it should be possible to query as far back as 9.2 and get
usable stats. The output of these calls will be a series of SELECT
statements, each one making a call to either pg_set_relation_stats (one per
table/index/matview) or pg_set_attribute_stats (one per attribute that had
a matching pg_statistic row).

The positioning of these calls in the restore sequence was originally set
up as SECTION_NONE, but it was strongly suggested that SECTION_DATA /
SECTION_POST_DATA was the right spot instead, and that's where they
currently reside.

The end result will be that the new database now has the stats identical
(or at least close to) the source system. Those statistics might be good or
bad, but they're almost certainly better than no stats at all. Even if they
are bad, they will be overwritten by the next ANALYZE or autovacuum.


WHAT IS NOT DONE

1. Extended Statistics, which are considerably more complex than regular
stats (stxdexprs is itself an array of pg_statistic rows) and thus more
difficult to express in a simple function call. They are also used fairly
rarely in customer installations, so leaving them out of the v1 patch
seemed like an easy trade-off.

2. Any sort of validity checking beyond data-types. This was initially
provided, verifying that arrays values representing frequencies must be
between 0.0 and 1.0, arrays that represent most common value frequencies
must be in monotonically non-increasing order, etc. but these were rejected
as being overly complex, potentially rejecting valid stats, and getting in
the way of an other use I hadn't considered.

3. Export functions. Strictly speaking we don't need them, but some
use-cases described below may make the case for including them.

OTHER USES

Usage of these functions is not restricted to upgrade/restore situations.
The most obvious use was to experiment with how the planner behaves when
one or more tables grow and/or skew. It is difficult to create a table with
10 billion rows in it, but it's now trivial to create a table that says it
has 10 billion rows in it.

This can be taken a step further, and in a way I had not anticipated -
actively stress-testing the planner by inserting wildly incorrect and/or
nonsensical stats. In that sense, these functions are a fuzzing tool that
happens to make upgrades go faster.

FUTURE PLANS

Integration with postgres_fdw is an obvious next step, allowing an ANALYZE
on a foreign table to, instead of asking for a remote row sample, to simply
export the stats of the remote table and import them into the foreign table.

Extended Statistics.

CURRENT PROGRESS

I believe that all outstanding questions/request were addressed, and the
patch is now back to needing a review.

FOR YOUR CONSIDERATION

Rebase (current as of f04d1c1db01199f02b0914a7ca2962c531935717) attached.