Postgres tricks

From xoa

Jump to: navigation, search

Find info about table "testbook". If this query returns rows, the table has been analyzed.

select * from pg_statistic where starelid='testbook'::regclass;

Get a list of all tables that have been analyzed:

select distinct starelid::regclass from pg_statistic;

starelid is an oid, you can cast it to regclass or join it against pg_class.oid.

pg_statistic is obviously superuser-access-only since it can reveal data from tables. pg_stats is a view on pg_statistic that has schema and table names rather than oids, and it restricts output to those tables you have permission to access.

vacuum and analyze both allow a verbose modifier that tells you what it's done.

How to get sizes of things

http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

flr=# select pg_total_relation_size('testbook') / 1024/1024;
 ?column? 
----------
     7455

You can also use pg_relation_size() for just one relation.

Personal tools