Table of contents
- Given problem
- SQL Queries for a database
- SQL Queries for table
- SQL Queries for indexes
- SQL Queries for slow query
- Wrapping up
Sometimes, in our project, we need to verify a database, a table, or even indexes to investigate a specific issue. But we didn’t know any useful queries to improve the speed of investigation.
So today, I will share some interesting queries that I use them a lot in daily basis. These queries will be updated frequently. Let’s get started.
SQL Queries for a database
Calculate the size of a database (by bytes).
SELECT pg_database_size(current_database())/1000000000 as database_size_per_gb;
Calculate the size of all databases.
SELECT SUM(pg_database_size(datname)/1000000000) as all_databases_size_per_gb FROM pg_database;
Get version of PostgreSQL.
-- 1st way SELECT version(); -- 2nd way SHOW server_version;
SQL Queries for table
List all tables in a database.
SELECT * FROM pg_database;
List column name and its data type in a table.
SELECT column_name, data_type FROM information_schema.COLUMNS WHERE table_name = 'table-name';
Calculate the size (per bytes) of a table.
SELECT pg_relation_size('table-name'); -- make the size more pretty SELECT pg_size_pretty(pg_relation_size('table-name'));
List the biggest tables.
SELECT table_name, pg_relation_size(table_schema || '.' || table_name)/1000000 as size_per_mb FROM information_schema.tables WHERE table_name NOT IN ('information_schema', 'pg_catalog') ORDER BY size_per_mb DESC LIMIT 10;
SQL Queries for indexes
List indexes of each table in a table.
SELECT tablename, indexname, indexref FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;
Verify unused indexes.
SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;
Check the size of an index.
SQL Queries for slow query
Verify the current state of slow query logging.
SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement';
Get queries that has execute time is greater than specific duration.
SELECT query, calls(total_time/calls)::integer as avg_time_ms FROM pg_stat_statements WHERE calls > 1000 ORDER BY avg_time_ms DESC LIMIT 10;
Notes: need to check whether we have installed