database-optimizer

Enable the pg_stat_statements extension and query it ordered by mean_exec_time or total_exec_time to find the slowest queries. Then run EXPLAIN (ANALYZE, BUFFERS) on each candidate to capture the actual execution plan and buffer usage.

Use CREATE INDEX CONCURRENTLY, which builds the index without blocking reads and writes on the table. It takes longer than a standard build but is safe for production systems with active traffic.

PostgreSQL tuning centers on shared_buffers, work_mem, autovacuum, and WAL settings, while MySQL tuning focuses on the InnoDB buffer pool, log file size, and flush behavior. Both rely on execution plan analysis but use different diagnostic tools and syntax.

Common causes include stale table statistics, functions wrapping the indexed column, low selectivity making a sequential scan cheaper, or implicit type conversion. Run ANALYZE on the table and check the EXPLAIN output for row estimate mismatches.

Use a covering index when a query repeatedly filters on some columns and returns a small set of others, allowing an index-only scan. In PostgreSQL add non-key columns with INCLUDE; in MySQL append them to the index definition.

Every index slows down INSERT, UPDATE, and DELETE operations and consumes storage. Redundant indexes like (a) plus (a,b) waste resources, so monitor pg_stat_user_indexes or performance_schema and drop indexes with zero scans.