Postgres: Table list ordered by row count

  select nspname as schema, relname as tablename, 

   to_char(reltuples, '999,999,990') as row_count

  from pg_class c JOIN pg_catalog.pg_namespace n

   ON n.oid = c.relnamespace where relkind='r'

    order by reltuples desc



SELECT psu.schemaname as "schema", psu.relname AS table_name

    , to_char(sum(pg_total_relation_size(psu.relid)), '999,999,999,990') AS include_ndx

    , to_char(sum(pg_table_size(psu.relid)), '999,999,999,990') as whole_table

    , to_char(sum(pg_relation_size(psu.relid)), '99,999,999,990') AS not_toasts

    , to_char(sum(pg_table_size(psu.relid) - pg_relation_size(psu.relid)), '999,999,999,990') AS toasts

    , to_char(sum(pg_indexes_size(psu.relid)), '99,999,999,990') AS indexes

    , to_char(cl.reltuples, '999,999,990') as row_count

FROM pg_catalog.pg_statio_user_tables psu

    , pg_catalog.pg_class cl

where psu.relid = cl.oid

group by psu.schemaname, psu.relname, cl.reltuples

order by 1

;

Comments

Post a Comment

Popular posts from this blog

Oracle: To clean up WRI$_ADV_OBJECTS

 set linesize 200; select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc; COL OCCUPANT_NAME FORMAT A30 SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; COL SEGMENT_NAME FORMAT A30 COL OWNER FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL SEGMENT_TYPE FORMAT A15 SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; 1.  automatic clean-up default 31 days           SELECT dbms_stats.get_stats_history_availability from Dual; 2. modify retention policy        exec  dbms_stats.alter_stats_history_retention (14); 3. a manual cleaning  exec dbms_stats.purge_stats (sysdate-14);   ALTER TABLE WRI$_ADV_OBJECTS MOVE; ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 R...
Read more