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-07-28T01:08:44Z
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

>
> The "restore" use case is the primary point of your patch, and that
> should be as simple and future-proof as possible. The parameters should
> be name/value pairs and there shouldn't be any "control" parameters --
> it's not the job of pg_dump to specify whether the restore should be
> transactional or in-place, it should just output the necessary stats.
>
> That restore function might be good enough to satisfy the "ad-hoc stats
> hacking" use case as well, but I suspect we want slightly different
> behavior. Specifically, I think we'd want the updates to be
> transactional rather than in-place, or at least optional.
>

Point well taken.

Both function pairs now call a generic internal function.

Which is to say that pg_set_relation_stats and pg_restore_relation_stats
both accept parameters in their own way, and both call
an internal function relation_statistics_update(), each with their own
defaults.

pg_set_relation_stats always leaves "version" NULL, does transactional
updates, and treats any data quality issue as an ERROR. This is is in line
with a person manually tweaking stats to check against a query to see if
the plan changes.

pg_restore_relation_stats does in-place updates, and steps down all errors
to warnings. The stats may not write, but at least it won't fail the
pg_upgrade for you.

pg_set_attribute_stats is error-maximalist like pg_set_relation_stats.
pg_restore_attribute_stats never had an in-place option to begin with.




>
> > The leading OUT parameters tell us the rel/attribute/inh affected (if
> > any), and which params had to be rejected for whatever reason. The
> > kwargs is the variadic key-value pairs that we were using for all
> > stat functions, but now we will be using it for all parameters, both
> > statistics and control, the control parameters will be:
>
> I don't like the idea of mixing statistics and control parameters in
> the same list.
>

There's no way around it, at least now we need never worry about a
confusing order for the parameters in the _restore_ functions because they
can now be in any order you like. But that speaks to another point: there
is no "you" in using the restore functions, those function calls will
almost exclusively be generated by pg_dump and we can all live rich and
productive lives never having seen one written down. I kid, but they're
actually not that gross.

Here is a -set function taken from the regression tests:

SELECT pg_catalog.pg_set_attribute_stats(
    relation => 'stats_import.test'::regclass::oid,
    attname => 'arange'::name,
    inherited => false::boolean,
    null_frac => 0.5::real,
    avg_width => 2::integer,
    n_distinct => -0.1::real,
    range_empty_frac => 0.5::real,
    range_length_histogram => '{399,499,Infinity}'::text
    );
 pg_set_attribute_stats
------------------------

(1 row)

and here is a restore function

-- warning: mcv cast failure
SELECT *
FROM pg_catalog.pg_restore_attribute_stats(
    'relation', 'stats_import.test'::regclass::oid,
    'attname', 'id'::name,
    'inherited', false::boolean,
    'version', 150000::integer,
    'null_frac', 0.5::real,
    'avg_width', 2::integer,
    'n_distinct', -0.4::real,
    'most_common_vals', '{2,four,3}'::text,
    'most_common_freqs', '{0.3,0.25,0.05}'::real[]
    );
WARNING:  invalid input syntax for type integer: "four"
 row_written |          stats_applied           |            stats_rejected
           | params_rejected
-------------+----------------------------------+--------------------------------------+-----------------
 t           | {null_frac,avg_width,n_distinct} |
{most_common_vals,most_common_freqs} |
(1 row)

There's a few things going on here:

1. An intentionally bad, impossible to write, value was put in
'most_common_vals'. 'four' cannot cast to integer, so the value fails, and
we get a warning
2. Because most_common_values failed, we can no longer construct a legit
STAKIND_MCV, so we have to throw out most_common_freqs with it.
3. Those failures aren't enough to prevent us from writing the other stats,
so we write the record, and report the row written, the stats we could
write, the stats we couldn't, and a list of other parameters we entered
that didn't make sense and had to be rejected (empty).

Overall, I'd say the format is on the pedantic side, but it's far from
unreadable, and mixing control parameters (version) with stats parameters
isn't that big a deal.


I do like the idea of returning a set, but I think it should be the
> positive set (effectively a representation of what is now in the
> pg_stats view) and any ignored settings would be output as WARNINGs.
>

Displaying the actual stats in pg_stats could get very, very big. So I
wouldn't recommend that.

What do you think of the example presented earlier?

Attached is v25.

Key changes:
- Each set/restore function pair now each call a common function that does
the heavy lifting, and the callers mostly marshall parameters into the
right spot and form the result set (really just one row).
- The restore functions now have all parameters passed in via a variadic
any[].
- the set functions now error out on just about any discrepancy, and do not
have a result tuple.
- test cases simplified a bit. There's still a lot of them, and I think
that's a good thing.
- Documentation to reflect significant reorganization.
- pg_dump modified to generate new function signatures.