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.

Ce contenu a été publié dans code, system. Vous pouvez le mettre en favoris avec ce permalien.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*