engineering-database-optimizer

Enable the pg_stat_statements extension and query it ordered by total_exec_time to find the slowest queries. Combine this with EXPLAIN (ANALYZE, BUFFERS) on individual statements to see actual row counts, buffer hits, and scan types.

Enable slow_query_log with long_query_time set to your threshold, then analyze the log with pt-query-digest. Use Performance Schema's events_statements_summary_by_digest table for live query latency statistics without parsing log files.

Use a partial index when queries consistently filter on a small subset of rows, such as active users or pending orders. Partial indexes are smaller, faster to scan, and reduce write amplification compared to indexing the entire table.

No. Every index slows down INSERT and UPDATE operations and consumes storage. Create indexes only after confirming the access path via EXPLAIN ANALYZE, and remove unused indexes identified through pg_stat_user_indexes or Performance Schema.

Sequential scans occur when the planner estimates the index is not cheaper, often due to stale statistics, low selectivity, or a function wrapping the indexed column. Run ANALYZE to refresh statistics and rewrite expressions like DATE(column) into range comparisons.

Configuration tuning cannot fix fundamentally bad queries or missing indexes; it only optimizes resource usage. Changes like shared_buffers or innodb_buffer_pool_size require restarts or reloads, and aggressive settings like synchronous_commit off trade durability for speed.