Skip to main content
100%

find long running query in postgres

✓ Published0🌍 Public
Aadkdev
Last edited Jan 15, 2019
Created on Jan 15, 2019

This example displays PostgreSQL processes that have been running for more than five minutes, highlighting long-running queries for database monitoring. It uses a SQL query against `pg_stat_activity` to retrieve process IDs, query text, waiting status, wait reasons, and start times, filtering out idle sessions and ordering by duration. The visualization presents these results as a text-based table or list, with the data sourced directly from the live database system catalog. The code relies on standard PostgreSQL system functions like `now()` and interval comparisons to identify and rank the most time-consuming queries.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist 4a460749f5f2d01019812d536ed42648

This example showcases a set of PostgreSQL client configuration settings designed to format query output for readability and analysis. It defines a custom prompt, enables timing information, sets unicode and wrapped table borders, and activates expanded display for wide results. A slow-query log pulls the top 100 queries by total time from the `pg_stat_statements` view, calculating average execution time per call. The configuration relies on standard `psql` meta-commands like `\pset`, `\set`, and `\x`, with no external visualization libraries involved.

XXXX
58% match
Loading thumbnail…

Close all connections from a Postgres database

This example shows how to forcibly terminate every active connection to a named Postgres database except the current session. The SQL query selects all rows from `pg_stat_activity` and calls `pg_terminate_backend(pid)` for each matching process ID, filtering by `datname = 'database'` and excluding the backend’s own PID. The query returns the full activity listing alongside the termination result for each row. The visualization relies solely on the Postgres system catalog function, with no external charting libraries or rendering pipeline.

LLuisSevillano
55% match
Loading thumbnail…

Postgres is Enough

The visualization maps PostgreSQL’s ecosystem onto a network graph, showing how the database extends into background jobs, search, APIs, and data visualization. It uses a force-directed layout with `d3.forceSimulation` to position nodes representing tools like pg_cron, PostGIS, pgvector, and Metabase, while links connect related categories. The graph is rendered as an SVG with D3’s `d3.select` and `d3.line`, with node sizes encoding the number of associated libraries. Data comes from the static markdown file listing each tool’s URL and category.

Aapburnes
54% match
Loading thumbnail…

Row count for all tables in a postgres database

This visualization ranks every table in a PostgreSQL database by its live row count, displaying the table names and schemas in descending order. The horizontal bar chart makes it immediately clear which tables dominate the database in size and how quickly the counts taper off. The data comes from the `pg_stat_user_tables` system view, which tracks `n_live_tup` (an estimate of live rows). The query runs directly against PostgreSQL, returning `schemaname`, `relname`, and `n_live_tup` for each table, and the chart renders those values as proportional bars.

Aamccartney
54% match
Loading thumbnail…

PostgreSQL command line cheatsheet

This example presents a text-based cheat sheet for PostgreSQL command-line tools, rendering a curated list of psql commands, configuration snippets, and handy SQL queries in a plain markdown document. It shows the core syntax for database management tasks, such as connecting to databases, listing tables and views, managing user roles, and adjusting server logging verbosity, alongside query examples for inspecting indexes, active sessions, and table sizes. The visualization relies on static code blocks and inline formatting, with no interactive graphics or JavaScript libraries; instead, it leverages the raw text content from a gist, emphasizing practical API references like `psql` meta-commands and `pg_stat_activity` queries.

AAngieLiang
53% match
Loading thumbnail…

PostgreSQL installation tutorial

This example presents a step-by-step guide for installing PostgreSQL across macOS, Linux, and Windows. It shows the terminal command `psql -U postgres` as the target for verifying a successful setup, then details OS-specific actions, such as using the Postgres.app on macOS, compiling from source via `wget` on Linux, and using the EnterpriseDB installer on Windows. The tutorial also covers checking the `$PATH` variable and verifying the server port with `lsof`.

115Dkatz
49% match
Loading thumbnail…

Gist ed5d1681970062ebe8088ea3fdbb896f

This example shows a live query log for a Power BI or Analysis Services session by displaying the most recent command timestamps and command texts. It starts with an empty table, loads prior log rows, then queries the current session’s last command data using the `MetaQuery` and `SELECT` statements against the `$SYSTEM.DISCOVER_SESSIONS` schema. The code filters out its own log entries, compares timestamps to keep only new rows since the last refresh, and appends them to the existing log. The rendering relies on Power Query’s table operations (`Table.FromRows`, `Table.SelectRows`, `Table.Combine`) rather than a visual JavaScript library.

HHugoberry
49% match
Loading thumbnail…

Gist 11250891

This example shows a PostgreSQL query plan for a Django ORM query that retrieves inventory events by filtering on stock items associated with a specific item model. The visualization renders the EXPLAIN output as a tree of hash joins and sequential scans, using the `explain` command to expose query execution details. The code includes Python Django model definitions and raw SQL, with the query plan revealing how the database optimizes multiple LEFT OUTER JOINs and subqueries against inventory, booking, purchase, and cross-hire tables.

Aandybak
49% match