Skip to content

Instantly share code, notes, and snippets.

@techgaun

techgaun/postgres_queries_and_commands.sql

Forked from rgreenjr/postgres_queries_and_commands.sql
Last active May 7, 2024 14:44
Show Gist options
  • Save techgaun/843d9c6ecdcc1ae6df5e678c521c293b to your computer and use it in GitHub Desktop.
Save techgaun/843d9c6ecdcc1ae6df5e678c521c293b to your computer and use it in GitHub Desktop.
Download ZIP
Useful PostgreSQL Queries and Commands
Raw
postgres.sql
-- show running queries
SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-- kill running query
SELECT pg_cancel_backend(procpid);
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment