pgplan

A command-line tool for analyzing and comparing PostgreSQL query execution plans. Get optimization insights and track performance regressions without leaving your terminal.

Features

• Plan Analysis - Run 15+ intelligent rules against a query plan to surface performance issues with actionable fix suggestions
• Plan Comparison - Semantically diff two plans side-by-side to understand what changed and whether it got better or worse
• Flexible Input - Accept JSON EXPLAIN output, raw SQL files, stdin, or paste plans interactively
• Connection Profiles - Save and manage named PostgreSQL connection strings for quick reuse
• Multiple Output Formats - Human-readable colored terminal output or structured JSON for tooling integration

Installation

PyPI

pip install pgplan

NPM

npm i -g pgplan

Go

go install github.com/jacobarthurs/pgplan@latest

Binary

Download the latest release for your platform from the releases page.

Quick Start

# Analyze a query plan from a JSON EXPLAIN output
pgplan analyze plan.json

# Analyze by running a SQL file against a database
pgplan analyze query.sql --db postgres://localhost:5432/mydb

# Compare two plans
pgplan compare before.json after.json

# Interactive mode - paste plans or queries directly into the terminal
pgplan analyze
pgplan compare

Commands

pgplan analyze [file]

Analyzes a single query plan and returns optimization findings sorted by severity.

Arguments:

| Argument | Description | | -------- | ----------- | | file | Path to a .json (EXPLAIN output) or .sql file. Use - for stdin. Omit for interactive mode. |

Flags:

| Flag | Description | | ---- | ----------- | | -d, --db | PostgreSQL connection string (required for SQL input) | | -p, --profile | Named connection profile to use | | -f, --format | Output format: text (default) or json |

Example:

pgplan analyze slow-query.sql --profile prod

pgplan compare [file1] [file2]

Compares two query plans and reports on cost, time, row estimate, and buffer differences across every node in the plan tree.

Arguments:

| Argument | Description | | -------- | ----------- | | file1 | The "before" plan. .json, .sql, - for stdin, or omit for interactive. | | file2 | The "after" plan. Same input options as file1. |

Flags:

| Flag | Description | | ---- | ----------- | | -d, --db | PostgreSQL connection string (required for SQL input) | | -p, --profile | Named connection profile to use | | -f, --format | Output format: text (default) or json | | -t, --threshold | Percent change threshold for significance (default: 5) |

Example:

pgplan compare before.json after.json --threshold 10

pgplan profile <subcommand>

Manages saved PostgreSQL connection profiles stored in ~/.config/pgplan/profiles.yaml.

| Subcommand | Description | | ---------- | ----------- | | list [--show] | List saved profiles. Pass --show to display connection strings. | | add <name> <conn_str> | Add or update a named profile. | | remove <name> | Remove a profile. | | default <name> | Set a profile as the default. | | clear-default | Clear the default profile. |

Example:

pgplan profile add prod postgres://user:pass@host:5432/mydb
pgplan profile default prod

# Now use it with analyze or compare
pgplan analyze query.sql --profile prod

Analysis Rules

The analyze command applies the following rules to identify performance issues. Each finding includes a severity level and an actionable suggestion.

| Severity | Rule | Description | | -------- | ---- | ----------- | | Critical | Sort Spill to Disk | Sort operation exceeded work_mem and spilled to disk | | Warning | Hash Spill to Disk | Hash table exceeded work_mem | | Warning | Temp Block I/O | Plan is reading/writing temporary blocks | | Warning | Seq Scan in Join | Sequential scan used inside a join against a smaller set | | Warning | Seq Scan with Filter | Standalone sequential scan filtering a large number of rows | | Warning | Index Scan Filter Inefficiency | Index scan is fetching many rows then discarding most via filter | | Warning | Bitmap Heap Recheck | Lossy bitmap scan rechecking conditions (bitmap exceeded work_mem) | | Warning | Nested Loop High Loops | Nested loop executing 1,000+ iterations | | Warning | Correlated Subplan | Subplan re-executing on every outer row | | Warning | Worker Launch Mismatch | Fewer parallel workers launched than planned | | Warning | Parallel Overhead | Parallel execution is slower than the serial estimate | | Warning | Large Join Filter Removal | Join filter is discarding a large percentage of rows | | Warning | Excessive Materialization | Materialize node looping many times | | Info | Low Selectivity Index Scan | Index scan is returning most of the table | | Info | Wide Row Output | Query is selecting more columns than necessary |

Comparison Output

The compare command produces a structured diff of two plans including:

• Summary - Overall cost, execution time, and buffer changes with directional indicators
• Node Details - Per-node breakdown of metric changes (cost, rows, loops, buffers, filters, indexes)
• Verdict - A final assessment such as "faster and cheaper" or "slower but cheaper"

Changes below the significance threshold (default 5%) are filtered out to reduce noise.

Output Formats

Text (default)

Colored terminal output with severity-coded findings and directional change indicators. Designed for quick human review.

JSON

Structured output suitable for piping into other tools, CI systems, or dashboards. Includes all metrics, findings, and comparison deltas.

pgplan analyze plan.json --format json | jq '.findings[] | select(.severity == "critical")'

Configuration

Connection Profiles

Profiles are stored in a YAML configuration file at the platform-appropriate config directory:

• Linux/macOS: ~/.config/pgplan/profiles.yaml
• Windows: %APPDATA%\pgplan\profiles.yaml

default: prod
profiles:
  - name: prod
    conn_str: postgres://user:pass@host:5432/production
  - name: dev
    conn_str: postgres://localhost:5432/development

Use --profile <name> with any command, or set a default to skip the flag entirely. The --db and --profile flags are mutually exclusive.

Contributing

Contributions are welcome! To get started:

1. Fork the repository
2. Create a feature branch (git checkout -b my-new-feature)
3. Open a pull request

CI will automatically run tests and linting on your PR.

License

This project is licensed under the MIT License.