EXPLAIN ANALYZE visualizer

Plain EXPLAIN shows the *estimated* execution plan — the planner's prediction of what it will do, including estimated costs and row counts. It does not actually run the query. EXPLAIN ANALYZE runs the query and adds *actual* execution statistics: real time per node, actual row counts, loop counts, and (with BUFFERS) shared/local buffer hit and read counts. The actual numbers are essential for finding performance problems because estimated row counts can be wildly wrong when table statistics are stale or when complex expressions confuse the planner. Always prefer EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for diagnosis. Be careful with EXPLAIN ANALYZE on data-modifying statements (INSERT, UPDATE, DELETE) — wrap them in a transaction and ROLLBACK afterward so the changes are not committed.

PostgreSQL's cost-based planner may choose a sequential scan over an index scan for several legitimate reasons. First, if the query will return a large fraction of the table (typically more than 5–15%, depending on settings), sequential I/O is faster than random I/O from an index. Second, the table may be small enough that it fits in shared buffers, making a sequential scan nearly free. Third, the planner's statistics may be inaccurate — run ANALYZE on the table to update them. Fourth, the index may not match the query's filter conditions or sort order closely enough. Fifth, random_page_cost (default 4.0) may be too high for your storage — on SSDs, setting it to 1.1–1.5 encourages index usage. You can check the planner's reasoning by comparing the estimated cost of a sequential scan vs. an index scan using SET enable_seqscan = off temporarily, but never leave this disabled in production.

BUFFERS shows I/O statistics per plan node. shared hit means the page was already in PostgreSQL's shared buffer cache — this is fast. shared read means the page had to be fetched from the OS page cache or disk — this is slower. shared dirtied means the node modified a page, and shared written means it flushed a dirty page. For each node, the buffer counts are cumulative, including all child nodes. To isolate a single node's I/O, subtract its children's counts. High read counts on a node that processes few rows usually indicate poor index selectivity or bloated tables. If read is high but execution time is low, the OS page cache is serving the reads — still worth optimizing because under memory pressure those cache hits will become disk reads. Enable track_io_timing = on in postgresql.conf to see the actual time spent on I/O (shown as I/O Timings: read=... write=...), which distinguishes CPU-bound from I/O-bound queries.

When a plan node shows Rows Removed by Filter: N, it means PostgreSQL fetched N rows from the underlying data source (table or index) but then discarded them because they did not match the WHERE clause. A high number relative to the actual rows returned is a strong signal that the scan is reading far more data than necessary. For sequential scans, this often means a missing index — adding one on the filtered columns can eliminate most of the unnecessary reads. For index scans, it can mean the index covers only part of the filter condition; a composite index matching all conditions would be more selective. It can also appear after a Bitmap Heap Scan when the bitmap is "lossy" (block-level rather than row-level granularity), which happens when work_mem is too small to hold an exact bitmap. Increasing work_mem for the session or rewriting the query to reduce the bitmap size can help.

Use the full form: EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT). Enable track_io_timing = on in your session (SET track_io_timing = on;) to get I/O timing breakdowns. For PostgreSQL 13+, add WAL to see WAL generation per node for write queries. Use FORMAT TEXT rather than FORMAT JSON when sharing with humans or this tool — TEXT format is more compact and easier to read. If the query is a SELECT, just run it. If it is an INSERT, UPDATE, or DELETE, wrap it in BEGIN; EXPLAIN (ANALYZE, BUFFERS) ...; ROLLBACK; to avoid side effects. Run the query twice — the first run warms the cache, the second gives you a representative "warm cache" plan. For the most representative results, run on a database with production-like data volume and statistics; plans on small dev databases often differ dramatically from production.