Useful PSQL queries

useful_psql_queries.sql

-- Table Sizes
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relname::regclass))                                       AS full_size,
       pg_size_pretty(pg_relation_size(relname::regclass))                                             AS table_size,
       pg_size_pretty(pg_total_relation_size(relname::regclass) - pg_relation_size(relname::regclass)) AS TOAST size,
       pg_size_pretty(pg_total_relation_size(relname::regclass) - pg_table_size(relname::regclass))    AS index_size
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(relname::regclass) DESC;

-- duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx))[1]                                AS idx1,
       (array_agg(idx))[2]                                AS idx2,
       (array_agg(idx))[3]                                AS idx3,
       (array_agg(idx))[4]                                AS idx4
FROM (
         SELECT indexrelid::regclass                                                   AS idx,
                (indrelid::text || E'\n' || indclass::text || E'\n' || indkey::text || E'\n' ||
                 coalesce(indexprs::text, '') || E'\n' || coalesce(indpred::text, '')) AS key
         FROM pg_index) sub
GROUP BY key
HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

-- indexes review (type, size, uniq, scans, ...)
SELECT t.schemaname,
       t.tablename,
       indexname,
       type,
       c.reltuples                                                       AS num_rows,
       pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' ||
                                       quote_ident(t.tablename)::text))  AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' ||
                                       quote_ident(indexrelname)::text)) AS index_size,
       CASE
           WHEN indisunique THEN 'Y'
           ELSE 'N'
           END                                                           AS UNIQUE,
       number_of_scans,
       tuples_read,
       tuples_fetched
FROM pg_tables t
         LEFT OUTER JOIN pg_class c ON t.tablename = c.relname
         LEFT OUTER JOIN (
    SELECT c.relname     AS ctablename,
           ipg.relname   AS indexname,
           x.indnatts    AS number_of_columns,
           idx_scan      AS number_of_scans,
           idx_tup_read  AS tuples_read,
           idx_tup_fetch AS tuples_fetched,
           indexrelname,
           indisunique,
           schemaname,
           am.amname     AS type
    FROM pg_index x
             JOIN pg_class c ON c.oid = x.indrelid
             JOIN pg_class ipg ON ipg.oid = x.indexrelid
             JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid
             JOIN pg_class cls ON cls.oid = x.indexrelid
             JOIN pg_am am ON am.oid = cls.relam
) AS foo ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
-- and number_of_scans = 0
ORDER BY pg_relation_size(quote_ident(t.schemaname)::text || '.' ||
                          quote_ident(indexrelname)::text) DESC;
                          
                          
-- indexes missing
SELECT relname,
       seq_scan - idx_scan                 AS too_much_seq,
       CASE WHEN seq_scan - idx_scan > 0 THEN 'Missing Index?' ELSE 'OK' END,
       pg_relation_size(relname::regclass) AS rel_size,
       seq_scan,
       idx_scan
FROM pg_stat_all_tables
WHERE schemaname = 'public'
  AND pg_relation_size(relname::regclass) > 80000
ORDER BY too_much_seq DESC;                          

-- get all foreign keys poiting to given table
SELECT (SELECT r.relname FROM pg_class r WHERE r.oid = c.confrelid)         AS base_table,
       a.attname                                                            AS base_col,
       (SELECT r.relname FROM pg_class r WHERE r.oid = c.conrelid)          AS referencing_table,
       UNNEST((SELECT array_agg(attname)
               FROM pg_attribute
               WHERE attrelid = c.conrelid AND ARRAY [attnum] <@ c.conkey)) AS referencing_col,
       pg_get_constraintdef(c.oid)                                             contraint_sql
FROM pg_constraint c
         JOIN pg_attribute a ON c.confrelid = a.attrelid AND a.attnum = ANY (confkey)
WHERE c.confrelid = (SELECT oid FROM pg_class WHERE relname = '<table_name>')
  AND c.confrelid != c.conrelid;
  
  
  -- invalid indexes
