Skip to content

Instantly share code, notes, and snippets.

@rgreenjr
Last active October 9, 2024 20:30
Show Gist options
  • Save rgreenjr/3637525 to your computer and use it in GitHub Desktop.
Save rgreenjr/3637525 to your computer and use it in GitHub Desktop.
Useful PostgreSQL Queries and Commands
-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-- kill running query
SELECT pg_cancel_backend(procpid);
-- kill idle query
SELECT pg_terminate_backend(procpid);
-- vacuum command
VACUUM (VERBOSE, ANALYZE);
-- all database users
select * from pg_stat_activity where current_query not like '<%';
-- all databases and their sizes
select * from pg_user;
-- all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;
-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;
-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;
-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > dump.sql
-- Import dump into existing database
$ psql -d newdb -f dump.sql
@tarkhil
Copy link

tarkhil commented Feb 10, 2019

"Show running queries" does not show running queries.

Correct request is

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

or maybe

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state not like 'idle%' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

depending on what you want. First request shows current query or last query in uncompleted transaction

@sandrovw64
Copy link

For Postgres 9.3
Script to kill all running connections of a current database

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();

@awsiv
Copy link

awsiv commented Jan 30, 2020

Find cardinality of index:

SELECT relname, relkind, reltuples as cardinality, relpages 
FROM pg_class 
WHERE relname LIKE 'tableprefix%';

Connection type and count:

SELECT count(*),
       state
FROM pg_stat_activity
GROUP BY 2;

@horpto
Copy link

horpto commented Feb 18, 2020

Hi, @rgreenjr
It's better to replace new line characters in query to spaces replace(query, E'\n', ' ') or It will explode your brain on multiline queries.

@sandrovw64
Copy link

Restart all sequences

SELECT 'ALTER SEQUENCE ' || relname || ' RESTART;' FROM pg_class WHERE relkind = 'S';

@bagatonovic-remesh
Copy link

This is very awesome and useful. I've used this enough times that I had to stop by and say thanks!

@Diwas777
Copy link

Diwas777 commented Apr 22, 2020

Suppose I have a function with a parameter and is called from any backend language. How can I know what parameter is sent in PostgreSQL?
pg_stat_activity is only giving me what query is run. How do I know which value in parameter is used in the PostgreSQL function?

Please help me?
Thanks in advance.

same thinks can be easily done with SQL profiler in the SQL server.

Image attached below explaining what I like to know?

parameter

@himachalapathyp
Copy link

I used pg_stat_activity view to get the PID of the postgres session. How do you find the program that opened that session and its corresponding operating system process id.

@sandrovw64
Copy link

sandrovw64 commented May 5, 2020

I used pg_stat_activity view to get the PID of the postgres session. How do you find the program that opened that session and its corresponding operating system process id.

I believe its not possible, you can get only the database user that is running it, not the application nor OS.
It is a good practice to have different database users for each application that you allow access to your database exactly for this reason.

@dbamohsin
Copy link

Find cardinality of index:

SELECT relname, relkind, reltuples as cardinality, relpages 
FROM pg_class 
WHERE relname LIKE 'tableprefix%';

a slightly neater way of presenting the query above..

-- Find cardinality of index
SELECT schema_name,
       object_name,
       object_type,
       cardinality,
       pages
