ServerlessBase Blog

    • Understanding Slow Query Analysis

    Learn how to identify, diagnose, and optimize slow database queries for better application performance

    databasesperformanceoptimizationsqlpostgresql

    Understanding Slow Query Analysis

    You've deployed your application, users are happy, and everything seems to be working. Then you notice the dashboard is sluggish. The admin panel takes ten seconds to load. The search feature times out. You check the database logs and see queries running for hundreds of milliseconds or even seconds. This is where slow query analysis becomes your most valuable tool.

    Slow query analysis isn't just about finding slow queries. It's about understanding why they're slow, fixing them, and preventing them from happening again. Every millisecond you save in query execution translates to faster page loads, happier users, and lower infrastructure costs.

    What Makes a Query Slow

    Before you can fix slow queries, you need to understand what makes them slow. Database queries are slow for three main reasons: inefficient execution plans, missing indexes, and poor data volume.

    An execution plan is the database's strategy for retrieving your data. It decides whether to scan the entire table or use an index, whether to join tables in one order or another, and whether to fetch all rows or just the ones you need. If the execution plan is suboptimal, your query will be slow regardless of how well-written it is.

    Missing indexes are the most common cause of slow queries. An index is like a book's table of contents—it lets the database find specific rows without reading the entire book. Without an index, the database must scan every row in the table, which is slow for large tables.

    Poor data volume compounds these problems. A query that takes 10ms on a table with 1,000 rows might take 10 seconds on a table with 1,000,000 rows. This is why query performance degrades as your data grows.

    Reading Query Execution Plans

    Every database has a way to show you the execution plan. In PostgreSQL, you use the EXPLAIN command. In MySQL, it's EXPLAIN as well. The output tells you exactly what the database is doing.

    EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

    The output shows you the steps the database will take, the estimated cost of each step, and the actual time taken. Look for these red flags:

    • Seq Scan: Sequential scan means the database is reading every row in the table. This is slow for large tables. You want to see Index Scan instead.
    • Nested Loop: Nested loop joins can be slow if they're executed many times. Hash joins and merge joins are usually faster for large datasets.
    • Filter: A filter step means the database is applying a condition after retrieving rows. This often indicates a missing index.

    Here's an example of a good execution plan:

    Index Scan using users_email_idx on users  (cost=0.29..8.31 rows=1 width=100) (actual time=0.012..0.015 rows=1 loops=1)
  Index Cond: (email = 'test@example.com')

    This shows the database is using an index (users_email_idx) to find the single matching row. The actual time is only 0.015ms.

    Common Slow Query Patterns

    Some query patterns are slow by nature. Recognizing them helps you write better queries from the start.

    1. Functions on indexed columns

    -- SLOW: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
 
-- FAST: Index can be used
SELECT * FROM users WHERE email = 'test@example.com';

    When you apply a function to a column, the database can't use an index on that column. It must scan the entire table and apply the function to each row.

    2. OR conditions instead of UNION

    -- SLOW: May require multiple index scans
SELECT * FROM users WHERE email = 'test@example.com' OR username = 'testuser';
 
-- FAST: Clear intent, better performance
SELECT * FROM users WHERE email = 'test@example.com'
UNION
SELECT * FROM users WHERE username = 'testuser';

    OR conditions can be tricky for query optimizers. Sometimes they force multiple index scans, sometimes they force a full table scan. UNION is explicit and often faster.

    3. SELECT *

    -- SLOW: Retrieves all columns, including large ones
SELECT * FROM users WHERE id = 1;
 
-- FAST: Retrieves only needed columns
SELECT id, name, email FROM users WHERE id = 1;

    SELECT * retrieves every column in the table, including large text or binary columns. This increases network transfer time and memory usage. Only select the columns you need.

    4. LIKE with leading wildcard

    -- SLOW: Can't use index
SELECT * FROM users WHERE name LIKE '%john%';
 
-- FAST: Index can be used
SELECT * FROM users WHERE name LIKE 'john%';

    A leading wildcard (%john%) prevents index usage because the database doesn't know where to start searching. A trailing wildcard (john%) allows index usage.

    Setting Up Query Logging

    To catch slow queries, you need to log them. Most databases have built-in slow query logging.

    PostgreSQL

    -- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries taking > 1 second
 
