WIUNIX ACADEMY
PostgreSQL slow queries: interval statistics and row-estimate errors
Page 2 of 3. Review the earlier steps and prerequisites before continuing.
YOUR SYSTEM. YOUR STEPS.
Where are you working?
Choose your OS and version. The prerequisites, commands and troubleshooting follow your environment.
USE YOUR OWN VALUES
Configure this tutorial
Set the example values for your environment; related explanations and copy panels update together. Never enter passwords, private keys or tokens. Values stay in this tab and are not sent to the server or share links. Check actual DNS, certificates and network policy before running commands.
Different IP addresses, domains or ports? Open the settings below, change the values and select “Apply to tutorial”, then copy the updated commands.
Set values for my environment3 editable values
LET’S BUILD
Rank only comparable deltas
A global reset invalidates subtraction; a changed per-entry start is excluded. Increased dealloc means eviction may hide work, so label the interval incomplete. The inner join deliberately excludes newly appearing statements; inspect those separately in the cumulative view. Negative deltas are discarded, not described as faster execution.
Same persistent monitored psql session
SELECT b.captured_at,clock_timestamp() AS observed_at,i.stats_reset=b.stats_reset AS same_reset,i.dealloc-b.dealloc AS evicted_entries FROM public.pg_stat_statements_info i CROSS JOIN academy_info_before b;
SELECT s.queryid,s.userid,s.toplevel,s.calls-b.calls AS interval_calls,round((s.total_exec_time-b.total_exec_time)::numeric,2) AS interval_ms,round(((s.total_exec_time-b.total_exec_time)/NULLIF(s.calls-b.calls,0))::numeric,2) AS ms_per_call,s.rows-b.rows AS interval_rows,s.shared_blks_read-b.shared_blks_read AS blocks_read,s.temp_blks_written-b.temp_blks_written AS temp_blocks_written
FROM public.pg_stat_statements s JOIN academy_stat_before b USING(dbid,userid,queryid,toplevel) CROSS JOIN academy_info_before ib CROSS JOIN public.pg_stat_statements_info ii
WHERE s.stats_since=b.stats_since AND ii.stats_reset=ib.stats_reset AND s.calls>b.calls AND s.total_exec_time>=b.total_exec_time
ORDER BY s.total_exec_time-b.total_exec_time DESC LIMIT 15;A bounded ranking has explicit reset, eviction and new-entry limitations.
Counters suggest hypotheses, not automatic indexes
High calls and total time suggest repeated work; high per-call cost deserves representative parameters and a plan. Rows counts returned/affected rows, not all rows scanned. Shared block reads miss PostgreSQL buffers but may hit the OS cache, so are not direct disk-latency measurements. Temp blocks indicate spill work; a global work_mem increase multiplies across simultaneous operations. Preserve concurrency and parameter skew when reproducing.
LET’S BUILD
Open the isolated synthetic experiment
Use a second terminal and the approved disposable PostgreSQL18 service. Keep this second connection for the remaining SQL. Temporary data and its plans are illustrative, never a benchmark of your production workload. Bound CPU, temporary storage and row count before running.
Disposable test client
/usr/lib/postgresql/18/bin/psql -X -v ON_ERROR_STOP=1 'service=academy_plan_lab'The second session is connected only to the disposable database.
LET’S BUILD
Create a known column correlation
Every tenant has a fixed status in this fixture. Without multivariate information the planner may multiply independent selectivities and underestimate the conjunction. Inspect the scan node: the aggregate root always returns one count row. Sequential scan itself is not a failure.
Same disposable psql session
SET statement_timeout='30s';
CREATE TEMP TABLE academy_plan_probe AS SELECT i AS id,(i%100)::integer AS tenant_id,CASE WHEN i%100<5 THEN 'pending' ELSE 'done' END::text AS status FROM generate_series(1,100000) AS g(i);
ANALYZE academy_plan_probe;
EXPLAIN (ANALYZE,BUFFERS,TIMING OFF,SETTINGS) SELECT count(*) FROM academy_plan_probe WHERE tenant_id=1 AND status='pending';Estimated and actual qualifying scan rows can be compared.
LET’S BUILD
Add dependency statistics and compare again
Analyze after creating extended statistics, then compare the same query/data. Better cardinality estimates do not promise lower elapsed time. Real-table ANALYZE and statistics targets have cost and need owning review; this temporary fixture is not a production tuning prescription.
Same disposable psql session
CREATE STATISTICS pg_temp.academy_plan_dependency(dependencies) ON tenant_id,status FROM pg_temp.academy_plan_probe;
ANALYZE academy_plan_probe;
EXPLAIN (ANALYZE,BUFFERS,TIMING OFF,SETTINGS) SELECT count(*) FROM pg_temp.academy_plan_probe WHERE tenant_id=1 AND status='pending';The scan estimate is compared with actual rows under the same fixture.
ANALYZE executes the query
Plain EXPLAIN estimates; EXPLAIN ANALYZE executes and adds instrumentation overhead. Do not paste a production write or volatile function into it. Rollback cannot reverse every external side effect or sequence increment. Reproduce a bounded representative read on staging with timeout. Read loops, cardinality errors, spills and buffer work together; a warm second run alone proves no durable improvement.