SQL-Abfragen für PostgreSQL

Datenbank-Größen abfragen

Gesamtgröße DB inkl. Index in byteByte und GB:

SELECT pg_size_pretty( pg_database_size( 'moodle-dbname' ) ) As human_size

    ,human_size, pg_database_size( 'moodle-dbname' ) As byte_size;

  

Tabellengrößen aller Tabellen aufgeschlüsselt nach verschiedenen Werten wie Gesamtgröße und Index:

SELECT *, pg_size_pretty(total_bytes) AS total

    ,total,
    pg_size_pretty(index_bytes) AS INDEX

    ,INDEX,
    pg_size_pretty(toast_bytes) AS toast

    ,toast,
    pg_size_pretty(table_bytes) AS TABLE

 
  FROM (

 
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (

 
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME

              ,TABLE_NAME,
              c.reltuples AS row_estimate

              ,row_estimate,
              pg_total_relation_size(c.oid) AS total_bytes

              ,total_bytes,
              pg_indexes_size(c.oid) AS index_bytes

              ,index_bytes,
              pg_total_relation_size(reltoastrelid) AS toast_bytes

 toast_bytes,
          FROM pg_class c

 
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

 
          WHERE relkind = 'r'

 
  ) a
) a;

Die 20 größten Tabellen anzeigen, inkl. indexesIndexes und toasted data:

SELECT nspname || '.' || relname AS "relation",

 
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"


 FROM pg_class C

 
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

 
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')

 
    AND C.relkind <> 'i'

 
    AND nspname !~ '^pg_toast'

 
  ORDER BY pg_total_relation_size(C.oid) DESC

 
  LIMIT 20;

Tabellengrößen

SELECT pg_size_pretty( pg_total_relation_size('tablename') );

-

• HeidiSQL (in GB inkl. Index): Tabellenübericht
  -
pgadmin
• Pgadmin (in MB, Tabelle/Index separat): links in Navigation unter "tables" die Tabelle auswählen, dann oben Tab "Statistics"
  -
• SQL (in MB inkl. Index): SELECT pg_size_pretty( pg_total_relation_size('mdl_logstore_lanalytics_log') );

Weiterführende Links

https://docs.moodle.org/en/Custom_SQL_queries_report

https://docs.moodle.org/en/ad-hoc_contributed_reports

https://moodle.org/plugins/block_configurable_reports

https://docs.moodle.org/en/Report_builder