As a PostgreSQL DBA, my major work was retrieving the database information using pg_catalogs. You all know, pg_catalogs in PostgreSQL has a very good informative function,view etc., So, I have come up with few queries which will give a statistical information at first, when you get a hands on to the DB box. I am enclosing few queries which will help you in getting very good information on the PostgreSQL Database Server.
1. Find the number of live rows vs dead rows of a table::
select relname as "Table",
n_live_tup as "Live tuples",
n_dead_tup as "Dead tuples"
from pg_stat_user_tables
where relname = 'my_table';
2. Finding the read activity statistics of tables in a database::
select schemaname as "Schema Name" ,
relname as "Table Name",
seq_tup_read+idx_tup_fetch as " no.of reads”
from pg_stat_all_tables
order by seq_tup_read+idx_tup_fetch;
3. Finding the write activity statistics of tables in a database::
select schemaname as " Schema Name",
relname as "Table Name”,
n_tup_ins+n_tup_upd+n_tup_del as "no.of writes"
from pg_stat_all_tables
order by n_tup_ins+n_tup_upd+n_tup_del;
4. Find out the I/O statistics of a table::
select relname as "Table",
heap_blks_read as "Heap from disc",
heap_blks_hit as "Heap from cache",
idx_blks_read as "Index from disc",
idx_blks_hit as "Index from cache",
toast_blks_read as "Toast from disc",
toast_blks_hit as "Toast from cache",
tidx_blks_read as "Toast index disc",
tidx_blks_hit as "Toast index cache"
from pg_statio_user_tables
where relname = 'my_table';
5. Find the size of all databases in a cluster::
select datname as "Database name",
pg_size_pretty(pg_database_size(datname)) as "Size of each database"
from pg_database;
6. Find out the index statistics in a database::
select relname as "Tablename",
indexrelname as "Index name",
idx_scan as "Index lookups",
idx_tup_read as "Index entries",
idx_tup_fetch as "Tuples fetched via index"
from pg_stat_user_indexes
order by relname,indexrelname;
7. Finding the lock statistics in a database ::
select pid as "Process id”,
mode,
current_query as " Sql query"
from pg_locks,pg_stat_activity
where granted=false and locktype='transactionid' and pid=procpid order by pid,granted;
8. Finding the sizes of indexes in a database::
select relname as "Table name",
indexrelname as " Index name",
pg_size_pretty( pg_relation_size( indexrelid ) ) as "Size"
from pg_stat_all_indexes
where schemaname = 'public' and relname='users'
order by pg_relation_size( indexrelid ) desc;
9. Finding the Database statistics in a cluster::
Select datname as "Database",
numbackends as "Backends",
xact_commit as "Xact Committed",
xact_rollback as "Xact Rolled Back",
blks_read as "Blocks Read",
blks_hit as "Blocks Hit",
(pg_database_size(datid) / 1024)::int as "Size (KB)"
FROM pg_stat_database db
WHERE UPPER(db.datname) != 'TEMPLATE0' AND UPPER(db.datname) != 'TEMPLATE1'
ORDER BY "Database";
10. Finding the number of users connected to each Database in a cluster::
SELECT COUNT(datid) as "Count",
datconnlimit "Connection Limit per Database",
d.datname as "Database Name"
FROM pg_database d LEFT JOIN pg_stat_activity s ON (s.datid = d.oid) GROUP BY 2,3 ORDER BY d.datname;
11. Size of each table inside the Database on PostgreSQL 8.4 on-words::
SELECT tablename as " Table Name",
pg_size_pretty(pg_total_relation_size(CAST(tablename AS TEXT))) as “Total size of table",
pg_size_pretty((pg_total_relation_size(CAST(tablename AS TEXT)) - pg_relation_size(CAST(tablename AS
TEXT)))) as "Index size”
FROM pg_tables
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' ORDER BY pg_total_relation_size(CAST(tablename AS TEXT));
12.List of tables in a Database::
SELECT relname as "Table Name”
FROM pg_class
WHERE relname !~ '^(pg_|sql_)' AND relkind = 'r';
13.List of sequences::
SELECT relname as " Sequence Name”
FROM pg_class
WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' );
14. List Triggers of a table::
SELECT trg.tgname as "Trigger_name"
FROM pg_trigger trg, pg_class tbl
WHERE trg.tgrelid = tbl.oid AND tbl.relname = 'newtable';
15.Which tables are being updated the most and looking for vacuum ::
select relname, /* pg_size_pretty( pg_relation_size( relid ) ) as table_size,
pg_size_pretty( pg_total_relation_size( relid ) ) as table_total_size, */
n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum::date, last_autovacuum::date, last_analyze::date, last_autoanalyze::date
from pg_stat_all_tables
where relid in (select oid from pg_class
where relnamespace not in (select oid from pg_namespace
where nspname in ('information_schema', 'pg_catalog','pg_toast', 'edbhc' ) ) )
order by n_tup_upd desc, schemaname, relname;
16. How to get the indexes scanned less than 200 times and is not unique::
SELECT idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(idstat.relname)) AS table_size,
pg_size_pretty(pg_relation_size(indexrelname)) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE idstat.idx_scan < 200
AND indexdef !~* 'unique'
ORDER BY idstat.relname, indexrelname;
17. Size of each schema in a Database::
SELECT nspname, sum(relpages * cast( 8192 AS bigint )) as "table size", sum( ( select sum(relpages) from pg_class i, pg_index idx where i.oid = idx.indexrelid and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size", sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages) from pg_class i, pg_index idx where i.oid = idx.indexrelid and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size" FROM pg_class t, pg_namespace WHERE relnamespace = pg_namespace.oid and pg_namespace.nspname not like 'pg_%' and pg_namespace.nspname != 'information_schema' and relkind = 'r' group by nspname;
18. Find out how many pages and tuples are used by a table::
select relname as "table",
reltuples as "number of tuples",
relpages as "number of 8kb pages"
from pg_class
where relname = 'table';
19. Find out the Primary key tables in a database::
SELECT tablename FROM pg_tables WHERE tablename IN? (SELECT r.relname FROM pg_class r, pg_constraint c WHERE r.oid = c.conrelid AND c.contype = 'p') AND schemaname = 'public';
20. Which user PID is locking on which transaction process::
select bl.pid as blocked_pid, a.usename as blocked_user,kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement from pg_catalog.pg_locks bl
join pg_catalog.pg_stat_activity a on bl.pid = a.procpid
join pg_catalog.pg_locks kl
join pg_catalog.pg_stat_activity ka on kl.pid = ka.procpid on bl.transactionid = kl.transactionid and bl.pid != kl.pid
where not bl.granted;
21. Checking the user permissions::
select relname as "Relation", relacl as "Access permissions" FROM pg_class
WHERE relkind IN ('r', 'v', 'S') AND relname !~ '^pg_' ORDER BY relname;
