Horton

Re: Statistics Import and Export

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>, 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-10T19:52:51Z
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 Sun, Mar 10, 2024 at 11:57 AM Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:

> On Fri, Mar 8, 2024 at 12:06 PM Corey Huinker <corey.huinker@gmail.com>
> wrote:
> >
> > Anyway, here's v7. Eagerly awaiting feedback.
>
> Thanks for working on this. It looks useful to have the ability to
> restore the stats after upgrade and restore. But, the exported stats
> are valid only until the next ANALYZE is run on the table. IIUC,
> postgres collects stats during VACUUM, autovacuum and ANALYZE, right?
>  Perhaps there are other ways to collect stats. I'm thinking what
> problems does the user face if they are just asked to run ANALYZE on
> the tables (I'm assuming ANALYZE doesn't block concurrent access to
> the tables) instead of automatically exporting stats.
>

Correct. These are just as temporary as any other analyze of the table.
Another analyze will happen later, probably through autovacuum and wipe out
these values. This is designed to QUICKLY get stats into a table to enable
the database to be operational sooner. This is especially important after
an upgrade/restore, when all stats were wiped out. Other uses could be
adapting this for use the postgres_fdw so that we don't have to do table
sampling on the remote table, and of course statistics injection to test
the query planner.


> 2.
> +        they are replaced by the next auto-analyze. This function is used
> by
> +        <command>pg_upgrade</command> and <command>pg_restore</command> to
> +        convey the statistics from the old system version into the new
> one.
> +       </para>
>
> Is there any demonstration of pg_set_relation_stats and
> pg_set_attribute_stats being used either in pg_upgrade or in
> pg_restore? Perhaps, having them as 0002, 0003 and so on patches might
> show real need for functions like this. It also clarifies how these
> functions pull stats from tables on the old cluster to the tables on
> the new cluster.
>

That code was adapted from do_analyze(), and yes, there is a patch for
pg_dump, but as I noted earlier it is on hold pending feedback.


>
> 3. pg_set_relation_stats and pg_set_attribute_stats seem to be writing
> to pg_class and might affect the plans as stats can get tampered. Can
> we REVOKE the execute permissions from the public out of the box in
> src/backend/catalog/system_functions.sql? This way one can decide who
> to give permissions to.
>

You'd have to be the table owner to alter the stats. I can envision these
functions getting a special role, but they could also be fine as
superuser-only.


>
> 4.
> +SELECT pg_set_relation_stats('stats_export_import.test'::regclass,
> 3.6::float4, 15000);
> + pg_set_relation_stats
> +-----------------------
> + t
> +(1 row)
> +
> +SELECT reltuples, relpages FROM pg_class WHERE oid =
> 'stats_export_import.test'::regclass;
> + reltuples | relpages
> +-----------+----------
> +       3.6 |    15000
>
> Isn't this test case showing a misuse of these functions? Table
> actually has  no rows, but we are lying to the postgres optimizer on
> stats.


Consider this case. You want to know at what point the query planner will
start using a given index. You can generate dummy data for a thousand, a
million, a billion rows, and wait for that to complete, or you can just
tell the table "I say you have a billion rows, twenty million pages, etc"
and see when it changes.

But again, in most cases, you're setting the values to the same values the
table had on the old database just before the restore/upgrade.


> I think altering stats of a table mustn't be that easy for the
> end user.


Only easy for the end users that happen to be the table owner or a
superuser.


> As mentioned in comment #3, permissions need to be
> tightened. In addition, we can also mark the functions pg_upgrade only
> with CHECK_IS_BINARY_UPGRADE, but that might not work for pg_restore
> (or I don't know if we have a way to know within the server that the
> server is running for pg_restore).
>

I think they will have usage both in postgres_fdw and for tuning.


>
> 5. In continuation to the comment #2, is pg_dump supposed to generate
> pg_set_relation_stats and pg_set_attribute_stats statements for each
> table? When pg_dump does that , pg_restore can automatically load the
> stats.
>

Current plan is to have one TOC entry in the post-data section with a
dependency on the table/index/matview. That let's us leverage existing
filters. The TOC entry will have a series of statements in it, one
pg_set_relation_stats() and one pg_set_attribute_stats() per attribute.


> 9. Isn't it good to add a test case where the plan of a query on table
> after exporting the stats would remain same as that of the original
> table from which the stats are exported? IMO, this is a more realistic
> than just comparing pg_statistic of the tables because this is what an
> end-user wants eventually.
>

I'm sure we can add something like that, but query plan formats change a
lot and are greatly dependent on database configuration, so maintaining
such a test would be a lot of work.