Horton

Re: Statistics Import and Export

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2023-12-16T04:30:25Z
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.

On Fri, Dec 15, 2023 at 3:36 AM Andrei Lepikhov <a.lepikhov@postgrespro.ru>
wrote:

> On 13/12/2023 17:26, Corey Huinker wrote:> 4. I don't yet have a
> complete vision for how these tools will be used
> > by pg_upgrade and pg_dump/restore, the places where these will provide
> > the biggest win for users.
>
> Some issues here with docs:
>
> func.sgml:28465: parser error : Opening and ending tag mismatch: sect1
> line 26479 and sect2
>    </sect2>
>            ^
>

Apologies, will fix.


>
> Also, as I remember, we already had some attempts to invent dump/restore
> statistics [1,2]. They were stopped with the problem of type
> verification. What if the definition of the type has changed between the
> dump and restore? As I see in the code, Importing statistics you just
> check the column name and don't see into the type.
>

We look up the imported statistics via column name, that is correct.

However, the values in stavalues and mcv and such are stored purely as
text, so they must be casted using the input functions for that particular
datatype. If that column definition changed, or the underlying input
function changed, the stats import of that particular table would fail. It
should be noted, however, that those same input functions were used to
bring the data into the table via restore, so it would have already failed
on that step. Either way, the structure of the table has effectively
changed, so failure to import those statistics would be a good thing.


>
> [1] Backup and recovery of pg_statistic
> https://www.postgresql.org/message-id/flat/724322880.K8vzik8zPz%40abook


That proposal sought to serialize enough information on the old server such
that rows could be directly inserted into pg_statistic on the new server.
As was pointed out at the time, version N of a server cannot know what the
format of pg_statistic will be in version N+1.

This patch avoids that problem by inspecting the structure of the object to
be faux-analyzed, and using that to determine what parts of the JSON to
fetch, and what datatype to cast values to in cases like mcv and
stavaluesN. The exported JSON has no oids in it whatseover, all elements
subject to casting on import have already been cast to text, and the record
returned has the server version number of the producing system, and the
import function can use that to determine how it interprets the data it
finds.


>
> [2] Re: Ideas about a better API for postgres_fdw remote estimates
>
> https://www.postgresql.org/message-id/7a40707d-1758-85a2-7bb1-6e5775518e64%40postgrespro.ru
>
>
This one seems to be pulling oids from the remote server, and we can't
guarantee their stability across systems, especially for objects and
operators from extensions. I tried to go the route of extracting the full
text name of an operator, but discovered that the qualified names, in
addition to being unsightly, were irrelevant because we can't insert stats
that disagree about type with the attribute/expression. So it didn't matter
what type the remote system thought it had, the local system was going to
coerce it into the expected data type or ereport() trying.

I think there is hope for having do_analyze() run a remote query fetching
the remote table's exported stats and then storing them locally, possibly
after some modification, and that would save us from having to sample a
remote table.