A PostgreSQL dashboard query takes 900 ms from the app, but EXPLAIN ANALYZE reports Execution Time: 40 ms; which EXPLAIN options help explain the gap?
answer
- what Execution Time leaves out
- output functions and TOAST
- is it the same session
- planning has its own lines
basics
~20 sEXPLAIN ANALYZE discards rows before converting them for the client and never sends them, so detoasting, output functions and network time stay invisible. SERIALIZE measures the conversion, SETTINGS exposes session differences, and the app's prepared-statement plan may differ.
solid answer
~40 s`Execution Time` covers the executor and non-deferred triggers only: it excludes parsing, planning (reported as `Planning Time`), converting result rows to text or binary, and the network. So I first run `EXPLAIN (ANALYZE, SERIALIZE)`, available since 17: it performs the output conversion, detoasting large values such as a `gps_track` `jsonb` column, and prints `Serialization: time=... output=...kB`, which also tells me how many kilobytes the app has to pull over the wire. Next, `SETTINGS` lists planner-relevant parameters that differ from the built-in defaults, so I can compare my `psql` session with the app's (`work_mem`, `enable_*`, `random_page_cost`, `search_path`). If the app uses prepared statements it may run a generic plan, which `EXPLAIN ANALYZE EXECUTE` shows. `MEMORY` reports planner memory. To catch the slow execution as the app ran it, I'd use auto_explain.
code
sql · 5 linesEXPLAIN (ANALYZE, SERIALIZE, SETTINGS, MEMORY)
SELECT w.id, w.started_at, w.calories, w.gps_track
FROM workouts w
WHERE w.club_id = 77
AND w.started_at >= now() - interval '7 days';go deeper
Recall that EXPLAIN ANALYZE never sends rows to the client, so its Execution Time is not the time the application waits.
Explain what Execution Time includes and excludes, and what SERIALIZE, SETTINGS and MEMORY each add to the output.
Close a real gap between app latency and EXPLAIN: measure serialization and output size, diff session settings, check the prepared-statement plan, then capture the slow run itself.
Set the team's rule for performance evidence: client-side latency and server plans captured together, with settings and plan shape recorded, before anyone tunes.
## What Execution Time covers `EXPLAIN ANALYZE` reports two summary times, and neither is what the application waited for: | Phase | In `Execution Time`? | How to see it | |---|---|---| | Parsing and rewriting | No | Not shown by `EXPLAIN` | | Planning | No | `Planning Time`, the `Planning:` buffers and, with `MEMORY`, planner memory | | Executor start-up, plan nodes and shut-down | Yes | The node lines | | Non-deferred `AFTER` triggers | Yes | The `Trigger ...` lines | | Converting rows to text or binary, fetching TOASTed values | Only with `SERIALIZE` | The `Serialization:` line | | Sending rows over the network | Never | Measure at the client | So a 40 ms `Execution Time` against 900 ms in the application is not a contradiction. The plan itself ran in 40 ms; the rest lives where the default output does not look. ## SERIALIZE: the cost of producing the output By default `EXPLAIN ANALYZE` throws each result row away **before** it is converted into the wire format, so the data types' output functions never run and large values stored out of line in TOAST are never fetched. For a dashboard query that selects `gps_track`, a `jsonb` column holding each workout's route, that skipped work can be most of the real cost. `EXPLAIN (ANALYZE, SERIALIZE)`, available since PostgreSQL 17, performs the conversion and prints a line such as `Serialization: time=702.448 ms output=91318kB format=text`. Key facts: - The serialization time is included in `Execution Time`. - With `BUFFERS`, the line also shows the pages read to detoast the values. - `SERIALIZE` on its own means `TEXT`; `SERIALIZE BINARY` matches clients that request binary results. - It requires `ANALYZE`, and it still sends nothing, so network time remains invisible. But `output=91318kB` tells you the client would download about 89 MB for one chart. ## SETTINGS: is it even the same environment? A plan from your `psql` session may not be the plan the application gets. `EXPLAIN (SETTINGS)` adds a `Settings:` line listing **planner-relevant parameters whose values differ from the built-in defaults**, wherever they were set: `postgresql.conf`, the role, the database or a `SET` in the session. Typical entries are `work_mem`, `random_page_cost`, the `enable_*` switches, `jit` and `search_path`. Run the same `EXPLAIN` under the application's role and diff the two lines. A different `work_mem`, for example, can turn an in-memory hash into a spilled one or change the plan's shape altogether. In PostgreSQL 18 a node the planner had to use although its `enable_*` switch is off is also marked `Disabled: true`. ## The planning side - `Planning Time` is how long the planner took; it excludes parsing and rewriting. - `EXPLAIN (MEMORY)`, also new in 17, adds `Memory: used=...kB allocated=...kB` under `Planning:`. It is the planner's memory, not the executor's, and helps when a query over many partitions spends real time and memory just being planned. - If the application uses prepared statements, it may be running a **generic plan** built without the actual parameter values. `EXPLAIN ANALYZE EXECUTE` on the prepared statement shows the plan actually used. `EXPLAIN (GENERIC_PLAN)` shows a generic plan for a statement with `$1` placeholders but cannot be combined with `ANALYZE`. When PostgreSQL switches to a generic plan is a planner-settings question. ## Other reasons the numbers differ - **Cache warmth**: your repeated run is warm; compare the `Buffers` `read` counts between runs. - **Timing overhead**, in the other direction: on machines with a slow clock, per-node timing inflates `EXPLAIN ANALYZE`; `TIMING OFF` keeps the row counts and the total while dropping per-node times. - **Different parameter values**: the club with 50,000 workouts is not the club you tested with. - **Work after the last byte**: the driver and the application still have to parse 1,214 rows of route data into objects and render them, which is outside the database entirely. ## Capturing it for the team 1. Reproduce with `EXPLAIN (ANALYZE, SERIALIZE, SETTINGS)` under the application's role and real parameters. 2. Save it as `FORMAT JSON`, the same information in machine-readable form that plan visualisers accept. 3. Add `VERBOSE` when you need output columns, schema-qualified names and the `Query Identifier` that appears when `compute_query_id` is active. 4. To catch the slow execution as the application actually ran it, rather than reproducing it by hand, rely on `auto_explain`, which logs the plans of statements slower than a threshold.
- Why can SELECT * on a table with a large jsonb column look fast in EXPLAIN ANALYZE but slow in the app?Large `jsonb` values are usually stored out of line in TOAST, often compressed. A plain `EXPLAIN ANALYZE` discards each row before its columns are converted, so it never fetches or decompresses those values; the application pays for both, plus the transfer. `EXPLAIN (ANALYZE, SERIALIZE)` performs that conversion and shows its time and output size, and selecting only the columns the chart needs removes the cost.
- Why does SERIALIZE require ANALYZE, and what does SERIALIZE BINARY change?Serialization measures converting real result rows, which exist only when the statement executes, so PostgreSQL rejects `SERIALIZE` without `ANALYZE`. `SERIALIZE` alone means `TEXT`, using each type's text output function; `BINARY` uses the binary output functions instead, matching a client that requests binary results. Neither sends anything over the network.
- How do you see the plan the application's prepared statement actually used?Prepare the statement the same way and run `EXPLAIN ANALYZE EXECUTE name(params)`: it shows the cached plan, generic or custom, that the execution used, and its `Planning Time` includes fetching that plan from the cache. `EXPLAIN (GENERIC_PLAN)` shows a generic plan for a `$1`-style statement but cannot run it. When PostgreSQL switches to the generic plan is a planner-settings question.
- Execution Time includes sending the result rows to the client.
- SERIALIZE measures network transfer time to the client.
- EXPLAIN (MEMORY) shows how much memory the sorts and hashes used.
- EXPLAIN (SETTINGS) prints every server parameter in effect.
- A plan from psql always matches the plan the application's prepared statement runs.
Assumes PostgreSQL 18; the SERIALIZE and MEMORY options exist since 17, so on 16 or older EXPLAIN cannot measure output conversion or planner memory.