Thread

Re: [PATCH] Optionally record Plan IDs to track plan changes for a query

Artem Gavrilov <artem.gavrilov@percona.com> — 2025-01-21T18:47:37Z

On Thu, Jan 2, 2025 at 10:47 PM Lukas Fittl <lukas@fittl.com> wrote:

>
> 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
> <https://url.avanan.click/v2/r01/___https://www.postgresql.org/message-id/flat/604E3199-2DD2-47DD-AC47-774A6F97DCA9*40amazon.com___.YXAzOnBlcmNvbmE6YTpnOjk5NDUyOGU1MTIwZDhhNDQxNzNiMDM0NjEwZjY1NTIxOjc6YWM0YjpjN2VmMzI5ZmVjMmM2N2RlNDg0MGVlNjJmMGFlOTQ3OGQ1NTM1ODZmZGMxNzI2NGQ4NmEwMDcxYmI1ODVjY2RjOmg6VDpO>
> [1]: https://github.com/2ndQuadrant/pg_stat_plans
> <https://url.avanan.click/v2/r01/___https://github.com/2ndQuadrant/pg_stat_plans___.YXAzOnBlcmNvbmE6YTpnOjk5NDUyOGU1MTIwZDhhNDQxNzNiMDM0NjEwZjY1NTIxOjc6NjM3NTowODVhZWY2OGY1MjdhYWEzY2NiMDY1NTVlNzcwYjM5YTlmOTI5ODU3ZWI5ZWY2NjY1YTljMDBmMWEyNDU0ZmMwOmg6VDpO>
> [2]: https://ossc-db.github.io/pg_store_plans/
> <https://url.avanan.click/v2/r01/___https://ossc-db.github.io/pg_store_plans/___.YXAzOnBlcmNvbmE6YTpnOjk5NDUyOGU1MTIwZDhhNDQxNzNiMDM0NjEwZjY1NTIxOjc6NjU3ZDo0NjA1YzQ1ZTk2ZGEzZmZiNmM5NTEyYjZiMTRmYjk3Y2RjMTE5M2ZkMTMwYTg4ZWM1NjdmMWY1N2RhZjI5YTliOmg6VDpO>
> [3]:
> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_stat_plans.html
> <https://url.avanan.click/v2/r01/___https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_stat_plans.html___.YXAzOnBlcmNvbmE6YTpnOjk5NDUyOGU1MTIwZDhhNDQxNzNiMDM0NjEwZjY1NTIxOjc6ZGNjNTozOGIyNDM2MWVhYzg1MTcyNjc5NzJlZTdkM2JkNzliMjE3NjYzODk5MGQwMTdkNDM1YzliMGU5MDA1ZmEwNzFlOmg6VDpO>
>
> --
> Lukas Fittl
>

Hello Lukas,

We have another extension that does plan ID tracking: pg_stat_monitor. So I
think it would be great to have this functionality in core.

I tested your patch set on top of *86749ea3b76* PG revision on MacOS. All
tests successfully passed. However, pgident shows that some files are not
properly formatted.



-- 

<https://www.percona.com/>

Artem Gavrilov
Senior Software Engineer, Percona

artem.gavrilov@percona.com