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:

Flags:

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:

Flags:

Example:

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

pgplan profile <subcommand>

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

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.

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.