Skip to main content

Explain Analyze

The Explain tab runs EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) against your query and returns plan-aware recommendations. This is an owner-only feature because ANALYZE actually executes the query.

Safety​

Three defenses run before EXPLAIN:

  1. Whitelist — only SELECT, WITH, VALUES, and TABLE statements are allowed (checked on literal-stripped text).
  2. Multi-statement rejection — no ; separator outside strings/comments.
  3. BEGIN READ ONLY + ROLLBACK — blocks INSERT/UPDATE/DELETE/DDL at the transaction level.

Recommendations by node type​

Sequential scan​

pocketPG distinguishes between sequential scans that are probably appropriate and scans that look expensive relative to the filter. When the filter can be parsed safely, the app may generate concrete index SQL. When the filter is too complex or the signal is ambiguous, it falls back to prose so it does not produce misleading DDL.

Sort spill​

When a sort spills to disk, pocketPG explains the spill and may recommend a work_mem change or a query-specific workaround.

Hash join spill​

When a hash operation spills or batches, pocketPG accounts for PostgreSQL's hash memory behavior and recommends a conservative memory adjustment. Exact sizing calculations are intentionally not documented publicly.

Nested loop inner Seq Scan​

When the inner side of a nested loop repeatedly scans a table, the join may be missing a supporting index. pocketPG attempts to identify the relevant column and generates SQL only when it can do so safely.

Row estimate accuracy​

When the planner's row estimate is materially different from what actually happened, pocketPG recommends refreshing statistics and validating the plan again.

Index scan selectivity​

When an index scan returns a broad slice of a table, pocketPG may suggest checking whether a bitmap scan or storage-cost setting would be more appropriate for the workload.

Cast safety​

The filter parser distinguishes display casts (text, varchar — safe to strip) from semantic casts (date, integer — need expression indexes). Semantic casts on the column side cause the recommendation to fall back to prose rather than generating a plain-index CREATE INDEX that wouldn't work.