FROM (
       SELECT pg_catalog.pg_namespace.nspname AS schema_name,
              relname                         as object_name,
              relkind                         as object_type,
              reltuples                       as cardinality,
              relpages                        as pages
       FROM pg_catalog.pg_class
              JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
  and schema_name <> 'information_schema'
  --and schema_name = '$schema_name'
  --and object_name = '$object_name'
ORDER BY pages DESC, schema_name, object_name
;

@ld0906
Copy link

ld0906 commented Jun 15, 2020

Suppose I have a function with a parameter and is called from any backend language. How can I know what parameter is sent in PostgreSQL?
pg_stat_activity is only giving me what query is run. How do I know which value in parameter is used in the PostgreSQL function?

Please help me?
Thanks in advance.

same thinks can be easily done with SQL profiler in the SQL server.

Image attached below explaining what I like to know?

parameter

@Diwas777, I am facing same request, did you get a solution to get parameter values? Thanks~!

@kallol29
Copy link

Hi
Can anybody provide me the query to fetch of CPU utilization for each query. From pg_stat_activity I can get the pid and from top or ps I can get the CPU utilization. I want one query to fetch the details

@JonEngle
Copy link

Hey Ron, small world.....I was looking for a few postgres queries and you popped up to the top of list!

@HariSekhon
Copy link

You guys might also be interested in my SQL-scripts repo which contain lots of SQL scripts for PostgreSQL and MySQL tested across all versions of PostgreSQL / MySQL / MariaDB - with fully documented headers showing version support and variations to account for version differences:

https://github.com/HariSekhon/SQL-scripts

There are also Google BigQuery SQL queries in there, as well as AWS Athena CloudTrail logs integration DDL, and I have Snowflake queries pending adding there too.

Additionally, in my adjacent DevOps Bash tools repo I've put my advanced .psqlrc and written scripts for one-touch test environments that boot any version of PostgreSQL, MySQL and MariaDB available in docker and drop you straight in to a psql / mysql shell with all the SQL scripts available in $PWD for fast easy testing and sourcing, as well as scripts to test any SQL script(s) against a list of versions of PostgreSQL / MySQL / MariaDB to determine its compatibility across versions (I used this to determine the version compatibility for every interesting SQL query which is documented in the headers in each SQL script)

@alamk6043
Copy link

How to find list of tables,which have not been accessed and their last accessed time

@robfordww
Copy link

robfordww commented Jun 15, 2021

Seems like the "[IDLE]" syntax is deprecated, perhaps this is better for active queries on modern Postgres:

SELECT age(clock_timestamp(), query_start), *
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

@rodrigues-pedro
Copy link

rodrigues-pedro commented Jun 15, 2021

Very Usefull, Sadly I allways come looking for this one:

select pid, pg_blocking_pids(pid) as blocked_by, query as blocked_query
from pg_stat_activity
where pg_blocking_pids(pid)::text != '{}';

@mencargo
Copy link

Great post, but outdated, I found myself creating my own version of the scripts often, so I decided to make a gist:
https://gist.github.com/mencargo/79447185034ebabcb49087008fbdc266

@HariSekhon
Copy link

@mencargo did you see my SQL-scripts repo mentioned above? I have different versions of these SQL queries for different versions of PostgreSQL / MySQL / MariaDB which are programmatically tested on all the different versions.

@mencargo
Copy link

@mencargo did you see my SQL-scripts repo mentioned above? I have different versions of these SQL queries for different versions of PostgreSQL / MySQL / MariaDB which are programmatically tested on all the different versions.

Yep, great collection, a bit advanced for my level.
Mine is just a single page, basic but useful stuff to have at hand, focused only on recent PostgreSQL.

@HariSekhon
Copy link

HariSekhon commented Jun 22, 2021

If you need SQL queries that work on a specific DB version they're all tested and documented in the headers of each script there all the way up to recent Postgres 13.

They're also mostly PostgreSQL because there are just so many useful queries and interesting things exposed to query in postgres SQL.

@tallpeak
Copy link

Possible division by zero when scanning index usage rates, fix:

  CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan) 
    ELSE 0
  END AS percent_of_times_index_used,

I usually use x/NULLIF(possible0, 0) for this. Whether MSSQL or PG, etc.

@HariSekhon
Copy link

Possible division by zero when scanning index usage rates, fix:

  CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan) 
    ELSE 0
  END AS percent_of_times_index_used,

I usually use x/NULLIF(possible0, 0) for this. Whether MSSQL or PG, etc.

That case statement pre-checks for zero and only runs that division if it's non-zero.

Checking my scripts, I've done it like this, which I think is more concise:

100 * idx_scan / GREATEST(seq_scan + idx_scan, 1)

taken from:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_tables_index_usage.sql

@singals
Copy link

singals commented Mar 5, 2022

top 10 tables with their size:

select schemaname as table_schema,
    relname as table_name,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_relation_size(relid)) as data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
      as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
         pg_relation_size(relid) desc
limit 10;

@tychodaimon
Copy link

-- show running queries (11+)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

@HariSekhon
Copy link

@tychodaimon that query almost identical to one I have here which is currently tested on PostgreSQL 9.2 - 13.0:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_running_queries.sql

@tychodaimon
Copy link

tychodaimon commented May 18, 2022

@HariSekhon You right, i not sure from which version it started, I get it on 11, 12 and also on 13, but condition query != '<IDLE>' not filter idle queries, only state != 'idle' give you list filtered from idle. (sorry I was thinking that You are author of this page)

@tychodaimon
Copy link

tychodaimon commented May 18, 2022

@rgreenjr And thanks for this page! I use Your queries for many years, today I put comment because just tired to fix condition after copy and paste all the time

@HariSekhon
Copy link

@tychodaimon yes I used state != 'idle' in that query too.

I documented my scripts with the versions of Postgres they work on.

You can then copy/paste or source from live psql prompt for the version of Postgres you're running.

The default scripts work on the latest versions of Postgres while the version suffixed scripts are maintained for older versions.

I also have other scripts there to run the latest scripts all on new versions to check if anything has changed/broken.

@tychodaimon
Copy link

@HariSekhon Thank you very much. You did a great job! I am studying your repository now. I commented here because this is the page that google gives me for "postgres running queries" which I am used to

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment