SQL Query Optimization in 2026: 10 Mistakes That Make Queries Slow and How to Fix Each One

Your claims dashboard loaded in two seconds last quarter. Now it spins for forty, the data has not changed shape, and nobody deployed anything. That is almost never a server out of headroom; it is a query that quietly stopped using an index. One situation runs through this guide: a two-person analytics team at a mid-size insurer in Pune, Postgres behind a Power BI report, no DBA and no budget for a bigger instance. Tuning SQL at this level is a core module in 360DT's live Data Analyst course.

How SQL Query Optimization Actually Works

You never tell a relational database how to run a query. A cost-based planner picks the index, the join order and whether to sort in memory or on disk. Optimization is therefore not about clever SQL; it is about changing the facts that planner reasons over.

-- PostgreSQL: the only diagnostic command you really need
EXPLAIN (ANALYZE, BUFFERS)
SELECT claim_id, amount
FROM   claims
WHERE  status = 'OPEN'
  AND  created_at >= DATE '2026-09-01'
  AND  created_at <  DATE '2026-09-02';

The official PostgreSQL 18 documentation states that BUFFERS is now on by default for EXPLAIN ANALYZE; PostgreSQL 18.6, released 13 August 2026, is current in that series. On 17 and older you still type it yourself. The MySQL 8.4 LTS manual notes that EXPLAIN ANALYZE always returns TREE format regardless of explain_format.

The most valuable habit here: compare the planner's rows= estimate against actual rows=. If it guessed 400 and reality was 90,000, you have not found a slow query. You have found a lying statistic. Warehouse engines behave the same way, which is why plan reading sits in the tuning module of the live Microsoft Fabric data engineering program.

Mistakes 1 to 3: Filters That Quietly Disable Your Index

A filter is SARGable when the database can use it to seek into an index. Wrap the column in anything and the index becomes dead weight: the engine must compute your expression for every row before it can decide whether the row matches. Mistake one is worth more than the other nine combined, and the Pune dashboard broke exactly this way.

Mistake two, the implicit cast, is nastier because nothing warns you. Results stay correct; the query is simply far slower until someone runs EXPLAIN and sees a Seq Scan. An application integer ID meeting a legacy varchar key is the usual source.

Mistake three is the leading wildcard: past a few hundred thousand rows, LIKE '%term%' is a full scan with a friendly name.

-- Substring search that can actually use an index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX claims_desc_trgm_idx
  ON claims USING gin (description gin_trgm_ops);

-- Expression index, for a query you cannot edit
CREATE INDEX claims_created_day_idx
  ON claims (( created_at::date ));

Mistakes 4 to 6: Index Mistakes and SQL Index Best Practices

Mistake four is having no composite index where the query filters two columns. Two single-column indexes are not equivalent to one two-column index; a bitmap AND merges two structures before touching the table.

Mistake five is column order inside that index, and the rule people forget is equality columns first, range column last. An index on (created_at, status) serving a query filtering status = 'OPEN' plus a date range can only seek on the date. Flip it and both predicates come from the seek.

-- Wrong order: the range column leads, so status is only a filter
CREATE INDEX claims_bad_idx ON claims (created_at, status);

-- Right order: equality first, range last, payload included
CREATE INDEX CONCURRENTLY claims_status_created_idx
  ON claims (status, created_at)
  INCLUDE (amount);

The INCLUDE clause is mistake six in reverse. Without it the database finds rows in the index, then visits the heap for amount one random read at a time. Carry amount in the leaf and you get an Index Only Scan.

The counterweight: indexes are not free. Each is another B-tree updated on every write touching its columns, plus storage, cache pressure and autovacuum work. A table with fourteen indexes because fourteen people each added one is a common production problem. Before adding one, check whether an existing index can be widened. Hosted-database work like this sits in 360DT's Azure Solutions Architect and DevOps course.

Mistakes 7 and 8: Joins, Subqueries and the N+1 Trap

Mistake seven does not live in your SQL. It lives in the loop above it. An ORM fetches 500 claims, then lazily loads the policy for each, so the database sees 501 tiny fast queries instead of one join. Nothing looks slow in isolation and the page takes four seconds. Count queries per request, then ask for the data up front with a JOIN or eager loading.

-- Slow: the subquery runs once per row of claims
SELECT c.claim_id, c.amount,
       (SELECT SUM(amount) FROM claims x
         WHERE x.policy_id = c.policy_id) AS policy_total
FROM   claims c
WHERE  c.status = 'OPEN';

-- Fast: one pass, computed by a window function
SELECT c.claim_id, c.amount,
       SUM(c.amount) OVER (PARTITION BY c.policy_id) AS policy_total
FROM   claims c
WHERE  c.status = 'OPEN';

That is mistake eight: the correlated subquery in the SELECT list. Planners sometimes rewrite it into something sane and sometimes do not, particularly once it carries its own join or a LIMIT. Window functions are the reliable tool. Also read: 20 SQL interview questions with model answers.

Mistakes 9 and 10: Stale Statistics and the Memory Limits Behind SQL Performance Tuning

Mistake nine is trusting statistics after a bulk load. The planner picks between a nested loop and a hash join by estimating rows from a sample, so load eight million rows and query immediately and it may still think the table is empty.

-- After any bulk load, before you benchmark anything
ANALYZE claims;

-- For a skewed column, sample harder than the default
ALTER TABLE claims ALTER COLUMN status SET STATISTICS 500;
ANALYZE claims;

That number matters. The PostgreSQL 18 documentation gives default_statistics_target a default of 100, so the histogram comes from a small sample. Where 97% of rows share one value and the other 3% are what everybody queries, 100 buckets is not enough resolution.

