Re: [PATCH] Optionally record Plan IDs to track plan changes for a query - Mailing list pgsql-hackers

The first patch allows use of node jumbling by other unit files / extensions, which would help an out-of-core extension avoid duplicating all the node jumbling code.

The second patch adds a function for the extensible cumulative statistics system to drop all entries for a given statistics kind. This already exists for resetting, but in case of a dynamic list of entries its more useful to be able to drop all of them when "reset" is called.

The third patch adds plan ID tracking in core. This is turned off by default, and can be enabled by setting "compute_plan_id" to "on". Plan IDs are shown in pg_stat_activity, as well as EXPLAIN and auto_explain output, to allow matching a given plan ID to a plan text, without requiring the use of an extension. There are some minor TODOs in the plan jumbling logic that I haven't finalized yet. There is also an open question whether we should use the node attribute mechanism instead of custom jumbling logic?

The fourth patch adds the pg_stat_plans contrib extension, for illustrative purposes. This is inspired by pg_stat_statements, but intentionally kept separate for easier review and since it does not use an external file and could technically be used independently. We may want to develop this into a unified pg_stat_statements+plans in-core mechanism in the future, but I think that is best kept for a separate discussion.

The pg_stat_plans extension utilizes the cumulative statistics system for tracking statistics (extensible thanks to recent changes!), as well as dynamic shared memory to track plan texts up to a given limit (2kB by default). As a side note, managing extra allocations with the new extensible stats is a bit cumbersome - it would be helpful to have a hook for cleaning up data associated to entries (like a DSA allocation).

Thanks,

Lukas

[0]: https://www.postgresql.org/message-id/flat/604E3199-2DD2-47DD-AC47-774A6F97DCA9%40amazon.com

[1]: https://github.com/2ndQuadrant/pg_stat_plans

[2]: https://ossc-db.github.io/pg_store_plans/

[3]: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_stat_plans.html

--

Lukas Fittl