WIUNIX ACADEMY
PostgreSQL slow queries: interval statistics and row-estimate errors
Rank comparable pg_stat_statements deltas, then investigate a correlation-estimation error on bounded synthetic data.
Make this guide yoursYOUR 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
THE OUTCOME
What will you have at the end?
Rank comparable pg_stat_statements deltas, then investigate a correlation-estimation error on bounded synthetic data.
Supported operating systems and versions
Documented paths for Ubuntu, Debian and Red Hat families with preinstalled compatible products/clients. The first step states each branch’s limits. Service commands were not executed during authoring.
Before you begin
- Existing authorized service and protected client authentication; use the isolated fixture scope specified below.
Which query consumed this interval?
Cumulative totals can rank an old expensive statement above the query causing today’s incident. Start with a bounded interval and stable identity, then examine a representative plan. Aggregate execution time is not one user request’s latency: a small frequently repeated query can dominate total work while a rare query dominates tail latency.
Observe the workload; experiment on synthetic data
This guide assumes PostgreSQL18 and pg_stat_statements already loaded and installed in the public schema of the monitored database. Do not restart production or reset shared counters to follow it. Authorized statistics access can expose normalized SQL from other users; object names can still be sensitive. The plan experiment uses only temporary objects in a separate disposable database.
Select protected services, never credentials
The form contains existing libpq service names and a bounded synthetic row count. Authentication and TLS remain in protected service definitions. Values stay in this browser tab; Apply updates prose and panels together. Pending or invalid edits disable copying. Do not put customer query literals or secrets in these fields.
LET’S BUILD
Verify the installed tools and the host on which commands run
The server must already be PostgreSQL 18 with pg_stat_statements loaded and installed in the documented schema. The OS selector describes the administration client, not a procedure for enabling the extension. PGDG documents Ubuntu 26.10/26.04 and Debian 13/12 clients under /usr/lib/postgresql/18/bin; current RHEL/Rocky/Alma 9/10 PGDG clients use /usr/pgsql-18/bin. The RHEL branch uses that absolute psql path so an older AppStream client is not selected accidentally. Keep the monitored service and disposable lab service distinct. Protected libpq service files belong to the invoking client account; do not copy PostgreSQL server configuration between distro layouts.
These are documented command paths, not executed compatibility tests. Read /etc/os-release and verify the exact product/version before continuing. Existing accounts, approved network access and protected credentials are prerequisites. Do not restart existing services to provision tools, initialize a database, open a firewall port or alter a system Python installation to make this exercise run.
Approved administration client; node-specific checks only on the existing node
cat /etc/os-release
id
test -x /usr/lib/postgresql/18/bin/psql
/usr/lib/postgresql/18/bin/psql --versionThe OS, tool path and version match the selected procedure; stop before data changes if they do not.
LET’S BUILD
Keep one observation session open
Run on the administration workstation. The following SQL panels must remain in this same psql connection because their baseline is a temporary table. Keep autocommit on; a long surrounding transaction can retain a statistics snapshot. Stop if the selected extension/schema does not match.
Administration workstation
/usr/lib/postgresql/18/bin/psql -X -v ON_ERROR_STOP=1 'service=academy_stats'One persistent observation session is open.
LET’S BUILD
Capture a baseline without a shared reset
Record the complete statement key and stats_since, plus global reset and deallocation state. Wait for a representative workload interval at the prompt; this article does not generate production traffic. Statement identity is database/user/queryid/toplevel, not queryid alone.
Same persistent monitored psql session
SELECT version(), current_database();
SELECT extversion FROM pg_extension WHERE extname='pg_stat_statements' AND extnamespace='public'::regnamespace;
CREATE TEMP TABLE academy_info_before AS SELECT clock_timestamp() AS captured_at, stats_reset, dealloc FROM public.pg_stat_statements_info;
CREATE TEMP TABLE academy_stat_before AS SELECT dbid,userid,queryid,toplevel,stats_since,calls,total_exec_time,rows,shared_blks_read,temp_blks_written FROM public.pg_stat_statements WHERE dbid=(SELECT oid FROM pg_database WHERE datname=current_database());
\prompt 'Wait for the representative interval, then press Enter: ' interval_doneA private baseline exists; shared counters remain intact.