# Finding plans in pg\_stat\_plans easily with pg\_find\_plans

DevFeed: [Finding plans in pg\_stat\_plans easily with pg\_find\_plans](<https://devfeed.tech/articles/finding-plans-in-pg-stat-plans-easily-with-pg-find-plans-33647.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2012/12/finding-plans-in-pgstatplans-easily.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2012-12-04T10:40:00Z

Content type: article

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Python](<https://devfeed.tech/topics/python.md>), [Monitoring](<https://devfeed.tech/topics/monitoring.md>)

Tags: [join](<https://devfeed.tech/tags/join.md>), [json](<https://devfeed.tech/tags/json.md>), [pg-stat-plans](<https://devfeed.tech/tags/pg-stat-plans.md>), [pg-stat-statements](<https://devfeed.tech/tags/pg-stat-statements.md>), [planner](<https://devfeed.tech/tags/planner.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [python](<https://devfeed.tech/tags/python.md>), [sql](<https://devfeed.tech/tags/sql.md>)

## 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