-- Restart PostgreSQL to apply changes
SELECT pg_reload_conf();

    You can also log to a file:

    ALTER SYSTEM SET log_destination = 'stderr';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_directory = 'log';
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';

    MySQL

    -- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking > 1 second
 
-- Log to file
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

    MongoDB

    // Enable slow query logging
db.setProfilingLevel(1, { slowms: 1000 }); // Log queries taking > 1 second
 
// View slow queries
db.system.profile.find().sort({ ts: -1 }).limit(10);

    Analyzing Slow Query Logs

    Slow query logs can be overwhelming. Here's how to make sense of them.

    Look for patterns

    Are the same queries appearing repeatedly? These are your optimization targets. If SELECT * FROM users WHERE email = ? appears 10,000 times in your logs, fixing that one query will improve performance for thousands of users.

    Check the frequency

    A query that runs once a day taking 5 seconds is less urgent than a query that runs 100 times a second taking 10ms. Prioritize based on impact.

    Consider the data volume

    A query that scans 1 million rows is slower than one that scans 100 rows. But if the 1 million row query runs once a day and the 100 row query runs 10,000 times a second, the 1 million row query might be more important to optimize.

    Practical Optimization Techniques

    Once you've identified a slow query, here's how to fix it.

    1. Add missing indexes

    -- Create an index on the column used in WHERE clause
CREATE INDEX idx_users_email ON users(email);
 
-- Create a composite index for multi-column queries
CREATE INDEX idx_users_name_email ON users(name, email);

    Composite indexes are useful when you frequently query on multiple columns. The database can use the index for queries that match the leftmost columns.

    2. Rewrite queries for better performance

    -- Instead of:
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-11';
 
-- Use:
SELECT * FROM orders WHERE created_at >= '2026-03-11 00:00:00' AND created_at < '2026-03-12 00:00:00';

    The first query forces a full table scan because DATE() is a function. The second query can use an index on created_at.

    3. Use EXPLAIN to verify improvements

    After making changes, always check the execution plan:

    EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

    You should see Index Scan instead of Seq Scan, and the actual time should be lower.

    4. Consider query refactoring

    Sometimes the best optimization is to change how you query the data:

    -- Instead of querying users and orders separately
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at > NOW() - INTERVAL '7 days';
 
-- Consider denormalizing for read-heavy workloads
SELECT u.name, u.total_spent, o.total FROM user_stats u JOIN orders o ON u.id = o.user_id WHERE o.created_at > NOW() - INTERVAL '7 days';

    Denormalization trades write performance for read performance. It's worth it for frequently accessed data.

    Monitoring and Prevention

    Optimization isn't a one-time task. It's an ongoing process.

    Set up alerts

    Configure your monitoring system to alert you when slow query thresholds are exceeded. Most APM tools and database monitoring solutions have slow query alerts.

    Regular reviews

    Schedule regular reviews of your slow query logs. Even if no queries are slow, review the logs to ensure no new slow queries have appeared.

    Performance budgets

    Define performance budgets for your queries. For example, no query should take more than 100ms, and no page load should exceed 2 seconds. This keeps performance a priority.

    Use query analysis tools

    Tools like PostgreSQL's pg_stat_statements, MySQL's Performance Schema, and MongoDB's profiler provide detailed query statistics. They help you identify the most frequently executed and slowest queries.

    Conclusion

    Slow query analysis is a fundamental skill for any database professional. It requires understanding execution plans, recognizing common patterns, and knowing how to optimize queries effectively. The process is iterative: identify slow queries, analyze them, fix them, and verify the improvements.

    Remember that optimization is a balance. Sometimes the most performant query is the simplest one. Don't over-optimize prematurely. Focus on queries that actually impact your users and your infrastructure costs.

    Platforms like ServerlessBase make it easier to deploy and manage your applications, but they can't fix slow queries for you. You need to understand your database and write efficient queries. The time you invest in learning query optimization pays dividends in faster applications, happier users, and lower cloud bills.

    The next time you notice your application slowing down, don't just add more resources. Analyze the slow queries, fix them, and prevent them from happening again. That's the mark of a skilled database engineer.

    Leave comment