Table of contents

Given problem

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

  1. Calculate the size of a database (by bytes).

     SELECT pg_database_size(current_database())/1000000000 as database_size_per_gb;
  2. Calculate the size of all databases.

     SELECT SUM(pg_database_size(datname)/1000000000) as all_databases_size_per_gb
     FROM pg_database;
  3. Get version of PostgreSQL.

     -- 1st way
     SELECT version();
     -- 2nd way
     SHOW server_version;

SQL Queries for table

  1. List all tables in a database.

     SELECT * FROM pg_database;
  2. List column name and its data type in a table.

     SELECT column_name, data_type FROM information_schema.COLUMNS
     WHERE table_name = 'table-name';
  3. 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'));
  4. 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

  1. List indexes of each table in a table.

     SELECT tablename, indexname, indexref
     FROM pg_indexes
     WHERE schemaname = 'public'
  2. Verify unused indexes.

     SELECT schemaname, relname, indexrelname, idx_scan
     FROM pg_stat_user_indexes
     ORDER BY idx_scan;
  3. Check the size of an index.

     SELECT pg_size_pretty(pg_relation_size('index-name'));

SQL Queries for slow query

  1. Verify the current state of slow query logging.

     SELECT * FROM pg_settings
     WHERE name = 'log_min_duration_statement';
  2. 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 pg_stat_statements extension yet.

Wrapping up