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:
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;
pg_find_plans is distributed with pg_stat_plans, version 1.0 beta 3, as a submodule. I'm reasonably confident that there will be a stable release of pg_stat_plans soon.
While there are some problems with using a query text as a proxy for a plan that was once produced by that query text, these cases are handled reasonably well, though the "limitations" section of the pg_find_plans documentation should be understood by users. Still, pg_find_plans exists mostly to "test the waters" for a better-principled implementation. It remains to be seen just how much demand there is for this kind of functionality.
I must say that working on this gave me a new-found appreciation for JSON as a data-interchange format - it dawned on me just why some people consider the Postgres 9.2 JSON datatype so compelling a feature. The last time I needed to write some code that used a lowest common denominator interchange format, that format was the ludicrously verbose XML. Having a format that maps almost perfectly onto scripting language data structures cut down on the amount of boilerplate required considerably. JSON interacts well with Python's dynamic, strong typing, because a piece of JSON data almost looks like a declaration of a nested Python data structure, and can be fairly easily made to be manipulated as one too.