SELECT n.nspname, c.relname
FROM pg_catalog.pg_class c,
     pg_catalog.pg_namespace n,
     pg_catalog.pg_index i
WHERE (i.indisvalid = FALSE OR i.indisready = FALSE)
  AND i.indexrelid = c.oid
  AND c.relnamespace = n.oid
  AND n.nspname != 'pg_catalog'
  AND n.nspname != 'information_schema'
  AND n.nspname != 'pg_toast';
  
  
 -- foreign keys to my table
SELECT tc.table_schema,
       tc.constraint_name,
       tc.table_name,
       kcu.column_name,
       ccu.table_schema AS foreign_table_schema,
       ccu.table_name   AS foreign_table_name,
       ccu.column_name  AS foreign_column_name
FROM information_schema.table_constraints AS tc
         JOIN information_schema.key_column_usage AS kcu
              ON tc.constraint_name = kcu.constraint_name
                  AND tc.table_schema = kcu.table_schema
         JOIN information_schema.constraint_column_usage AS ccu
              ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_schema = 'public'
  AND ccu.table_name = 'yout_table';
  
  
-- generate reindex index by table commands
SELECT 'REINDEX INDEX CONCURRENTLY ' || indexname || ' /*' ||
       PG_SIZE_PRETTY(PG_RELATION_SIZE(QUOTE_IDENT(t.schemaname)::text || '.' ||
                                       QUOTE_IDENT(indexrelname)::text)) || '*/;'
FROM pg_tables t
         LEFT OUTER JOIN pg_class c ON t.tablename = c.relname
         LEFT OUTER JOIN (SELECT c.relname     AS ctablename,
                                 ipg.relname   AS indexname,
                                 x.indnatts    AS number_of_columns,
                                 idx_scan      AS number_of_scans,
                                 idx_tup_read  AS tuples_read,
                                 idx_tup_fetch AS tuples_fetched,
                                 indexrelname,
                                 indisunique,
                                 schemaname
                          FROM pg_index x
                                   JOIN pg_class c ON c.oid = x.indrelid
                                   JOIN pg_class ipg ON ipg.oid = x.indexrelid
                                   JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid) AS foo
                         ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
  AND tablename IN ('yourtable')
ORDER BY PG_RELATION_SIZE(QUOTE_IDENT(t.schemaname) :: TEXT || '.' ||
                          QUOTE_IDENT(indexrelname) :: TEXT) ASC;
                          
SELECT 'REINDEX INDEX CONCURRENTLY ' || indexname || ' /*' ||
       PG_SIZE_PRETTY(PG_RELATION_SIZE(QUOTE_IDENT(t.schemaname)::text || '.' ||
                                       QUOTE_IDENT(indexrelname)::text)) || '*/;'
FROM pg_tables t
         LEFT OUTER JOIN pg_class c ON t.tablename = c.relname
         LEFT OUTER JOIN (SELECT c.relname     AS ctablename,
                                 ipg.relname   AS indexname,
                                 x.indnatts    AS number_of_columns,
                                 idx_scan      AS number_of_scans,
                                 idx_tup_read  AS tuples_read,
                                 idx_tup_fetch AS tuples_fetched,
                                 indexrelname,
                                 indisunique,
                                 schemaname
                          FROM pg_index x
                                   JOIN pg_class c ON c.oid = x.indrelid
                                   JOIN pg_class ipg ON ipg.oid = x.indexrelid
                                   JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid) AS foo
                         ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
  AND tablename IN ('product_country')
ORDER BY PG_RELATION_SIZE(QUOTE_IDENT(t.schemaname) :: TEXT || '.' ||
                          QUOTE_IDENT(indexrelname) :: TEXT) ASC;                          
                          
                          
-- basic histogram example
SELECT DATE_TRUNC('month', created_at), COUNT(*)
FROM yourtable
GROUP BY 1;


-- bloat in tables and index: https://github.com/ioguix/pgsql-bloat-estimation