Here is a Postgresql requests which will allows you to view the size of your tables with their idx_scan and seq_scan counts.<\/p>\n
SELECT s.schemaname, s.relname, c.oid, c.relfilenode, s.seq_scan,s.idx_scan,\r\n c.relpages AS pages\r\n FROM pg_stat_all_tables AS s, pg_class AS c\r\n WHERE s.relname = c.relname\r\n AND s.schemaname IN (SELECT nspname FROM pg_namespace WHERE oid = c.relnamespace)\r\n AND ( s.schemaname = 'public' OR s.schemaname = 'pg_toast' );<\/pre>\nAnother one to view the size of your indexes :<\/p>\n
SELECT s.schemaname, s.relname, s.indexrelname, c.oid, c.relfilenode,\r\n c.relpages AS pages\r\n FROM pg_stat_all_indexes as s, pg_class AS c\r\n WHERE s.indexrelname = c.relname\r\n AND s.schemaname IN (SELECT nspname FROM pg_namespace WHERE oid = c.relnamespace)\r\n AND ( s.schemaname = 'public' OR s.schemaname = 'pg_toast' );<\/pre>\nThe size is reported in the `pages’ column, and in most case one page is 8KB (postgresql compiled with default settings)\u00a0.<\/p>\n
The `seq_scan’ and `idx_scan’ allows you to view own many sequential scans and index scans are performed on your table.\u00a0Too many sequential scans on a huge table might be a sign for a missing index that could help traverse the table more\u00a0efficiently.<\/p>\n
These statistics might help you optimize your requests\/indexes, and to fine tune your Postgresql shared_buffers parameter.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continuer la lecture