PostgreSQL 便利クエリ集

いろんなところから集めてきてます。

バージョン

$ psql --version
psql (PostgreSQL) 9.3.5

容量使っているテーブル上位10を表示したい

参考：http://qiita.com/awakia/items/99c3d114aa16099e825d

SELECT
  pgn.nspname,
  relname,
  pg_size_pretty(relpages::bigint * 8 * 1024) AS size,
  CASE
    WHEN relkind = 't'
    THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid)
    WHEN nspname = 'pg_toast' AND relkind = 'i'
    THEN (SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', ''))
    ELSE (SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.oid)
  END::varchar AS refrelname,
  CASE
    WHEN nspname = 'pg_toast' AND relkind = 'i'
    THEN (SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid = (SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '')))
  END AS relidxrefrelname,
  relfilenode,
  relkind,
  reltuples::bigint,
  relpages
FROM pg_class pg, pg_namespace pgn
WHERE pg.relnamespace = pgn.oid AND pgn.nspname NOT IN ('information_schema', 'pg_catalog')
ORDER BY relpages DESC
limit 10; 

テーブルごとのvacuumの最後の実行時間が知りたい

select * from pg_stat_all_tables where relname = 'テーブル名';

デッドロックの状況が知りたい

参考：http://longkey1.net/blog/2012/11/08/postgresql-practice-when-deadlock/
参考：http://zx.jpn.org/b/20141026/386/postgresql/postgresql-howto-find-lock-sql

select *
from pg_locks
left join pg_stat_activity using(pid);

ついでにロックを解除

プロセスID = pid

SELECT pg_cancel_backend(プロセスID);

文字列をbase64エンコード/デコード

エンコード

select encode('dummy', 'base64');

デコード

select encode(decode('ZHVtbXk=', 'base64'), 'escape');