When your PostgreSQL queries start slowing down, the first place to look is the execution plan. PostgreSQL’s EXPLAIN and EXPLAIN ANALYZE commands give deep insights into how a query is executed, but reading those plans can be overwhelming — especially when dealing with complex joins, nested subqueries, or parallel execution.
That’s where pgMustard comes in. It’s a modern, user-friendly tool designed to help you analyze PostgreSQL query plans faster, spot inefficiencies, and apply targeted optimizations.
What is pgMustard?
pgMustard is a web-based PostgreSQL query plan viewer and analyzer. Unlike the raw text output of EXPLAIN, pgMustard:
- Parses and visualizes plans in a structured, human-friendly way.
- Highlights potential performance issues directly in the plan.
- Provides actionable hints about what each part of the plan means.
- Allows easy sharing of query plans with colleagues.
Essentially, it helps bridge the gap between PostgreSQL’s detailed but cryptic plans and actionable query tuning steps.
Why use pgMustard over plain EXPLAIN?
Here are some pain points of using plain EXPLAIN (or even EXPLAIN (ANALYZE, BUFFERS)):
- The output is textual and hard to scan.
- You need deep knowledge of PostgreSQL internals to interpret costs, row estimates, or node types.
- Identifying common mistakes (like missing indexes or row misestimation) isn’t obvious.
- Sharing an execution plan often means sending long text files or screenshots.
pgMustard solves these by:
- Turning plans into a hierarchical tree view with collapsible nodes.
- Showing row estimates vs. actuals in an intuitive way.
- Flagging possible misestimations and giving you hints (e.g., suggesting index improvements or statistics updates).
- Letting you upload and share plans through a simple URL.
How pgMustard works
Run your query in PostgreSQL with:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) your_query;
- The FORMAT JSON option produces structured output that pgMustard can read.
- Copy the JSON output and paste it into the pgMustard interface.
- Explore the plan visually:
- Each node is explained in plain language.
- Misestimations and cost hotspots are highlighted.
- Recommendations are linked to relevant PostgreSQL documentation.
Key Features
- Visual plan tree: Easy navigation of nested operations.
- Row estimate analysis: See where the planner guessed wrong.
- Cost breakdowns: Understand which part of the query is most expensive.
- Indexing hints: Detect when a sequential scan might benefit from an index.
- Join analysis: Check whether nested loops, hash joins, or merge joins are being used appropriately.
- Parallelism insights: Learn how PostgreSQL splits work across workers.
- Shareable links: Collaborate with teammates by sharing a single URL.
Example Use Case
Imagine you’re debugging a slow query that joins multiple large tables. The plain plan shows a nested loop with millions of rows processed, but it’s hard to spot the exact cause.
Paste the plan into pgMustard, and immediately:
- The tool highlights that a nested loop is the bottleneck.
- It shows that the planner underestimated row counts, leading to a poor join choice.
- It suggests checking for a missing index on the join column.
With that knowledge, you create the index, rerun the plan, and pgMustard confirms the query is now using a hash join with far fewer rows processed.
Who should use pgMustard?
- Developers writing SQL queries and needing faster feedback loops.
- DBAs who regularly tune queries and explain plans.
- Teams that collaborate on performance debugging and want a consistent tool.
- Educators teaching PostgreSQL query planning concepts.
Alternatives and Complementary Tools
- pgAdmin / DBeaver: Offer graphical EXPLAIN viewers, but without pgMustard’s guidance and hints.
- auto_explain extension: Useful for logging plans automatically, but still outputs raw text.
- pganalyze / pganalyze EXPLAIN Insights: Another commercial tool with similar goals, focused on automated monitoring.
pgMustard stands out for its simplicity and accessibility: no installation required, and it works directly with JSON plans.
Best Practices when using pgMustard
- Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) for the most accurate analysis.
- Pair pgMustard insights with PostgreSQL logs (log_min_duration_statement) to catch recurring slow queries.
- Use pgMustard as a learning tool to deepen your understanding of PostgreSQL internals.
PostgreSQL’s execution plans are a goldmine of information, but they’re not easy to digest. pgMustard makes that process smoother by turning dense JSON into meaningful, actionable insights. Whether you’re a developer new to query optimization or a seasoned DBA, pgMustard can save you time and help you make better tuning decisions.
If you haven’t tried it yet, grab a query plan and give pgMustard a spin — it might just become your go-to tool for PostgreSQL performance tuning.