Hi, Currently, there is little visibility for queries that are being executed using generic or custom plans. There is pg_prepared_statements which show generic_plans and custom_plans as of d05b172a760, but this information is backend local and not very useful to a DBA that wishes to track this information cumulatively and over time. [0] had intentions to add these counters to pg_stat_statements, but was withdrawn due to timing with the commitfest at the time [0] and never picked back up again.
I think it's useful to add these counters. Therefore, the attached patch adds two new columns to pg_stat_statements: "generic_plan_calls" and "custom_plan_calls". These columns track the number of executions performed using a generic or custom plan. Only non-utility statements are counted, as utility statements with an optimizable parameterized query (i.e. CTAS) cannot be called with PREPARE. Additionally, when such statements are repeatedly executed using an extended protocol prepared statement, pg_stat_statements may not handle them properly, since queryId is set to 0 during pgss_ProcessUtility. To avoid introducing two additional counters in CachedPlan, the existing boolean is_reusable—which determines whether a generic plan is reused to manage lock requirements—has been repurposed as an enum. This enum now tracks different plan states, including "generic reused", "generic first time" and "custom". pg_stat_statements uses these states to differentiate between generic and custom plans for tracking purposes. ( I felt this was better than having to carry 2 extra booleans in CachedPlan for this purpose, but that will be the alternative approach ). Not included in this patch and maybe for follow-up work, is this information can be added to EXPLAIN output and perhaps pg_stat_database. Maybe that's a good idea also? This patch bumps the version pf pg_stat_statements. -- Sami Imseih Amazon Web Services (AWS) [0] https://www.postgresql.org/message-id/add1e591fbe8874107e75d04328859ec%40oss.nttdata.com
v1-0001-add-plan_cache-counters-to-pg_stat_statements.patch
Description: Binary data
