get a list of postgres table and index sizes

gistfile1.sh

DBNAME=
QUERY="select tablename from pg_catalog.pg_tables where tablename not like 'sql_%' and tablename not like 'pg_%';"
for table in `psql $DBNAME -t -c "$QUERY"`; do
    TABLE_QUERY="select pg_size_pretty(pg_relation_size('$table'));"
    TABLE_SIZE=`psql $DBNAME -t -c "$TABLE_QUERY"`

    TOTAL_QUERY="select pg_total_relation_size('$table');"
    TOTAL=`psql $DBNAME -t -c "$TOTAL_QUERY"`

    INDEX_QUERY="select pg_size_pretty(pg_total_relation_size('$table') - pg_relation_size('$table'));"
    INDEX=`psql $DBNAME -t -c "$INDEX_QUERY"`

    echo $TOTAL $table index: $INDEX table: $TABLE_SIZE
done