postgres-tips-megalist.md

Postgres Tips & Tricks - Megalist of Secret SQL Queries

• List all table indexes
• List all column types across the schema
• List history of sequential & index scans across all tables
• Show table size

List all table indexes

select 
    c.relnamespace::regnamespace as schema_name,
    c.relname as table_name,
    i.indexrelid::regclass as index_name,
    i.indisprimary as is_pk,
    i.indisunique as is_unique
from pg_index i
join pg_class c on c.oid = i.indrelid
where c.relname = 'TABLE_NAME'

List all column types across the schema

SELECT n.nspname AS schema_name,
       t.typname AS type_name,
       t.typtype AS type_type,
       t.typcategory AS type_category
FROM pg_type t
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND t.typtype IN ('e', 'c'); -- 'e' for ENUM, 'c' for composite types

List history of sequential & index scans across all tables

SELECT
    relname AS table_name,
    seq_scan,
    last_seq_scan,
    idx_scan,
    last_idx_scan,
    seq_scan + idx_scan AS total_accesses
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY total_accesses DESC;

Show table size

SELECT pg_size_pretty(pg_relation_size('table'));