# pg\_stat\_plans Published articles for pg\_stat\_plans. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Finding plans in pg\_stat\_plans easily with pg\_find\_plans DevFeed: [Finding plans in pg\_stat\_plans easily with pg\_find\_plans]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-12-04T10:40:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [SQL](), [JSON](), [Python](), [Monitoring]() Tags: [join](), [json](), [pg-stat-plans](), [pg-stat-statements](), [planner](), [postgresql](), [python](), [sql]() ### AI overview The article introduces pg_find_plans, an experimental pg_stat_plans submodule for PostgreSQL. It uses machine-readable EXPLAIN output to search stored execution plans by criteria such as sequential scans, execution costs, and join counts directly from SQL. The article also discusses its PL/Python and PL/pgSQL implementation, input sanitization, limitations, and the usefulness of JSON for representing plan structures. ### Source excerpt As I recently blogged about, pg_stat_plans is a PostgreSQL satellite project I've been working on that aims to support earlier versions of Postgres that cannot use the new pg_stat_statements, and to track execution costs at the plan rather than the query granularity. It allows the user to easily explain each stored query text to see the plan for the entry, and has features that facilitate monitoring planner regressions. Since PostgreSQL 9.0, support for machine-readable EXPLAIN output has existed. I'm not aware that anyone else got around to actually doing something interesting with this capability, though. I knew that in order to get the most benefit from pg_stat_plans, it ought to be possible to leverage this capability to search for plans based on arbitrary criteria, directly from SQL. I've written an experimental submodule of pg_stat_plans, called pg_find_plans, that is designed to do just that - to quickly find plans and their execution costs, for those plans that, say, perform a sequential scan on a known large table. Here's the description of pg_find_plans from its documentation: pg_find_plans is written in PL/Python and PL/PgSQL. It is intended to provide users with a better way to ask questions like "what are the execution costs of all plans tracked since last statistics reset that involve a sequential scan against mytable, and have more than 2 joins?". That might be written as: mydb=# select join_count(json_plan), p.* from pg_stat_plans p join stored_plans sp on (p.userid=sp.userid and p.dbid=sp.dbid and p.planid=sp.planid) where from_our_database and join_count(json_plan) > 2 and contains_node(json_plan, 'Seq Scan', 'mytable'); order by 1 desc nulls last; Users should have a high degree of confidence that their queries on plan's structure are free of detectable errors, and pg_find_plans ensures this by carefully sanitising user input. For example, if the node of interest was specified as 'seq scan' above, the query would raise an error - to do any less mi ## First release of pg\_stat\_plans DevFeed: [First release of pg\_stat\_plans]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-10-20T14:53:00Z Content type: release Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [SQL](), [Open Source](), [Logging]() Tags: [database](), [open-source](), [performance](), [pg-stat-plans](), [pg-stat-statements](), [planner](), [postgresql](), [query](), [release](), [sql]() ### AI overview The article announces the first release of pg_stat_plans, an open-source PostgreSQL module based on pg_stat_statements. It analyzes plan execution costs and planner regressions, extends normalized-statement analysis to PostgreSQL 9.0 and 9.1, and tracks plan costs over time. ### Source excerpt Anyone who attended my recent talk at Postgres Open, which was co-presented with my 2ndQuadrant colleague Greg Smith, "Beyond Query Logging", will be aware that pg_stat_statements, the standard contrib module that assigns execution costs to queries and makes them available from a view in the database, has been improved considerably in the recent 9.2 Postgres release. It has been improved in a way that we believe will alter the preferred approach to workload analysis on PostgreSQL databases away from log analysis tools, which just don't offer the performance, flexibility or granularity of this new approach. We also announced a new open source tool that addresses a related but slightly different problem (the analysis of plan execution costs, and planner regressions), as well as making most of the benefits of pg_stat_statements on 9.2 available to users stuck on earlier versions of Postgres. This new tool is called pg_stat_plans, and is itself based on pg_stat_statements. The 9.2 pg_stat_statements feature of particular importance, the ability to "normalise" non-prepared statements that the large majority of applications use exclusively is now brought to earlier versions (versions 9.0 and 9.1, though pg_stat_plans works fine on 9.2 too). Since pg_stat_plans fingerprints plans rather than query trees, the way this works is slightly different to pg_stat_statements, and perhaps doesn't quite match people's intuitive expectations about how normalisation ought to behave in some cases. These differences have been extensively documented. pg_stat_plans also has the ability to EXPLAIN a stored, representative SQL text, in order to facilitate deeper analysis of plan execution costs. Plan total_cost and startup_cost is tracked over time for each plan, for example, so that the "crossover point" at which the planner begins to prefer an alternative plan can sometimes be observed, and the planner's "reasoning" can perhaps be better understood. pg_stat_plans is distributed under the P