Index Optimization Queries

sql Copy Code

-- PostgreSQL Index Optimization Toolkit

-- 1. Find missing indexes (tables with sequential scans)
SELECT
    schemaname, relname AS table_name,
    seq_scan, seq_tup_read,
    idx_scan, idx_tup_fetch,
    n_tup_ins + n_tup_upd + n_tup_del AS writes,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND idx_scan < seq_scan / 2
  AND pg_relation_size(relid) > 10 * 1024 * 1024
ORDER BY seq_tup_read DESC
LIMIT 20;

-- 2. Find unused indexes (candidates for removal)
SELECT
    s.schemaname, s.relname AS table_name,
    s.indexrelname AS index_name,
    s.idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    i.indisunique AS is_unique
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan < 50
  AND NOT i.indisunique
  AND NOT i.indisprimary
  AND pg_relation_size(i.indexrelid) > 1024 * 1024
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- 3. Find duplicate indexes
SELECT
    a.indrelid::regclass AS table_name,
    a.indexrelid::regclass AS index_a,
    b.indexrelid::regclass AS index_b,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS size_a
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid < b.indexrelid
    AND a.indkey::text = b.indkey::text
WHERE a.indrelid::regclass::text NOT LIKE 'pg_%';

-- 4. Index bloat estimation
SELECT
    current_database(), nspname AS schema, tblname AS table,
    idxname AS index, bs*(relpages)::bigint AS real_size,
    bs*(relpages-est_pages)::bigint AS bloat_size,
    ROUND(100 * (relpages-est_pages)::float / relpages, 1) AS bloat_pct
FROM (
    SELECT *, (CASE WHEN relpages > est_pages THEN relpages ELSE est_pages END) AS safe_pages
    FROM (SELECT coalesce(1 + ceil(reltuples / floor((bs-pageopqdata-pagehdr) /
        (4+nulldatahdrwidth)::float)), 0) AS est_pages, bs, nspname, tblname,
        idxname, relpages, reltuples
    FROM (SELECT 8192 AS bs, 24 AS pageopqdata, 8 AS pagehdr,
        CASE WHEN max(coalesce(s.null_frac, 0)) > 0 THEN 2 ELSE 0 END +
        max(coalesce(s.avg_width, 0)) AS nulldatahdrwidth,
        n.nspname, c.relname AS tblname, i.relname AS idxname,
        i.relpages, i.reltuples
    FROM pg_index x
    JOIN pg_class c ON c.oid = x.indrelid
    JOIN pg_class i ON i.oid = x.indexrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_stats s ON s.tablename = c.relname AND s.attname = ANY(
        ARRAY(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = x.indrelid
              AND a.attnum = ANY(x.indkey)))
    WHERE n.nspname = 'public'
    GROUP BY n.nspname, c.relname, i.relname, i.relpages, i.reltuples
    ) AS est ) AS est2
) AS est3
WHERE relpages > 10 AND (relpages - est_pages) > 0
ORDER BY bloat_size DESC LIMIT 20;

indexing optimization performance dba

javascript

MongoDB Connection with Retry

bash

MongoDB Backup Script

sql

User Analytics Query

sql

Efficient Pagination Query