Last active
February 12, 2018 21:21
-
-
Save seanbyrne88/6e5c8c7aea701c9e342fd55eafa316d6 to your computer and use it in GitHub Desktop.
Useful PG Queries
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, 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); | |
-- show function source | |
SELECT proname,prosrc FROM pg_proc WHERE proname= your_function_name; | |
-- 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; | |
--table/view dependencies | |
SELECT DISTINCT dependent_ns.nspname as dependent_schema | |
, dependent_view.relname as dependent_view | |
, source_ns.nspname as source_schema | |
, source_table.relname as source_table | |
--, pg_attribute.attname as column_name | |
FROM pg_depend | |
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid | |
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid | |
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid | |
JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid | |
AND pg_depend.refobjsubid = pg_attribute.attnum | |
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace | |
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace | |
WHERE | |
source_ns.nspname = 'public' | |
AND source_table.relname = 'my_table' | |
AND pg_attribute.attnum > 0 | |
--AND pg_attribute.attname = 'my_column' | |
ORDER BY 1,2; | |
--list indexes | |
select | |
t.relname as table_name, | |
i.relname as index_name, | |
a.attname as column_name | |
from | |
pg_class t, | |
pg_class i, | |
pg_index ix, | |
pg_attribute a | |
where | |
t.oid = ix.indrelid | |
and i.oid = ix.indexrelid | |
and a.attrelid = t.oid | |
and a.attnum = ANY(ix.indkey) | |
and t.relname like 'vw%' | |
order by | |
t.relname, | |
i.relname; | |
-- describe table | |
SELECT | |
f.attnum AS number, | |
f.attname AS name, | |
f.attnum, | |
f.attnotnull AS notnull, | |
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type, | |
CASE | |
WHEN p.contype = 'p' THEN 't' | |
ELSE 'f' | |
END AS primarykey, | |
CASE | |
WHEN p.contype = 'u' THEN 't' | |
ELSE 'f' | |
END AS uniquekey, | |
CASE | |
WHEN p.contype = 'f' THEN g.relname | |
END AS foreignkey, | |
CASE | |
WHEN p.contype = 'f' THEN p.confkey | |
END AS foreignkey_fieldnum, | |
CASE | |
WHEN p.contype = 'f' THEN g.relname | |
END AS foreignkey, | |
CASE | |
WHEN p.contype = 'f' THEN p.conkey | |
END AS foreignkey_connnum, | |
CASE | |
WHEN f.atthasdef = 't' THEN d.adsrc | |
END AS default | |
FROM pg_attribute f | |
JOIN pg_class c ON c.oid = f.attrelid | |
JOIN pg_type t ON t.oid = f.atttypid | |
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum | |
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace | |
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey) | |
LEFT JOIN pg_class AS g ON p.confrelid = g.oid | |
WHERE c.relkind = 'r'::char | |
AND n.nspname = '%s' -- Replace with Schema name | |
AND c.relname = '%s' -- Replace with table name | |
AND f.attnum > 0 ORDER BY number | |
; | |
-- Function info (Schema, Name, Results, Args, Type) similar to "\df" output | |
SELECT n.nspname as "Schema", | |
p.proname as "Name", | |
pg_catalog.pg_get_function_result(p.oid) as "Result data type", | |
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", | |
p.prosrc as "Function Source", | |
'CREATE OR REPLACE FUNCTION ' || p.proname || '(' || pg_catalog.pg_get_function_arguments(p.oid) || ')' || ' | |
returns ' || pg_catalog.pg_get_function_result(p.oid) || ' AS | |
$$ | |
' || p.prosrc || ' | |
$$ | |
LANGUAGE plpgsql;' as "Full definition", | |
CASE | |
WHEN p.proisagg THEN 'agg' | |
WHEN p.proiswindow THEN 'window' | |
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' | |
ELSE 'normal' | |
END as "Type" | |
FROM pg_catalog.pg_proc p | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace | |
WHERE p.proname ~ '<proc_name>' | |
AND pg_catalog.pg_function_is_visible(p.oid) | |
ORDER BY 1, 2, 4; | |
-- view dependencies | |
SELECT DISTINCT dependent_ns.nspname as dependent_schema | |
, dependent_view.relname as dependent_view | |
, source_ns.nspname as source_schema | |
, source_table.relname as source_table | |
--, pg_attribute.attname as column_name | |
FROM pg_depend | |
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid | |
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid | |
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid | |
JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid | |
AND pg_depend.refobjsubid = pg_attribute.attnum | |
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace | |
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace | |
WHERE | |
source_ns.nspname = 'public' | |
AND source_table.relname = '<view_name>' | |
AND pg_attribute.attnum > 0 | |
ORDER BY 1,2; | |
-- 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