SQL Query Optimization with EXPLAIN ANALYZE — Find and Fix Bottlenecks • Meteora Web Agency

Your query takes seconds instead of milliseconds. The database slows down the application. Clients wait. And you don't know where to start fixing it. It happens to everyone sooner or later. The difference is how you react: either throw hardware at the problem, or learn to read what the database is already telling you.

We, at Meteora Web, have optimized queries for e-commerce sites processing thousands of orders daily, for SaaS platforms, and for WordPress WooCommerce sites managing catalogs of tens of thousands of products. The first step is always the same: EXPLAIN ANALYZE. It's not magic, it's diagnostics. Let's show you right away.

Why SQL queries slow down and how to diagnose the problem

A relational database is a calculation engine. Every query is an execution plan that the database chooses among many alternatives. If the plan is wrong – or if the information to choose the right one is missing – the query becomes slow. Typical causes: full table scan on large tables, joins without indexes, sorts on huge datasets, unoptimized subqueries. Diagnosis starts with one command: EXPLAIN ANALYZE.

What is EXPLAIN ANALYZE and how to use it

EXPLAIN ANALYZE executes the query and shows the actual execution plan with real timings and counts, not estimates. Supported by PostgreSQL and MySQL 8.0.18+. On MySQL it's EXPLAIN ANALYZE (since 8.0.18), on MariaDB it's ANALYZE SELECT .... Prepend the command to your SELECT query.

Basic syntax and example

EXPLAIN ANALYZE
SELECT o.id, o.order_date, SUM(d.amount) AS total
FROM orders o
JOIN order_details d ON o.id = d.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY o.id, o.order_date
ORDER BY total DESC
LIMIT 10;

Run this in your SQL client. The output will look like:

Limit  (cost=... actual time=1234.567..1234.789 rows=10 loops=1)
  ->  Sort  (cost=... actual time=1234.567..1234.678 rows=10 loops=1)
        Sort Key: (sum(d.amount)) DESC
        ->  HashAggregate  (cost=... actual time=1234.000..1234.500 rows=5000 loops=1)
              ->  Hash Join  (cost=... actual time=800.000..1000.000 rows=50000 loops=1)
                    Hash Cond: (d.order_id = o.id)
                    ->  Seq Scan on order_details d  (cost=... actual time=0.000..400.000 rows=100000 loops=1)
                    ->  Hash  (cost=... actual time=0.050..0.050 rows=1000 loops=1)
                          ->  Seq Scan on orders o  (cost=... actual time=0.000..0.050 rows=1000 loops=1)
                              Filter: (order_date >= '2025-01-01'::date)

Each line is a plan node. Read from bottom to top: Seq Scan on order_details with 100,000 rows – actual time 400 ms. Then Hash Join, then HashAggregate, then Sort, then Limit. The bottleneck is obvious: the full table scan on order_details.

How to read the EXPLAIN ANALYZE output

Key fields are:

actual time: real execution time for the node (first value = start, second = end). In milliseconds.
rows: actual number of rows processed by the node.
loops: how many times the node was executed (usually 1, but can be >1 for correlated subqueries).
cost: estimated cost (arbitrary units) – useful for comparison, but actual timings are more reliable.
actual time per row: you can compute it (actual time / rows) to understand the unit cost.

A high time on a node with many rows indicates the need for an index. A high time on a node with few rows may indicate a configuration issue (e.g., too small a buffer pool).

What are the most common SQL query bottlenecks

After years of reading execution plans, we've seen three recurring patterns:

Full Table Scan (Seq Scan)

On tables with thousands of rows it's not a problem. On hundreds of thousands or millions, it becomes dramatic. The solution is an index on the column used in WHERE or JOIN. In the example above, an index on order_details.order_id would turn the Seq Scan into an Index Scan, reducing time from 400 ms to a few milliseconds.

Inefficient joins (Nested Loop vs Hash Join vs Merge Join)

A Nested Loop on two large tables without indexes can run millions of iterations. The optimal plan depends on cardinality. If you see a Nested Loop with high actual time and large rows, you probably need an index on the join condition or updated statistics.

Expensive sorts (ORDER BY / GROUP BY)

The Sort operation requires memory. If the dataset exceeds work_mem (PostgreSQL) or sort_buffer_size (MySQL), the database writes to disk (external sort) and times explode. Solutions: increase the parameter, add an index on the sort column, or limit data before the sort with WHERE and LIMIT.

How to fix bottlenecks with practical examples

Here are three concrete actions you can take right after reading the output.

Add targeted indexes

-- For the Seq Scan on order_details
CREATE INDEX idx_order_details_order_id ON order_details (order_id);

-- For the date filter on orders
CREATE INDEX idx_orders_date ON orders (order_date);

-- For GROUP BY with ORDER BY on total (composite index)
CREATE INDEX idx_orders_date_id ON orders (order_date, id);

After adding the index, rerun EXPLAIN ANALYZE. The plan should show Index Scan or Index Only Scan and times reduced by 80-95%.

Rewrite the query

Sometimes the index is not enough. Example: ORDER BY (SELECT ...) or correlated subqueries. Try replacing with JOINs or window functions. Another typical case: DISTINCT on non-indexed columns. If possible, restructure the application logic to reduce the set before the distinct.

Partitioning tables

If a table exceeds 10 million rows and the main filter is temporal, partitioning (by month/year) can drastically reduce scanned data. In PostgreSQL:

CREATE TABLE orders_part (
    id INT,
    order_date DATE,
    ...
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2025_01 PARTITION OF orders_part
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Then modify queries to use the partitioned table. The query planner will automatically skip irrelevant partitions.

EXPLAIN ANALYZE vs EXPLAIN: when to use each

EXPLAIN shows only cost estimates based on statistics. It's fast (does not execute the query) but can be inaccurate if statistics are stale. EXPLAIN ANALYZE runs the query and shows actual costs. Use EXPLAIN for preliminary analysis on test queries, and EXPLAIN ANALYZE for final diagnosis on queries you can afford to execute (caution: writes data if the query is DML). Never on production without control.

What to do now

Enable the slow query log on your database (e.g., log_min_duration_statement = 200 in PostgreSQL).
Pick one of the slowest queries and prepend EXPLAIN ANALYZE.
Identify the node with the highest time and largest rows (Seq Scan, Nested Loop, Sort).
Add the missing index or rewrite the query.
Rerun EXPLAIN ANALYZE and compare times.
Repeat until the time falls within an acceptable threshold (typically under 100 ms for critical queries).

We at Meteora Web do this every day for our clients. If you want to dive deeper, read our full guide on SQL and Relational Databases or contact us for a database performance audit. Remember: one extra second of latency on an e-commerce page can cost you 7% of conversions. Don't wait until it's too late.