Horton

Re: Statistics Import and Export

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, 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>
Date: 2024-03-08T06:35:40Z
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

>
>
>
> Having some discussion around that would be useful.  Is it better to
> have a situation where there are stats for some columns but no stats for
> other columns?  There would be a good chance that this would lead to a
> set of queries that were properly planned out and a set which end up
> with unexpected and likely poor query plans due to lack of stats.
> Arguably that's better overall, but either way an ANALYZE needs to be
> done to address the lack of stats for those columns and then that
> ANALYZE is going to blow away whatever stats got loaded previously
> anyway and all we did with a partial stats load was maybe have a subset
> of queries have better plans in the interim, after having expended the
> cost to try and individually load the stats and dealing with the case of
> some of them succeeding and some failing.
>

It is my (incomplete and entirely second-hand) understanding is that
pg_upgrade doesn't STOP autovacuum, but sets a delay to a very long value
and then resets it on completion, presumably because analyzing a table
before its data is loaded and indexes are created would just be a waste of
time.



>
> Overall, I'd suggest we wait to see what Corey comes up with in terms of
> doing the stats load for all attributes in a single function call,
> perhaps using the VALUES construct as you suggested up-thread, and then
> we can contemplate if that's clean enough to work or if it's so grotty
> that the better plan would be to do per-attribute function calls.  If it
> ends up being the latter, then we can revisit this discussion and try to
> answer some of the questions raised above.
>

In the patch below, I ended up doing per-attribute function calls, mostly
because it allowed me to avoid creating a custom data type for the portable
version of pg_statistic. This comes at the cost of a very high number of
parameters, but that's the breaks.

I am a bit concerned about the number of locks on pg_statistic and the
relation itself, doing CatalogOpenIndexes/CatalogCloseIndexes once per
attribute rather than once per relation. But I also see that this will
mostly get used at a time when no other traffic is on the machine, and
whatever it costs, it's still faster than the smallest table sample (insert
joke about "don't have to be faster than the bear" here).

This raises questions about whether a failure in one attribute update
statement should cause the others in that relation to roll back or not, and
I can see situations where both would be desirable.

I'm putting this out there ahead of the pg_dump / fe_utils work, mostly
because what I do there heavily depends on how this is received.

Also, I'm still seeking confirmation that I can create a pg_dump TOC entry
with a chain of commands (e.g. BEGIN; ...  COMMIT; ) or if I have to fan
them out into multiple entries.

Anyway, here's v7. Eagerly awaiting feedback.