Created
June 7, 2018 08:12
-
-
Save analytik/3faacbfe8f34b97ade5be7dbddc1feaa to your computer and use it in GitHub Desktop.
A collection of PostgreSQL queries and reminders
This file contains hidden or 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
| -- See here for an explanation on when is restart or reload needed: | |
| -- https://blog.dbi-services.com/which-parameter-changes-do-require-a-restart-of-my-postgresql-instance/ | |
| show random_page_cost ; | |
| -- this will make reads better on SSDs and really should be the default: | |
| alter system set random_page_cost=1; | |
| select * from pg_reload_conf(); | |
| show random_page_cost ; | |
| -- default 64MB and 4MB - remember the latter is per query, so this could make the system DOS-vulnerable | |
| ALTER SYSTEM set maintenance_work_mem = '16GB'; | |
| ALTER SYSTEM SET work_mem = '128MB'; | |
| -- in psql, this will let you see queries behind \d+ and such | |
| \set ECHO_HIDDEN on | |
| -- so for example an index list by size would be | |
| SELECT n.nspname as "Schema", | |
| c.relname as "Name", | |
| pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size" FROM pg_catalog.pg_class c | |
| LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
| LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid | |
| LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid | |
| WHERE c.relkind IN ('i','') | |
| AND n.nspname <> 'pg_catalog' | |
| AND n.nspname <> 'information_schema' | |
| AND n.nspname !~ '^pg_toast' | |
| AND pg_catalog.pg_table_is_visible(c.oid) | |
| ORDER BY pg_catalog.pg_table_size(c.oid) DESC,1,2; | |
| -- and a plain table list by size would be | |
| SELECT n.nspname as "Schema", | |
| c.relname as "Name", | |
| pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size" | |
| FROM pg_catalog.pg_class c | |
| LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
| WHERE c.relkind IN ('r','p','v','m','S','f','') | |
| AND n.nspname <> 'pg_catalog' | |
| AND n.nspname <> 'information_schema' | |
| AND n.nspname !~ '^pg_toast' | |
| AND pg_catalog.pg_table_is_visible(c.oid) | |
| ORDER BY pg_catalog.pg_table_size(c.oid) DESC,1,2; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment