database-debug

name	database-debug
description	DB debug — slow queries, index inspection, lock detection.

Database Debug Toolkit

PostgreSQL

Execution plan

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <your query>;

Sequential scans on large tables (signals missing indexes)

SELECT relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Unused indexes (candidates for removal)

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE 'pg_%';

Active locks and blocking queries

SELECT pid, state, wait_event_type, wait_event, left(query, 100) AS query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

Table and index sizes

SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;

Top slow queries (requires pg_stat_statements extension)

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

Missing foreign key indexes

SELECT conrelid::regclass AS table, a.attname AS column
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

Bloat check (dead tuples — signals need for VACUUM)

SELECT relname, n_dead_tup, n_live_tup,
  round(100 * n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC;

---

MySQL / MariaDB

Execution plan

EXPLAIN FORMAT=JSON <your query>;
EXPLAIN ANALYZE <your query>;  -- MySQL 8.0+

Index inspection

SHOW INDEX FROM <table>;
SHOW TABLE STATUS LIKE '<table>';

Top slow queries (via Performance Schema)

SELECT digest_text, count_star, avg_timer_wait / 1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Active connections and locks

SHOW PROCESSLIST;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

Table sizes

SELECT table_name,
  round(data_length / 1024 / 1024, 2) AS data_mb,
  round(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC;

---

SQLite

Execution plan

EXPLAIN QUERY PLAN <your query>;

Index list

PRAGMA index_list('<table>');
PRAGMA index_info('<index_name>');

Table info

PRAGMA table_info('<table>');
PRAGMA integrity_check;

---

MongoDB

Query analysis

db.collection.find({ field: value }).explain("executionStats")

Index inspection

db.collection.getIndexes()
db.collection.aggregate([{ $indexStats: {} }])

Collection stats

db.runCommand({ collStats: "collection" })
db.runCommand({ dbStats: 1 })

Slow operations (requires profiler enabled)

db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.find().sort({ ts: -1 }).limit(10)

---

Redis

redis-cli INFO memory          # memory usage and fragmentation
redis-cli INFO stats           # command stats, hit/miss rates
redis-cli INFO clients         # connected clients
redis-cli --bigkeys            # find largest keys by memory
redis-cli --hotkeys            # find most-accessed keys (requires maxmemory-policy allkeys-lfu)
redis-cli MONITOR              # live command stream — use briefly, high CPU overhead
redis-cli SLOWLOG GET 10       # last 10 slow commands
redis-cli LATENCY HISTORY event

Key pattern inspection

redis-cli --scan --pattern "prefix:*" | wc -l  # count keys matching pattern
redis-cli OBJECT ENCODING <key>                 # storage encoding
redis-cli OBJECT IDLETIME <key>                 # seconds since last access
redis-cli MEMORY USAGE <key>                    # bytes consumed by a key