Some PostgreSQL tips

Indexes usage, ordered by index size

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelname))
FROM pg_stat_all_indexes WHERE schemaname = 'public' ORDER BY pg_relation_size(indexrelname) DESC;

Sample output :

Index / Sequential scans, by table

SELECT relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch
FROM pg_stat_user_tables ORDER BY seq_scan desc;

Sample output :

GET all tables and columns

SELECT pg_tables.tablename,columns.column_name
FROM pg_tables,information_schema.columns columns
WHERE pg_tables.tablename=columns.table_name AND pg_tables.schemaname='public'
ORDER by pg_tables.tablename;

Longest queries

First you have to activate postgreql queries log with, for example ::

log_min_duration_statement = 1000 log_statement = 'all'

Then, to see the 100 longest queries yo can do :

grep "CEST LOG" /var/log/postgresql/postgresql-8.3-main.log|tail -1000|sort -n -k 7 | tail -100