Horton

Re: Statistics Import and Export: difference in statistics dumped

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>

View thread
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Corey Huinker <corey.huinker@gmail.com>, Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, Michael Paquier <michael@paquier.xyz>, Nathan Bossart <nathandbossart@gmail.com>, Bruce Momjian <bruce@momjian.us>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Magnus Hagander <magnus@hagander.net>, Stephen Frost <sfrost@snowman.net>, Peter Smith <smithpb2250@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Alvaro Herrera <alvherre@alvh.no-ip.org>, jian he <jian.universality@gmail.com>
Date: 2025-02-28T09:21:29Z
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.

Hi Jeff,
I am changing the subject on this email and thus creating a new thread
to discuss this issue.

On Fri, Feb 28, 2025 at 8:02 AM Jeff Davis <pgsql@j-davis.com> wrote:
>
> On Tue, 2025-02-25 at 11:11 +0530, Ashutosh Bapat wrote:
> > So the dumped statistics are not restored exactly. The reason for
> > this
> > is the table statistics is dumped before dumping ALTER TABLE ... ADD
> > CONSTRAINT command which changes the statistics. I think all the
> > pg_restore_relation_stats() calls should be dumped after all the
> > schema and data modifications have been done. OR what's the point in
> > dumping statistics only to get rewritten even before restore
> > finishes.
>
> In your example, it's not so bad because the stats are actually better:
> the index is built after the data is present, and therefore relpages
> and reltuples are correct.
>
> The problem is more clear if you use --no-data. If you load data,
> ANALYZE, pg_dump --no-data, then reload the sql file, then the stats
> are lost.
>
> That workflow is very close to what pg_upgrade does. We solved the
> problem for pg_upgrade in commit 71b66171d0 by simply not updating the
> statistics when building an index and IsBinaryUpgrade.
>
> To solve the issue with dump --no-data, I propose that we change the
> test in 71b66171d0 to only update the stats if the physical relpages is
> non-zero.

I don't think I understand the patch well, but here's one question: If
a table is truncated and index is rebuilt would the code in patch stop
it from updating the stats? If yes, that looks problematic.

>
> Patch attached:
>
>  * If the dump is --no-data, or during pg_upgrade, the table will be
> empty, so the physical relpages will be zero and the restored stats
> won't be overwritten.
>
>  * If (like in your example) the dump includes data, the new stats are
> based on real data, so they are better anyway. This is sort of like the
> case where autoanalyze kicks in.
>
>  * If the dump is --statistics-only, then there won't be any indexes
> created in the SQL file, so when you restore the stats, they will
> remain until you do something else to change them.
>
>  * If your example really is a problem, you'd need to dump first with -
> -no-statistics, and then with --statistics-only, and restore the two
> SQL files in order.

There are few problems

1. If there are thousands of tables with primary key constraints, we
have twice the number of calls to pg_restore_relation_stats() of which
only half will be useful. The stats written by the first set of calls
will be overwritten by the second set of calls. The time spent in
executing the first set of calls can be saved completely and to some
extent time dumping the calls as well. It will be some measurable
improvement I think.

2. We aren't restoring the statistics faithfully - as mentioned in
Greg's reply. If users dump and restore with autovacuum turned off,
they will be surprised to see the statistics to be different on the
original and restored database - which may have other effects like
change in plans.

3. The test I am building over at [1] is aimed at testing whether the
objects dumped get restored faithfully by comparing dumps from the
original and restored database. That's a bit crude method but is being
used by some of our tests. I think it will be good to test statistics
as well in that test. But if it's not going to be same on the original
and the restored database we can not test it. For now, I have used
--no-statistics.

>
>
> Alternatively, we could put stats into SECTION_POST_DATA, which was
> already discussed[*], and we decided against it (though there was not a
> clear consensus).

I haven't looked at the code which dumps the statistics, but it does
seem simple dump the statistics after the constraint creation command
for the tables with primary key constraint. That will dump
not-up-to-date statistics and might overwrite the statistics


[1] https://www.postgresql.org/message-id/CAExHW5sBbMki6Xs4XxFQQF3C4Wx3wxkLAcySrtuW3vrnOxXDNQ%40mail.gmail.com

-- 
Best Wishes,
Ashutosh Bapat