Postgresql table and index size

Here is a Postgresql requests which will allows you to view the size of your tables with their idx_scan and seq_scan counts.

SELECT s.schemaname, s.relname, c.oid, c.relfilenode, s.seq_scan,s.idx_scan,
       c.relpages AS pages
  FROM pg_stat_all_tables AS s, pg_class AS c
  WHERE s.relname = c.relname
  AND s.schemaname IN (SELECT nspname FROM pg_namespace WHERE oid = c.relnamespace)
  AND ( s.schemaname = 'public' OR s.schemaname = 'pg_toast' );

Another one to view the size of your indexes :

SELECT s.schemaname, s.relname, s.indexrelname,  c.oid, c.relfilenode,
       c.relpages AS pages
  FROM pg_stat_all_indexes as s, pg_class AS c
  WHERE s.indexrelname = c.relname
  AND s.schemaname IN (SELECT nspname FROM pg_namespace WHERE oid = c.relnamespace)
  AND ( s.schemaname = 'public' OR s.schemaname = 'pg_toast' );

The size is reported in the `pages’ column, and in most case one page is 8KB (postgresql compiled with default settings) .

The `seq_scan’ and `idx_scan’ allows you to view own many sequential scans and index scans are performed on your table. Too many sequential scans on a huge table might be a sign for a missing index that could help traverse the table more efficiently.

These statistics might help you optimize your requests/indexes, and to fine tune your Postgresql shared_buffers parameter.