Horton

Re: Statistics Import and Export

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>, Jeff Davis <pgsql@j-davis.com>, jian he <jian.universality@gmail.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, 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>, alvherre@alvh.no-ip.org
Date: 2025-01-07T06:18:04Z
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

>
>
> +1, I've been reviewing the vacuumdb portion and am planning to start a new
> thread in the near future.  IIUC the bulk of the vacuumdb changes are
> relatively noncontroversial, we just haven't reached consensus on the user
> interface.
>
> --
> nathan
>

Attached is the latest (and probably last) unified patchset before parts
get spun off into their own threads.

0001 - This is the unified changes to pg_dump, pg_restore, pg_dumpall, and
pg_upgrade.

It incorporates most of what Jeff changed when he unified v36j, with typo
fixes spotted by Bruce. There was interest in splitting STATISTICS DATA
into RELATION STATISTICS DATA and ATTRIBUTE STATISTICS DATA. To do that,
we'd need to create separate TOC entries, and that doesn't seem worth it to
me.

There was also interest in changing the prefix for STATISTICS DATA.
However, the only special case for prefixes currently relies on an isData
flag. Since there is no isStatistics flag, we would either have to create
one, or do strcmps on te->description looking for "STATISTICS DATA". It's
do-able, but I'm not sure it's worth it.


0002-0005 are for extended stats.

0002 - This makes the input function for pg_ndistinct functional.
0003 - This makes the input function for pg_dependencies functional.
0004 - Makes several static functions in attribute_stats.c public for use
by extended stats. One of those is get_stat_attr_type(), which in the last
patchset was modified to take an attribute name rather than attnum, thus
saving a syscache lookup. However, extended stats identifies attributes by
attnum not name, so that optimization had to be set aside, at least
temporarily.

0005 - These implement the functions pg_set_extended_stats(),
pg_clear_extended_stats(), and pg_restore_extended_stats() and behave like
their relation/attribute equivalents. If we can get these committed and
used by pg_dump, then we don't have to debate how to handle post-upgrade
steps for users who happen to have extended stats vs the approximately
99.75% of users who do not have extended stats.


0006-0011 - These are the currently on-ice vacuumdb changes. 0006-0009 are
"do no harm" reorganizations which add no functionality but make 0011
clearer. 0010 introduces a new test check to TAP.  This group will be spun
out into their own thread where we can focus on what if any changes happen
to vacuumdb. Any changes to vacuumdb are dependent on 0001 being committed,
and any changes to vacuumdb would likewise be informed by the commit status
of 0002-0005.

Work still to be done, in the near and less-near term:

* Adding statistic import for extended statistics objects (i.e. CREATE
STATISTICS) to pg_dump. Blocked by 0002-0005.
* Implementing optional statistics import in postgres_fdw. Blocked by 0001.