For sharing a slow PostgreSQL reporting-query plan with a colleague in code review, how do you choose between explain.dalibo.com, the offline pev2.html and an embedded PEV2 component?
answer
- a plan is data, not just shape
- literals in Filter and Index Cond
- hosted means a third party holds it
- one file, browser-only storage
- embed behind your own auth
basics
~20 sUse explain.dalibo.com only when the plan and query may leave your control, because sharing stores them on a third-party service. Otherwise open the offline pev2.html, where plans stay in the browser, or embed the pev2 component in an internal tool.
solid answer
~50 sA plan carries data, not just structure. It contains table, index and column names (with `VERBOSE`, schema-qualified names and output columns), literal values in `Filter` and `Index Cond` lines, row counts, any non-default settings, and the query text if you paste it. The **hosted** explain.dalibo.com is the convenient way to share a link, but saving the plan there hands it to a service run by Dalibo, so it fits only data your policy allows outside. The **offline `pev2.html`** is one self-contained file. Its own banner says it is serverless and does not send plans over the internet, and it keeps plans in the browser's IndexedDB with JSON export and import. You attach the plan to the review yourself. **Embedding** the `pev2` package's `Plan` component in an internal tool keeps plans behind your own authentication. For a production plan in review, my default is the offline file, or the embedded tool if one exists.
go deeper
Recall the three forms: the hosted site, the offline single file and the embeddable component. Know that a plan can contain real values from the query.
Explain where each form keeps the plan, and which plan lines leak literals: Filter, Index Cond, Hash Cond and Recheck Cond.
Make the call for production plans: offline file or internal embed by default, hosted only where policy allows, and ways to reduce exposure without losing the review's value.
Set the team rule on where plans may be pasted, and decide whether an internal embed is worth building, weighing review speed against data-handling obligations.
## A plan is data, not just shape It is tempting to treat an execution plan as harmless metadata. Look at what a PostgreSQL `EXPLAIN (ANALYZE, VERBOSE, ...)` plan for a reporting query actually contains: - **schema, table, index and column names**: `VERBOSE` adds schema-qualified names and every node's output column list; - **literal values** from the statement in `Filter`, `Index Cond`, `Hash Cond` and `Recheck Cond` lines, such as a customer email, an account number, or a date range that reveals what is being investigated; - **row counts**, which hint at business volumes such as how many invoices were issued last month; - with `SETTINGS`, the server's **non-default planner settings**; - and, if you paste it, the full **query text**. So choosing where to paste a plan is a data-handling decision, the same one you would make about pasting a query result. ## The three ways to run PEV2 | | explain.dalibo.com | offline `pev2.html` | embedded component | |---|---|---|---| | What it is | a hosted service run by Dalibo | a single self-contained HTML file | the `pev2` package (a Vue 3 component) in your own web app | | Where the plan goes | to the hosted service, which keeps it so a link can be shared | rendered in your browser, kept in its IndexedDB | wherever your application stores it | | How you share | a link to the stored plan | export the plan as a JSON file and hand it over | your application's own links and access control | | Setup | none | download one file | build it into an app; it also needs Bootstrap's CSS | The project's README presents the hosted service as the way to share plans with colleagues or customers. The offline page's own banner says it is serverless and does not send your plans over the internet, and points to the hosted service for saving or sharing. ## Choosing for the code review 1. **Does the plan come from production, or contain real literals?** If so, default to the offline file. Open `pev2.html`, load the plan, look at it together, and attach the exported JSON or the raw plan to the review, where the review system's own access control applies. 2. **Does your organisation allow the hosted service for this data?** If the plan comes from synthetic or staging data, or policy explicitly permits it, explain.dalibo.com is the fastest way to give a colleague a link. 3. **Do plans get shared often?** Then embed the component in an internal tool, such as a review dashboard or an admin page. Plans stay behind your authentication, and links still work. ## Embedding the component The package exports a `Plan` component that takes three props: `plan-source` (the EXPLAIN text or JSON), `plan-query` (the SQL) and an optional `plan-comment`. It needs Vue 3 and Bootstrap's CSS on the page. Because it only renders what it is given, the host application decides who can see which plan, how long plans are kept, and how they are audited. That is the real advantage over the hosted site: the same visualisation, but storage, retention and access follow your own rules. The cost is that someone has to build and maintain the internal page, so it pays off only where plans are shared regularly. ## Reducing what a plan reveals - **Capture from a non-production copy** with realistic volumes, when the plan's shape reproduces there. - **Plan a parameterised statement**: `EXPLAIN (GENERIC_PLAN)` accepts `$1`-style placeholders, so no literal appears. But it cannot be combined with `ANALYZE`, so you lose actual times, and the generic plan may differ from the plan real values get. - **Edit literals out** of a text plan before sharing, keeping its structure intact. - **Leave out the query text** if the plan alone answers the review question. - **Drop `VERBOSE`** if output columns are sensitive. Literals in conditions remain, so this alone is not enough. None of this changes PEV2's behaviour; it changes what you hand it. The decision belongs to whoever owns the data. An interviewer asking this wants to hear that you treat a production plan like any other extract of production data, and that you still pick a tool that gets the review done quickly.
- Why is a plan captured with EXPLAIN (GENERIC_PLAN) safer to share but less useful in PEV2?It plans the statement with `$1`-style placeholders, so no literal values appear. But `GENERIC_PLAN` cannot be combined with `ANALYZE`, so PEV2 gets no actual times, rows or execution buffers, only estimated shape and cost. The generic plan may also differ from the plan the real values produce.
- Does the offline pev2.html make any network request at all?Its banner says plans are not sent over the internet, and it stores them in the browser. When it opens, it checks the project's latest-release endpoint to offer an update, a request that carries no plan data. Anything you export and attach afterwards travels through whatever channel you choose.
- Plans contain only structure, so they are always safe to paste anywhere
- The offline pev2.html uploads plans to explain.dalibo.com to render them
- An embedded PEV2 component connects to the database to fetch plans itself
- EXPLAIN (GENERIC_PLAN, ANALYZE) gives actual times without any literals
- Leaving out VERBOSE removes every literal value from a plan