diff options
Diffstat (limited to 'docs/_queries/postgres.md')
| -rw-r--r-- | docs/_queries/postgres.md | 305 |
1 files changed, 0 insertions, 305 deletions
diff --git a/docs/_queries/postgres.md b/docs/_queries/postgres.md deleted file mode 100644 index e3deb82..0000000 --- a/docs/_queries/postgres.md +++ /dev/null @@ -1,305 +0,0 @@ ---- -layout: default -title: PostgreSQL ---- - -# PostgreSQL queries - -## Disk usage - -Most of these queries vary based on the database you are connected to. - -### General Table Size Information Grouped For Partitioned Tables - -```sql -WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS - (select inhrelid, inhparent - FROM pg_inherits - UNION - SELECT child.inhrelid, parent.inhparent - FROM pg_inherit child, pg_inherits parent - WHERE child.inhparent = parent.inhrelid), -pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit)) -SELECT table_schema - , TABLE_NAME - , row_estimate - , pg_size_pretty(total_bytes) AS total - , pg_size_pretty(index_bytes) AS INDEX - , pg_size_pretty(toast_bytes) AS toast - , pg_size_pretty(table_bytes) AS TABLE - FROM ( - SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes - FROM ( - SELECT c.oid - , nspname AS table_schema - , relname AS TABLE_NAME - , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate - , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes - , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes - , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes - , parent - FROM ( - SELECT pg_class.oid - , reltuples - , relname - , relnamespace - , pg_class.reltoastrelid - , COALESCE(inhparent, pg_class.oid) parent - FROM pg_class - LEFT JOIN pg_inherit_short ON inhrelid = oid - WHERE relkind IN ('r', 'p') - ) c - LEFT JOIN pg_namespace n ON n.oid = c.relnamespace - ) a - WHERE oid = parent -) a -ORDER BY total_bytes DESC; -``` - -### General Table Size Information - -```sql -SELECT *, pg_size_pretty(total_bytes) AS total - , pg_size_pretty(index_bytes) AS index - , pg_size_pretty(toast_bytes) AS toast - , pg_size_pretty(table_bytes) AS table - FROM ( - SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM ( - SELECT c.oid,nspname AS table_schema, relname AS table_name - , c.reltuples AS row_estimate - , pg_total_relation_size(c.oid) AS total_bytes - , pg_indexes_size(c.oid) AS index_bytes - , pg_total_relation_size(reltoastrelid) AS toast_bytes - FROM pg_class c - LEFT JOIN pg_namespace n ON n.oid = c.relnamespace - WHERE relkind = 'r' - ) a -) a; -``` - -### Finding the largest databases in your cluster - -```sql -SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner, - CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') - THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) - ELSE 'No Access' - END as Size -FROM pg_catalog.pg_database d - order by - CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') - THEN pg_catalog.pg_database_size(d.datname) - ELSE NULL - END desc -- nulls first - LIMIT 20; -``` - -### Finding the size of your biggest relations - -Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts. - -```sql -SELECT nspname || '.' || relname AS "relation", - pg_size_pretty(pg_relation_size(C.oid)) AS "size" - FROM pg_class C - LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) - WHERE nspname NOT IN ('pg_catalog', 'information_schema') - ORDER BY pg_relation_size(C.oid) DESC - LIMIT 20; -``` - -### Finding the total size of your biggest tables - -```sql -SELECT nspname || '.' || relname AS "relation", - pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" - FROM pg_class C - LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) - WHERE nspname NOT IN ('pg_catalog', 'information_schema') - AND C.relkind <> 'i' - AND nspname !~ '^pg_toast' - ORDER BY pg_total_relation_size(C.oid) DESC - LIMIT 20; -``` - -## Indexes - -### Index summary - -```sql -SELECT - pg_class.relname, - pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes, - pg_class.reltuples AS num_rows, - count(indexname) AS number_of_indexes, - CASE WHEN x.is_unique = 1 THEN 'Y' - ELSE 'N' - END AS UNIQUE, - SUM(case WHEN number_of_columns = 1 THEN 1 - ELSE 0 - END) AS single_column, - SUM(case WHEN number_of_columns IS NULL THEN 0 - WHEN number_of_columns = 1 THEN 0 - ELSE 1 - END) AS multi_column -FROM pg_namespace -LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace -LEFT OUTER JOIN - (SELECT indrelid, - max(CAST(indisunique AS integer)) AS is_unique - FROM pg_index - GROUP BY indrelid) x - ON pg_class.oid = x.indrelid -LEFT OUTER JOIN - ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x - JOIN pg_class c ON c.oid = x.indrelid - JOIN pg_class ipg ON ipg.oid = x.indexrelid ) - AS foo - ON pg_class.relname = foo.ctablename -WHERE - pg_namespace.nspname='public' -AND pg_class.relkind = 'r' -GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique -ORDER BY 2; -``` - -### Index size/usage statistics - -```sql -SELECT - t.schemaname, - t.tablename, - indexname, - c.reltuples AS num_rows, - pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(t.tablename)::text)) AS table_size, - pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(indexrelname)::text)) AS index_size, - CASE WHEN indisunique THEN 'Y' - ELSE 'N' - END AS UNIQUE, - number_of_scans, - tuples_read, - tuples_fetched -FROM pg_tables t -LEFT OUTER JOIN pg_class c ON t.tablename = c.relname -LEFT OUTER JOIN ( - SELECT - c.relname AS ctablename, - ipg.relname AS indexname, - x.indnatts AS number_of_columns, - idx_scan AS number_of_scans, - idx_tup_read AS tuples_read, - idx_tup_fetch AS tuples_fetched, - indexrelname, - indisunique, - schemaname - FROM pg_index x - JOIN pg_class c ON c.oid = x.indrelid - JOIN pg_class ipg ON ipg.oid = x.indexrelid - JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid -) AS foo ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname -WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema') -ORDER BY 1,2; -``` - -### Duplicate indexes - -```sql -SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size, - (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2, - (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4 -FROM ( - SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| - coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key - FROM pg_index) sub -GROUP BY key HAVING count(*)>1 -ORDER BY sum(pg_relation_size(idx)) DESC; -``` - -## Maintenance - -[PostgreSQL wiki](https://wiki.postgresql.org/wiki/Main_Page) - -### CLUSTER-ing - -[CLUSTER](https://www.postgresql.org/docs/current/sql-cluster.html) - -```sql -CLUSTER [VERBOSE] table_name [ USING index_name ] -CLUSTER [VERBOSE] -``` - -`CLUSTER` instructs PostgreSQL to cluster the table specified by `table_name` based on the index specified by `index_name`. The index must already have been defined on `table_name`. - -When a table is clustered, it is physically reordered based on the index information. - -The [clusterdb](https://www.postgresql.org/docs/current/app-clusterdb.html) CLI tool is recommended, and can also be used to cluster all tables at the same time. - -### VACUUM-ing - -Proper vacuuming, particularly autovacuum configuration, is crucial to a fast and reliable database. - -[Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT](https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT) - -It is not advised to run `VACUUM FULL`, instead look at clustering. VACUUM FULL is a much more intensive task and acquires an ACCESS EXCLUSIVE lock on the table, blocking reads and writes. Whilst `CLUSTER` also does acquire this lock it's a less intensive and faster process. - -The [vacuumdb](https://www.postgresql.org/docs/current/app-vacuumdb.html) CLI tool is recommended for manual runs. - -#### Finding number of dead rows - -```sql -SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup <> 0 ORDER BY 2 DESC; -``` - -#### Finding last vacuum/auto-vacuum date - -```sql -SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; -``` - -#### Checking auto-vacuum is enabled - -```sql -SELECT name, setting FROM pg_settings WHERE name='autovacuum'; -``` - -#### View all auto-vacuum setting - -```sql -SELECT * from pg_settings where category like 'Autovacuum'; -``` - -## Locks - -### Looking at granted locks - -```sql -SELECT relation::regclass, * FROM pg_locks WHERE NOT granted; -``` - -### Сombination of blocked and blocking activity - -```sql -SELECT blocked_locks.pid AS blocked_pid, - blocked_activity.usename AS blocked_user, - blocking_locks.pid AS blocking_pid, - blocking_activity.usename AS blocking_user, - blocked_activity.query AS blocked_statement, - blocking_activity.query AS current_statement_in_blocking_process - FROM pg_catalog.pg_locks blocked_locks - JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid - JOIN pg_catalog.pg_locks blocking_locks - ON blocking_locks.locktype = blocked_locks.locktype - AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database - AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation - AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page - AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple - AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid - AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid - AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid - AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid - AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid - AND blocking_locks.pid != blocked_locks.pid - - JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid - WHERE NOT blocked_locks.granted; -``` |