Forked from rgreenjr/postgres_queries_and_commands.sql
Last active
May 1, 2023 09:00
-
-
Save bakongo/0fb9882712edd1797d5cf5e69fa4e8cf to your computer and use it in GitHub Desktop.
Useful PostgreSQL Queries and Commands
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- show running queries (pre 9.2) | |
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; | |
-- show running queries (9.2) | |
SELECT pid, age(query_start, clock_timestamp()), usename, state, application_name, query | |
FROM pg_stat_activity | |
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND state != 'idle' | |
ORDER BY query_start desc; | |
SELECT pid, age(query_start, clock_timestamp()), usename, query | |
FROM pg_stat_activity | |
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' | |
ORDER BY query_start desc; | |
-- what is running, who is running it, sorted by how long it's been running for | |
SELECT pid, age(query_start, clock_timestamp()), usename , state, application_name | |
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; | |
-- all tables and index sizes over 5mg, plus cumulative total count | |
SELECT | |
table_name, | |
pg_size_pretty(table_size) AS table_size, | |
pg_size_pretty(indexes_size) AS indexes_size, | |
pg_size_pretty(total_size) AS total_size, | |
sum(total_size) OVER(ORDER BY total_size desc) AS total | |
FROM ( | |
SELECT | |
table_name, | |
pg_table_size(table_name) AS table_size, | |
pg_indexes_size(table_name) AS indexes_size, | |
pg_total_relation_size(table_name) AS total_size | |
FROM ( | |
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name | |
FROM information_schema.tables | |
WHERE table_schema = 'public' | |
) | |
AS all_tables | |
where pg_table_size(table_name) > 516096 | |
ORDER BY total_size DESC | |
) AS pretty_sizes; | |
-- 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; | |
-- which indexes are not used or used rarely | |
SELECT s.schemaname, | |
s.relname AS tablename, | |
s.indexrelname AS indexname, | |
s.idx_scan, | |
pg_relation_size(s.indexrelid) AS index_size | |
FROM pg_catalog.pg_stat_user_indexes s | |
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid | |
WHERE s.idx_scan < 1000 -- has never been scanned | |
AND 0 <>ALL (i.indkey) -- no index column is an expression | |
AND NOT i.indisunique -- is not a UNIQUE index | |
AND NOT EXISTS -- does not enforce a constraint | |
(SELECT 1 FROM pg_catalog.pg_constraint c | |
WHERE c.conindid = s.indexrelid) | |
ORDER BY pg_relation_size(s.indexrelid) DESC; | |
-- which indexes are used most to least | |
SELECT s.schemaname, | |
s.relname AS tablename, | |
s.indexrelname AS indexname, | |
s.idx_scan, | |
pg_relation_size(s.indexrelid) AS index_size | |
FROM pg_catalog.pg_stat_user_indexes s | |
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid | |
WHERE 0 <>ALL (i.indkey) -- no index column is an expression | |
AND NOT i.indisunique -- is not a UNIQUE index | |
AND NOT EXISTS -- does not enforce a constraint | |
(SELECT 1 FROM pg_catalog.pg_constraint c | |
WHERE c.conindid = s.indexrelid) | |
ORDER BY s.idx_scan DESC; | |
-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment