Slow Queries: How to Detect and Optimize in MySQL and PostgreSQL

Introduction

Slow queries degrade application responsiveness, increase resource consumption, and may cause downtime. Identifying and optimizing slow queries is essential for maintaining an efficient database. This article provides an in-depth guide to detecting and optimizing slow queries in MySQL andPostgreSQL, emphasizing their similarities and differences.

Understanding Slow Queries

A slow query is any database query that takes longer than expected to execute. The threshold for "slow" varies based on system requirements but is commonly defined by MySQL's long_query_time setting or PostgreSQL's log_min_duration_statement. Slow queries can arise from several factors, including inefficient indexing, suboptimal query structures, or high contention in database locks.

Causes of Slow Queries

• Lack of Indexing: Queries without proper indexes may result in full table scans.
• Poor Query Design: Unoptimized SQL statements increase execution time.
• Lock Contention: Queries waiting on locks cause delays.
• High Volume of Data: Larger datasets naturally require more time to process.
• Inefficient Joins: Complex joins without appropriate indexing slow performance.
• Poor Caching Strategy: Absence of caching forces queries to run frequently.
• I/O Bottlenecks: Queries involving extensive disk reads slow down database performance.
• Suboptimal Query Execution Plans: The optimizer might choose inefficient execution paths.
• Inadequate Statistics: Outdated or missing table statistics lead to poor optimizer decisions.
• Resource Contention: Competition for CPU, memory, or I/O resources from other processes.

Detecting Slow Queries

Both MySQL and PostgreSQL provide tools to identify slow queries.

1. MySQL Slow Query Log vs. PostgreSQL Log

MySQL

-- Logs queries taking more than 1 second
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

‍-- Optional: log to a table instead of a file
‍SET GLOBAL log_output = 'TABLE';

PostgreSQL

-- Logs queries taking more than 1 second (in ms)
ALTER SYSTEM SET log_min_duration_statement = 1000;
-- Optional: log all statements
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();

PostgreSQL logs slow queries in its general log file, typically in the data directory's pg_log subdirectory.

2. Using EXPLAIN and EXPLAIN ANALYZE

MySQL

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1001;

Provides:

• Access methods for each table
• Join types
• Index usage information
• Estimated row counts

PostgreSQL

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1001;

Provides:

• Execution plan with node types
• Cost estimates (startup and total)
• Actual execution time when using ANALYZE
• Buffer usage statistics with BUFFERS
• Worker information for parallel queries

PostgreSQL’s EXPLAIN ANALYZE is generally more detailed, offering better query diagnostics.

3. Performance Schema vs. pg_stat_statements

MySQL

SET GLOBAL performance_schema = ON;
SELECT digest_text AS query, count_star
AS exec_count, round(avg_timer_wait/1000000000, 2)
AS avg_exec_time_ms, round(sum_timer_wait/1000000000, 2)
AS total_exec_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESCLIMIT 10;

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_time, total_time/calls
AS avg_time, rows/calls
AS avg_rows
FROM pg_stat_statements
ORDER BY total_time DESCLIMIT 10;

pg_stat_statements in PostgreSQL provides a similar function to MySQL's Performance Schema, helping to analyze the most expensive queries.

4. MySQL Enterprise Monitor and Third-Party Tools

• MySQL Enterprise Monitor: Provides graphical insights into query performance.
• Percona Toolkit (pt-query-digest): Analyzes slow query logs for patterns.
• Rapydo: Offers real-time MySQL performance monitoring and query optimization insights, along with automations that proactively detect and resolve slow query issues.
• New Relic & Datadog: Cloud-based monitoring solutions for MySQL performance.

Optimizing Slow Queries

1. Index Optimization

MySQL

CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE FULLTEXT INDEX idx_product_name ON products(product_name);

PostgreSQL

CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_product_search ON products USING GIN (to_tsvector('english', product_name));

PostgreSQL supports more advanced index types, including GIN (efficient for text search) and BRIN (for large sequential data).

2. Query Refactoring

Avoid functions on indexed columns:

-- Bad
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
‍-- Good
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Functions on indexed columns force full table scans.

3. Using JOIN Efficiently

CREATE INDEX idx_customer_id ON orders(customer_id);SELECT * FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

4. Partitioning Large Tables

PostgreSQL

CREATE TABLE orders (order_id SERIAL, customer_id INT, order_date DATE, PRIMARY KEY (order_id, order_date)) PARTITION BY RANGE (order_date);

PostgreSQL supports native partition pruning, making queries on partitioned tables faster than in MySQL.

5. Database Configuration Optimization

MySQL

SET GLOBAL innodb_buffer_pool_size = 2G;
SET GLOBAL join_buffer_size = 2M;

PostgreSQL

ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET work_mem = '16MB';SELECT pg_reload_conf();

PostgreSQL allows more fine-grained tuning of query memory settings.

6. Caching Query Results

PostgreSQL

CREATE MATERIALIZED VIEW fast_orders ASSELECT *
FROM orders WHERE order_date > now() - interval '30 days';
REFRESH MATERIALIZED VIEW fast_orders;

PostgreSQL’s materialized views offer built-in query caching, whereas MySQL relies on external solutions like Redis.

Conclusion

Both MySQL and PostgreSQL provide robust tools for detecting and optimizing slow queries, but they have different strengths:

• MySQL is easier to configure, widely used in web applications, and offers strong replication.
• PostgreSQL excels in advanced indexing, partitioning, query planning, and parallel execution.
  ‍

Choosing between MySQL and PostgreSQL depends on your workload:

• Use PostgreSQL if you need advanced query optimization, parallel execution, and sophisticated indexing.
• Use MySQL if simplicity, ease of use, and a well-supported ecosystem are your priorities.

Regardless of the database, implementing the best practices outlined in this article will ensure optimal performance for your workloads.

‍