Horton

Re: Statistics Import and Export

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: "Shinoda, Noriyoshi (SXD Japan FSIP)" <noriyoshi.shinoda@hpe.com>
Cc: Jeff Davis <pgsql@j-davis.com>, jian he <jian.universality@gmail.com>, 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>, "alvherre@alvh.no-ip.org" <alvherre@alvh.no-ip.org>
Date: 2024-10-22T11:09:45Z
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.

>
>
> If the relpages option contains -1 only for partitioned tables, shouldn't
> pg_set_relation_stats restrict the values that can be
>
> specified by table type? The attached patch limits the value to -1 or more
> if the target
>
> is a partition table, and 0 or more otherwise.
>
> Changing relpages to -1 on a non-partitioned table seems to significantly
> change the execution plan.
>

Short answer: It's working as intended. Significantly changing the
execution plan in weird ways is part of the intention of the function, even
if the execution plan changes for the worse. I appreciate

Longer answer:

Enforcing -1 on only partitioned tables is tricky, as it seems to be a
value for any table that has no local storage. So foreign data wrapper
tables could, in theory, also have this value. More importantly, the -1
value seems to be situational, in my experience it only happens on
partitioned tables after they have their first partition added, which means
that the current valid stat range is set according to facts that can
change. Like so :

chuinker=# select version();
                                                              version

------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 (Postgres.app) on aarch64-apple-darwin21.6.0, compiled by
Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit
(1 row)
chuinker=# create table part_parent (x integer) partition by range (x);
CREATE TABLE
chuinker=# select relpages from pg_class where oid =
'part_parent'::regclass;
 relpages
----------
        0
(1 row)

chuinker=# analyze part_parent;
ANALYZE
chuinker=# select relpages from pg_class where oid =
'part_parent'::regclass;
 relpages
----------
        0
(1 row)

chuinker=# create table part_child partition of part_parent for values from
(0) TO (100);
CREATE TABLE
chuinker=# select relpages from pg_class where oid =
'part_parent'::regclass;
 relpages
----------
        0
(1 row)

chuinker=# analyze part_parent;
ANALYZE
chuinker=# select relpages from pg_class where oid =
'part_parent'::regclass;
 relpages
----------
       -1
(1 row)

chuinker=# drop table part_child;
DROP TABLE
chuinker=# select relpages from pg_class where oid =
'part_parent'::regclass;
 relpages
----------
       -1
(1 row)

chuinker=# analyze part_parent;
ANALYZE
chuinker=# select relpages from pg_class where oid =
'part_parent'::regclass;
 relpages
----------
       -1
(1 row)

Prior versions (March 2024 and earlier) of this patch and the
pg_set_attribute_stats patch did have many checks to prevent importing stat
values that were "wrong" in some way. Some examples from attribute stats
import were:

* Histograms that were not monotonically nondecreasing.
* Frequency values that were out of bounds specified by other values in the
array.
* Frequency values outside of the [0.0,1.0] or [-1.0,1.0] depending on the
stat type.
* paired arrays of most-common-values and their attendant frequency array
not having the same length

All of these checks were removed based on feedback from reviewers and
committers who saw the pg_set_*_stats() functions as a fuzzing tool, so the
ability to set illogical, wildly implausible, or mathematically impossible
values was a feature, not a bug. I would suspect that they would view your
demonstration that setting impossible values on a table as proof that the
function can be used to experiment with planner scenarios. So, while I
previously would have eagerly accepted this patch as another valued
validation check, such checks don't fit with the new intention of the
functions. Still, I greatly appreciate your helping us discover ways in
which we can use this tool to make the planner do odd things.

One thing that could cause us to enforce a check like the one you submitted
would be if an invalid value caused a query to fail or a session to crash,
even then, that would probably spur a change to make the planner more
defensive rather than more checks on the set_* side.

>