PG WAT - PostgreSQL Workload Analyzer Tool

Summary

This tool parses PostgreSQL EXPLAIN (ANALYZE) plans in raw SQL, extracts all information and puts it without hierarchy into a table for further analysis. The table can be used to calculate statistics around queries and their plans.

Usage

1. Run setup.sql on any DB of your choice. Please note, that the setup script is destructive, it will remove the pgwat schema first and thus possibly all plans you already analyzed.

2. To run the analyzer, provide a plan name and plan in JSON format to pgwat.parse_explain_plan. For instance:

   SELECT pgwat.parse_explain_plan(
       'my fancy plan'
     , 'my db'
     , $$<json of plan>$$
   );
   

3. After using pgwat.parse_explain_plan, you can query pgwat.query_node_stats to extract all information you need. There are some functions in sql/analytics which you can load and then use as well.