SQL-Abfragen für PostgreSQL

Mit dem SQL-Tool der Wahl lassen sich gemäß Moodle Datenbankschema Abfragen gestalten. 

 Konfiguration abfragen per SQL-Befehl 

 show all; 

 PGSQL-Version 

 SELECT version(); 

 

 

 Datenbank-Größen abfragen 

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

 SELECT 

 pg_size_pretty( pg_database_size( 'moodle-dbname' ) ) As 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,

   pg_size_pretty(index_bytes) AS INDEX,

   pg_size_pretty(toast_bytes) AS 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,

             c.reltuples AS row_estimate,

             pg_total_relation_size(c.oid) AS total_bytes,

             pg_indexes_size(c.oid) AS index_bytes,

             pg_total_relation_size(reltoastrelid) AS 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. Indexes 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 Übersicht 

 SELECT pg_size_pretty( pg_total_relation_size('tablename') ); 

 HeidiSQL (in GB inkl. Index): Tabellenübericht 

 Pgadmin (in MB, Tabelle / Index separat): links in Navigation unter "tables" die Tabelle auswählen, dann oben Tab "Statistics" 

 

 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 

 

 

 Autor: Klaus Steitz , Technische Universität Darmstadt