requête adminsys PostgreSQL.md

Gros indexs

Peut être un bug dans celle là...

select
   stats.schemaname,
   stats.relname as table,
   stats.indexrelname as index,
   idx.indisprimary as primary,
   idx.indisunique as unique,
   stats.idx_scan,
   stats.idx_tup_read,
   stats.idx_tup_fetch,
   pg_size_pretty(pg_table_size(stats.schemaname || '.' || stats.indexrelname::text)) as index_disk_size,
   pg_catalog.pg_get_indexdef(idx.indexrelid, 0, true) as def
FROM pg_catalog.pg_stat_user_indexes stats
JOIN pg_catalog.pg_index idx ON (stats.indexrelid = idx.indexrelid)
 LEFT JOIN  pg_catalog.pg_constraint con
     ON (
         conrelid = idx.indrelid AND
         conindid = idx.indexrelid AND
         contype IN ('p','u','x')
      )
order by
  pg_table_size(stats.schemaname || '.' || stats.indexrelname::text) desc,
  stats.schemaname,
  stats.relname,
  stats.idx_scan desc
 LIMIT 50
;

Grosses tables:

(
SELECT
    -- user tables
    pg_size_pretty(pg_total_relation_size(relid)) as "total size",
    pg_size_pretty(pg_table_size(relid)) as "table size",
    pg_size_pretty(pg_relation_size(relid)) as "index size",
    concat(schemaname, '.', relname) as name,
    concat(
        'seq_scan: ',seq_scan,
        E'\\nseq_tup_read: ', seq_tup_read,
        E'\\nidx_scan: ', idx_scan,
        E'\\nidx_tup_fetch: ', idx_tup_fetch
    ) as "reads",
    concat(
        'insert: ',n_tup_ins,
        E'\\nupdate: ', n_tup_upd,
        E'\\nhot_update: ', n_tup_hot_upd,
        E'\\ndelete: ', n_tup_del
    ) as "writes",
    concat(
        'live: ',n_live_tup,
        E'\\ndead: ', n_dead_tup,
        E'\\nmod_since_analyze: ', n_mod_since_analyze
    ) as "state",
    concat(
        'last_vacuum: ', last_vacuum,
        'auto: ', last_autovacuum,
        E'\\nlast_analyze: ', last_analyze,
        ' auto: ', last_autoanalyze,
        E'\\ncpt_vacuum: ', vacuum_count,
        ' auto: ', autovacuum_count,
        E'\\ncpt_analyze: ', analyze_count,
        'auto: ', autoanalyze_count
    ) as "vacuum",
    pg_total_relation_size(relid) as real_size
    FROM pg_stat_user_tables
) UNION (
    -- toast tables
    SELECT
        pg_size_pretty(pg_total_relation_size(relid)) as "total size",
        pg_size_pretty(pg_table_size(relid)) as "table size",
        pg_size_pretty(pg_indexes_size(relid)) as "index size",
        concat(stats.schemaname, '.', stats.relname, ' [', class2.relname ,']') as name,
    concat(
        'seq_scan: ',seq_scan,
        E'\\nseq_tup_read: ', seq_tup_read,
        E'\\nidx_scan: ', idx_scan,
        E'\\nidx_tup_fetch: ', idx_tup_fetch
    ) as "reads",
    concat(
        'insert: ',n_tup_ins,
        E'\\nupdate: ', n_tup_upd,
        E'\\nhot_update: ', n_tup_hot_upd,
        E'\\ndelete: ', n_tup_del
    ) as "writes",
    concat(
        'live: ',n_live_tup,
        E'\\ndead: ', n_dead_tup,
        E'\\nmod_since_analyze: ', n_mod_since_analyze
    ) as "state",
    concat(
        'last_vacuum: ', last_vacuum,
        'auto: ', last_autovacuum,
        E'\\nlast_analyze: ', last_analyze,
        ' auto: ', last_autoanalyze,
        E'\\ncpt_vacuum: ', vacuum_count,
        ' auto: ', autovacuum_count,
        E'\\ncpt_analyze: ', analyze_count,
        'auto: ', autoanalyze_count
    ) as "vacuum",
        pg_total_relation_size(relid) as real_size
      FROM pg_stat_sys_tables stats
         JOIN pg_class class1 ON class1.relname = stats.relname
         JOIN pg_class class2 ON class2.reltoastrelid = class1.oid
      WHERE schemaname = 'pg_toast'
)
ORDER BY real_size DESC
LIMIT 30; 

Taille de la base

select pg_size_pretty(pg_database_size('nom_de_ma_base_a_moi_que_j_ai'))