Query Plan Playground

Explore how PostgreSQL turns SQL into scans, joins, sorts, and result estimates.

Value

This supporting project turns PostgreSQL plan snapshots into navigable trees with cost and row evidence.

The interface links each node to a short explanation rule. It keeps planner choices visible during database study.

The browser loads bundled fixtures or one local PostgreSQL EXPLAIN JSON file. Imports stay in memory.

Architecture

Area	Responsibility
PlanCatalog	Loads validated plan fixtures from the classpath.
PlanExplainer	Walks each tree and applies deterministic rules.
PostgresqlPlanImporter	Validates standard PostgreSQL JSON and maps nodes to the shared domain model.
PlanController	Serves fixtures and transient import results.
static/	Provides the browser tree, node inspector, and file import control.
db/	Defines optional PostgreSQL tables, indexes, and sample rows.

The API separates fixture loading from explanation rules. The browser consumes the same JSON used by tests.

PostgresqlPlanImporter validates standard PostgreSQL JSON before it reaches the shared explanation pipeline.

PlanController serves bundled fixtures and transient import results.

Setup

Prerequisites

• Install Java 21 or newer.
• Install Maven 3.9 or newer.
• Install Docker Desktop for the optional PostgreSQL fixture database.

Run these commands from the repository root.

mvn spring-boot:run

Open http://localhost:8080 in a browser.

The primary demo reads classpath fixtures. PostgreSQL is not required to run the application.

Choose a PostgreSQL EXPLAIN JSON file in the Import panel. The selected plan replaces the current view for this session.

Start the optional PostgreSQL fixture database with this command.

docker compose up -d

The database uses local sample values. Do not use this compose configuration for production data.

Stop the optional database with this command.

docker compose down

API

List available fixtures.

curl http://localhost:8080/api/plans

[
  {
    "id": "customer-orders",
    "name": "Find orders for one customer",
    "summary": "An indexed lookup uses a nested loop to find related orders."
  },
  {
    "id": "daily-sales-summary",
    "name": "Summarize daily sales",
    "summary": "A hash join combines broad table scans before sorting the summary."
  }
]

Fetch one tree and its explanations.

curl http://localhost:8080/api/plans/customer-orders

A compact view of the response looks like this.

id: customer-orders
root rule: Nested loop join
explained nodes: 3
root evidence: Cost 0.42..12.77 | estimated rows 5 | actual rows 5 | actual time 0.21 ms

Import one PostgreSQL plan with POST /api/plans/import and a JSON request body.

An imported response keeps the same tree and explanation fields.

id: imported-plan
root rule: Nested loop join
explained nodes: 2
sql text: not included in EXPLAIN JSON

The endpoint accepts one standard PostgreSQL statement result. It accepts planner estimates without ANALYZE fields.

Test status

Run the full verification command.

mvn verify

Status: JavaScript syntax check passed. Maven verification remains pending because Maven is unavailable in this workspace.

The deterministic suite covers fixture loading, tree traversal, scan rules, join rules, and PostgreSQL JSON import validation.

CI runs mvn verify on Java 21 for pushes and pull requests.

Limitations

• Bundled mode reads checked-in snapshots. Import mode does not run EXPLAIN against a live database.
• Rules cover common scan, join, sort, hash, and aggregate nodes.
• Rules do not replace PostgreSQL planner documentation.
• Fixture timings are sample values. They are not benchmark results.
• Imports support one statement result and do not persist after a restart.
• The browser does not yet compare two plans side by side.

Roadmap

Status: Release 2 is complete. Releases 3, 4, and 5 remain.

• Release 2: Import one PostgreSQL EXPLAIN (FORMAT JSON) result.
• Release 3: Highlight estimate and actual-row differences.
• Release 4: Add an optional read-only live database adapter.
• Release 5: Compare alternative plans with shared node paths.

Each roadmap item remains an independent release.