Find slow queries in Postgres

Sql

Ranks statements by total time spent. Needs the pg_stat_statements extension enabled.

SELECT
  round(total_exec_time::numeric, 1) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Download

Databases postgres performance chris

More in Databases

Reset a Postgres sequence after a bulk import Databases Copying rows with explicit ids leaves the identity sequence behind, so the next insert collides with an existing row. Table sizes, largest first Databases Where the disk actually went, including indexes and TOAST. Which indexes is nobody using? Databases Lists indexes that have never been scanned, which are pure write cost. Check against a long uptime before dropping any. Group rows into a JSON array Databases Returns one row per author with their posts nested, instead of a join that repeats the author on every row. Delete duplicate rows, keeping the oldest Databases Uses a window function to number the duplicates and removes everything but the first of each group. Add a column without locking the table Databases On Postgres 11 and later a nullable column with a default is a metadata-only change. Adding NOT NULL afterwards is the part that needs care. Upsert a row in SQLite Databases Inserts, or updates the existing row when the unique column already holds that value.

Random picks

Shell into a running container Containers Falls back to sh, since slim images often have no bash. Retry a fetch with exponential backoff JavaScript Backs off on each failure and adds jitter, so a recovering service is not hit by every client at the same moment. Prepared statement with PDO PHP Placeholders rather than interpolation. This is the whole of SQL injection defence in PHP. Find the commit that introduced a bug Git Binary search across history. Mark a known good and a known bad commit and git narrows it down for you. Centre a box, two ways Web & CSS Grid is one line. Flex is three and gives you control over the axes separately. Pin a dependency to an exact version Build & CI Caret ranges are how a build that passed last week fails today. Exact versions plus a lockfile remove the surprise. Call a JSON API from PowerShell Networking Invoke-RestMethod parses the response for you, so no ConvertFrom-Json step is needed. A regular expression for ISO 8601 dates Text Processing Matches a date, optionally with a time and a zone. Deliberately strict about separators.