433
Comment: converted to 1.6 markup
|
← Revision 4 as of 2013-05-02 21:51:51 ⇥
1308
|
Deletions are marked like this. | Additions are marked like this. |
Line 3: | Line 3: |
{{{SELECT DISTINCT a.procpid, a.current_query, 'is waiting on' AS "is waiting on", d.procpid, d.current_query FROM pg_stat_activity a join pg_locks b ON (a.procpid = b.pid) JOIN pg_locks c ON (b.relation = c.relation) JOIN pg_stat_activity d ON (c.pid = d.procpid) WHERE a.waiting = 't' AND b.granted = 'f' AND c.granted = 't'}}} | {{{ select distinct a.procpid as procpid1, a.current_query as current_query1, 'is waiting on' as "is waiting on", d.procpid, d.current_query from pg_stat_activity a join pg_locks b on (a.procpid = b.pid) join pg_locks c on (b.relation = c.relation) join pg_stat_activity d on (c.pid = d.procpid) where a.waiting = 't' and b.granted = 'f' and c.granted = 't'; }}} * To list which db tables are using TOAST tables, and how many of them: {{{ SELECT c.nspname, a.relname AS tablename, a.reltoastrelid::regclass AS toastname, b.relpages as usedpages FROM pg_class a JOIN pg_class b ON (a.reltoastrelid = b.oid) JOIN pg_namespace c ON (a.relnamespace = c.oid) WHERE a.reltoastrelid != 0 ORDER BY b.relpages DESC; }}} * To query the db to find out if a specific db table is using TOAST tables: {{{ SELECT c.nspname, a.relname AS tablename, a.reltoastrelid::regclass AS toastname, b.relpages as usedpages FROM pg_class a JOIN pg_class b ON (a.reltoastrelid = b.oid) JOIN pg_namespace c ON (a.relnamespace = c.oid) WHERE a.reltoastrelid != 0 AND c.nspname || '.' || a.relname = '<your schemaname.tablename here>' ORDER BY b.relpages DESC; }}} |
Useful Queries
* To see which postgres transactions are blocked on which postgres transactions:
select distinct a.procpid as procpid1, a.current_query as current_query1, 'is waiting on' as "is waiting on", d.procpid, d.current_query from pg_stat_activity a join pg_locks b on (a.procpid = b.pid) join pg_locks c on (b.relation = c.relation) join pg_stat_activity d on (c.pid = d.procpid) where a.waiting = 't' and b.granted = 'f' and c.granted = 't';
* To list which db tables are using TOAST tables, and how many of them:
SELECT c.nspname, a.relname AS tablename, a.reltoastrelid::regclass AS toastname, b.relpages as usedpages FROM pg_class a JOIN pg_class b ON (a.reltoastrelid = b.oid) JOIN pg_namespace c ON (a.relnamespace = c.oid) WHERE a.reltoastrelid != 0 ORDER BY b.relpages DESC;
* To query the db to find out if a specific db table is using TOAST tables:
SELECT c.nspname, a.relname AS tablename, a.reltoastrelid::regclass AS toastname, b.relpages as usedpages FROM pg_class a JOIN pg_class b ON (a.reltoastrelid = b.oid) JOIN pg_namespace c ON (a.relnamespace = c.oid) WHERE a.reltoastrelid != 0 AND c.nspname || '.' || a.relname = '<your schemaname.tablename here>' ORDER BY b.relpages DESC;