snippetsqlModeratepending

PostgreSQL EXPLAIN format options -- JSON and visual plans

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

EXPLAINFORMAT JSONanalyzebuffersplan visualizationdalibo

postgresql

Problem

Text EXPLAIN output is hard to parse programmatically and difficult to share for review.

Solution

Use FORMAT JSON for machine-readable plans and online tools for visualization.

Code Snippets

EXPLAIN formats and auto_explain setup

-- JSON format (paste into explain.dalibo.com)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 42;

-- Useful EXPLAIN options
EXPLAIN (ANALYZE, COSTS, BUFFERS, TIMING, VERBOSE)
SELECT * FROM orders WHERE created_at > NOW() - interval '7 days';

-- Compare plans: SET enable_seqscan = off;
-- Forces index usage to compare plans
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
RESET enable_seqscan;

-- Auto-explain: log slow queries automatically
ALTER SYSTEM SET shared_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '1s';
ALTER SYSTEM SET auto_explain.log_analyze = true;

Revisions (0)

No revisions yet.