Why should you care?

PostgresVerse latency regression after launch — diagnostics find seq scans and buffer cache miss not CPU lack.

See it live — copy this example

Run in pgAdmin or psql.

SELECT relname,
       seq_scan, idx_scan,
       round(100.0 * idx_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS idx_scan_pct,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY seq_scan DESC
LIMIT 10;

What happened?

• High seq_scan on large table with low idx_scan_pct suggests missing or unused index.
• n_dead_tup signals vacuum need.
• Prioritize tables with seq_scan millions.

Practice next

1. Run diagnostics query on PostgresVerse.
2. Cross-check top seq_scan table with EXPLAIN of app query.
3. Check pg_statio_user_tables heap_blks_hit vs read.
4. Query pg_stat_user_indexes idx_scan=0 for unused indexes.
5. Use pg_buffercache extension for hot page inspection.

Remember

seq_scan vs idx_scan guides index work. Buffer hit ratio below 99% on OLTP warrants investigation. Dead tuples correlate with autovacuum lag.

PostgresVerse slow launch postmortem

Diagnostics show products seq_scan 2M/min; composite index added same day.

Outcome: P95 drops 900ms to 45ms; no hardware change needed.