This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL.
Script 1 : Find a total size of the database.
|
1 |
SELECT pg_size_pretty(pg_database_size('db_employee')); |
Script 2: Find a total size of the table with an index.
|
1 |
SELECT pg_size_pretty(pg_total_relation_size('Employee_Table')); |
Script 3: Find a total size of the table without an index.
|
1 |
SELECT pg_size_pretty(pg_relation_size('Employee_table')); |
Script 4: Find a total size of the index.
|
1 |
SELECT pg_size_pretty(pg_indexes_size('index_empid')); |
Script 5: Find a total number of rows in a table.
|
1 |
SELECT COUNT(1) FROM Employee_table; |
Script 6 : Find all the table size in the current database.
|
1 2 3 4 5 6 |
SELECT table_schema || '.' || table_name AS TableName, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC |
Script 7 : Find all the table and index size in the current database.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT TableName ,pg_size_pretty(pg_table_size(TableName)) AS TableSize ,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize ,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName FROM information_schema.tables ) AS Tables ORDER BY 4 DESC |