Mistake ten is sorting more than the database may sort in memory. Exceed work_mem and PostgreSQL spills to temporary files, saying so plainly: Sort Method: external merge Disk: 51280kB. The word Disk in a sort node means I/O you did not need.

Raising work_mem is tempting and slightly dangerous, because the limit applies per sort node, not per query: four hash joins across twenty connections multiplies it by eighty. Set it per session.

First, though, find out which query to fix. Turn on pg_stat_statements, which the same documentation notes must be added to shared_preload_libraries and needs a restart.

-- Find the queries that actually cost you the most
SELECT substr(query, 1, 60) AS q,
       calls,
       round(mean_exec_time::numeric, 1)          AS avg_ms,
       round(total_exec_time::numeric / 1000, 1)  AS total_s
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

Sorting by total_exec_time is deliberate. A 40 millisecond query called 90,000 times an hour costs more than a nine second daily report, and the nine second report is the one everyone complains about.

How to Optimize a SQL Query: A Worked Example You Can Run Today

Twenty minutes of running plans teaches more than any article. You need Postgres with a couple of million rows, and need not pay for it: Neon's published plan documentation gives its free plan 0.5 GB of storage and 100 compute-hours per month per project, scaling to zero after five minutes idle.

CREATE TABLE claims (
  claim_id   bigserial PRIMARY KEY,
  policy_id  int           NOT NULL,
  status     text          NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at timestamptz   NOT NULL
);

INSERT INTO claims (policy_id, status, amount, created_at)
SELECT (random() * 50000)::int,
       CASE WHEN random() < 0.03 THEN 'OPEN' ELSE 'CLOSED' END,
       (random() * 90000)::numeric(12,2),
       timestamptz '2026-01-01' + (random() * 250) * interval '1 day'
FROM   generate_series(1, 2000000);

ANALYZE claims;

Run it the way most people write it, with the date wrapped in a function. Then rewrite the filter as a half-open range, add the composite index from earlier and run the same EXPLAIN. Watch Rows Removed by Filter collapse.

EXPLAIN (ANALYZE, BUFFERS)
SELECT claim_id, amount
FROM   claims
WHERE  status = 'OPEN'
  AND  DATE(created_at) = DATE '2026-09-01';

Seq Scan on claims
  Filter: ((status = 'OPEN')
    AND (date(created_at)
      = '2026-09-01'::date))
  Rows Removed by Filter: ~2m

Index Only Scan using
  claims_status_created_idx
  on claims
  Index Cond: ((status = 'OPEN')
    AND (created_at >= ...)
    AND (created_at <  ...))

One rewrite and one index, nothing added to the server. That is what most real optimization work looks like, and why I would not pay for a larger instance until someone has read the top five plans by total time.

When a Slow SQL Query Is Not an Optimization Problem

Here is what most tuning guides leave out, and why the Pune team eventually stopped tuning. Their morning refresh aggregated four years of claims into eighteen measures, and no index makes a full-table aggregation fast, because the query genuinely needs every row.

When a query must touch most of the table, stop optimizing and start precomputing. A nightly rollup or a materialized view does the work once instead of forty times a day. That boundary is where analytics work becomes data engineering work. Also read: the data analyst to data engineer switch plan.

-- Precompute once, query many times
CREATE MATERIALIZED VIEW claims_daily AS
SELECT created_at::date AS day, status,
       count(*)    AS claims,
       sum(amount) AS total
FROM   claims
GROUP  BY 1, 2;

CREATE UNIQUE INDEX ON claims_daily (day, status);
REFRESH MATERIALIZED VIEW CONCURRENTLY claims_daily;

Two honest caveats. Tuning has a hard ceiling: if your instance is CPU-bound at 95% all day, no rewrite saves you, and the capacity work in 360DT's AWS Solutions Architect and DevOps course matters more. And much of the advice online is padding: avoiding SELECT * is real but marginal, and will never turn four seconds into forty milliseconds.

The same thinking applies to vector search: with pgvector, an unindexed similarity query scans every vector, and an HNSW index changes the plan exactly as a B-tree does for a timestamp. That work sits inside the AI Engineer course on generative AI, RAG and agents.

Your SQL Query Optimization Checklist

Work top to bottom and stop at the first row that explains the behaviour.

To make this a skill rather than a one-off fix, the order that works is: read plans, then window functions, then the precompute boundary. Live sessions beat video here, because the useful part is someone reading your plan output and telling you which line matters. 360DT runs free webinars, you can sit in on a demo class first, and the certifications overview shows how SQL feeds into PL-300 and DP-700.

In the Pune team's position on Monday I would not touch the dashboard. I would enable pg_stat_statements, wait a day, sort by total time and read the top five plans. That single hour usually finds one non-SARGable filter and one missing composite index, and those two fixes end the argument about a bigger instance for another year. For the structured version with someone checking your work, the Data Analyst course covering SQL, Python, Excel and Power BI is the next step, with the current batch starting 27 September 2026.

Related guides

• PL-300 Exam Prep 2026 turns these skills into a certification.
• Microsoft Fabric vs Databricks in 2026, for when rollups outgrow one Postgres instance.
• Data Engineer Roadmap 2026 maps the path if tuning was the part you enjoyed.
• Data Engineer Jobs in Hyderabad 2026 shows which skills appear in live job ads.
• What Is Apache Iceberg in 2026 covers how partitioning replaces indexing in a lakehouse.

Frequently asked questions

About this guide. 360 Digital Transformation is an Authorized Training Partner of Anthropic and Microsoft. Other certification bodies, vendors and employers named here are not affiliated with us. Tools and versions change quickly; commands and figures cited were checked on 16 September 2026.