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-23T04:20:32Z
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.

>
>   * pg_set_relation_stats(): the warning: "cannot export statistics
> prior to version 9.2" doesn't make sense because the function is for
> importing. Reword.
>

+1


>   * I really think there should be a transactional option, just another
> boolean, and if it has a default it should be true. This clearly has
> use cases for testing plans, etc., and often transactions will be the
> right thing there. This should be a trivial code change, and it will
> also be easier to document.
>

For it to have a default, the parameter would have to be at the end of the
list, and it's a parameter list that will grow in the future. And when that
happens we have a jumbled parameter list, which is fine if we only ever
call params by name, but I know some people won't do that. Which means it's
up front right after `version`. Since `version` is already in there, and we
can't default that, I feel ok about moving it there, but alas no default.

If there was some way that the function could detect that it was in a
binary upgrade, then we could use that to determine if it should update
inplace or transactionally.

  * The return type is documented as 'void'? Please change to bool and
> be clear about what true/false returns really mean. I think false means
> "no updates happened at all, and a WARNING was printed indicating why"
> whereas true means "all updates were applied successfully".
>

Good point, that's a holdover.


>   * An alternative would be to have an 'error_ok' parameter to say
> whether to issue WARNINGs or ERRORs. I think we already discussed that
> and agreed on the boolean return, but I just want to confirm that this
> was a conscious choice?
>

That had been discussed as well. If we're adding parameters, then we could
add one for that too. It's making the function call progressively more
unwieldy, but anyone who chooses to wield these on a regular basis can
certainly write a SQL wrapper function to reduce the function call to their
presets, I suppose.


>   * tests should be called stats_import.sql; there's no exporting going
> on
>

Sigh. True.


>   * Aside from the above comments and some other cleanup, I think this
> is a simple patch and independently useful. I am looking to commit this
> one soon.
>
> v24-0002:
>
>   * Documented return type is 'void'
>
>   * I'm not totally sure what should be returned in the event that some
> updates were applied and some not. I'm inclined to say that true should
> mean that all updates were applied -- otherwise it's hard to
> automatically detect some kind of typo.
>

Me either. Suggestions welcome.

I suppose we could return two integers: number of stats input, and number
of stats applied. But that could be confusing, as some parameter pairs form
one stat ( MCV, ELEM_MCV, etc).

I suppose we could return a set of (param_name text, was_set boolean,
applied boolean), without trying to organize them into their pairs, but
that would get really verbose.

We should decide on something soon, because we'd want relation stats to
follow a similar signature.


>
>   * Can you describe your approach to error checking? What kinds of
> errors are worth checking, and which should we just put into the
> catalog and let the planner deal with?
>

1. When the parameters given make for something nonsensical Such as
providing most_common_elems with no corresponding most_common_freqs, then
you can't form an MCV stat, so you must throw out the one you did receive.
That gets a warning.

2. When the data provided is antithetical to the type of statistic. For
instance, most array-ish parameters can't have NULL values in them (there
are separate stats for nulls (null-frac, empty_frac). I don't remember if
doing so crashes the server or just creates a hard error, but it's a big
no-no, and we have to reject such stats, which for now means a warning and
trying to carry on with the stats that remain.

3. When the stats provided would overflow the data structure. We attack
this from two directions: First, we eliminate stat kinds that are
meaningless for the data type (scalars can't have most-common-elements,
only ranges can have range stats, etc), issue warnings for those and move
on with the remaining stats. If, however, the number of those statkinds
exceeds the number of statkind slots available, then we give up because now
we'd have to CHOOSE which N-5 stats to ignore, and the caller is clearly
just having fun with us.

We let the planner have fun with other error-like things:

1. most-common-element arrays where the elements are not sorted per spec.

2. frequency histograms where the numbers are not monotonically
non-increasing per spec.

3. frequency histograms that have corresponding low bound and high bound
values embedded in the array, and the other values in that array must be
between the low-high.


>
>   * I'd check stakindidx at the time that it's incremented rather than
> summing boolean values cast to integers.
>

Which means that we're checking that and potentially raising the same error
in 3-4 places (and growing, unless we raise the max slots), rather than 1.
That struck me as worse.


>
> v24-0003:
>
>   * I'm not convinced that we should continue when a stat name is not
> text. The argument for being lenient is that statistics may change over
> time, and we might have to ignore something that can't be imported from
> an old version into a new version because it's either gone or the
> meaning has changed too much. But that argument doesn't apply to a
> bogus call, where the name/value pairs get misaligned or something.
>

I agree with that.