A slow SQL query is one of the easiest ways to ruin a fast application.

You can have a clean architecture, a great cache, and a powerful server - and still watch a page hang because one query scans a million rows without an index.

Over the years, I have optimized hundreds of SQL queries across many production systems.

Most of the wins come from the same small set of techniques, applied again and again.

Some of them are obvious. Some of them go against advice you have probably heard.

To make them easy to remember, I have grouped 20 proven techniques into 6 themes you can reach for whenever a query gets slow.

In this post, we will explore:

• Write index-friendly (sargable) queries
  • Use indexes wisely
  • Avoid functions on columns in WHERE
  • Avoid leading wildcards in LIKE
  • Match data types exactly
• Fetch only the data you need
  • Stop using SELECT *
  • Filter early
  • Paginate with Keyset instead of OFFSET
  • Query only what changed
• Simplify joins and subqueries
  • Reduce JOIN complexity
  • Choose the right JOIN type
  • Replace redundant subqueries with JOINs or CTEs
  • Prefer EXISTS over IN
• Design your schema for reads
  • Normalize wisely
  • Use materialized views
• Make writes and transactions efficient
  • Batch large operations
  • Keep transactions short
• Let the database help you
  • Read the execution plan
  • Keep statistics up to date
  • Use query hints sparingly
  • Monitor and tune continuously

Let's dive in.

All queries in this post were tested on the PostgreSQL database. The same ideas apply to other databases, though the exact syntax may differ.

An index only helps if your query lets the database use it.

A "sargable" query (from Search ARGument ABLE) is one where the database can jump straight to the rows it needs through an index, instead of scanning the whole table. The four techniques below keep your queries sargable.

Indexes are the single biggest lever for read performance.

An index is a sorted data structure that lets the database find rows without scanning the entire table, much like the index at the back of a book saves you from reading every page.

Create indexes on the columns you filter, join, sort, and group by most often - the columns in your WHERE, JOIN, ORDER BY, and GROUP BY clauses.

When several columns are used together, a single composite index that covers them is far better than separate single-column indexes:

sql
1-- A composite index for frequent filters on status and order date
2CREATE INDEX idx_orders_status_order_date
3    ON orders (status, order_date);

This index speeds up queries that filter on status, and on status together with order_date.

Column order matters: an index on (status, order_date) helps queries that filter by status first, but not queries that filter by order_date alone.

You can also create a covering index that stores extra column values inside the index. This helps when a query only needs columns available in the index, so the database can avoid reading the actual table rows.

It is especially useful for small, frequent lookup queries.

sql
1-- Cover a common read query without fetching rows from the table
2CREATE INDEX idx_orders_status_order_date_covering
3    ON orders (status, order_date)
4    INCLUDE (customer_id, total_amount);
5
6SELECT customer_id, total_amount
7FROM orders
8WHERE status = 'paid'
9  AND order_date >= DATE '2026-01-01';

Note: indexes are not free. Every index must be updated on each insert, update, and delete, and it takes disk space. Index the columns your queries actually use, not every column.

For a deeper look at index types and trade-offs, see Optimizing SQL Performance with Indexing Strategies.

Wrapping a column in a function is one of the most common ways to accidentally disable an index.

When you call a function on a column, the database has to compute that function for every row before it can compare it, so it cannot use the index on the raw column:

sql
1-- Bad: the function on order_date prevents an index scan
2SELECT * FROM orders
3WHERE EXTRACT(YEAR FROM order_date) = 2025;

Rewrite the condition to compare the raw column against a range. This keeps the query sargable and lets the index do its job:

sql
1-- Good: a sargable range over the raw column
2SELECT * FROM orders
3WHERE order_date >= '2025-01-01'
4  AND order_date <  '2026-01-01';

Both queries return the same rows, but only the second can use an index on order_date.

The same rule applies to LOWER(email), CAST(...), and arithmetic on a column. If you often need to filter on a computed value, create an expression index for that exact expression instead.

A LIKE pattern that starts with a wildcard cannot use a normal index.

The database reads an index from left to right, so it needs to know the beginning of the value. A pattern like '%son' hides the beginning and forces a full scan:

sql
1-- Bad: leading wildcard forces a full table scan
2SELECT * FROM customers
3WHERE last_name LIKE '%son';
4
5-- Good: a known prefix allows an index range scan
6SELECT * FROM customers
7WHERE last_name LIKE 'Anders%';

If you genuinely need to search inside text - a "contains" search - a normal B-tree index will not help.

Reach for full-text search or a trigram index (the pg_trgm extension in PostgreSQL) built for that job.

Comparing two different data types forces the database to convert one of them, and that conversion can silently disable an index.

If a column is an integer but you compare it to a string, or you join an integer key to a bigint key, the database adds an implicit cast - and the index on the original column may be skipped.

Keep the types identical on both sides of every join and filter:

sql
1CREATE TABLE logs (
2    log_id     int PRIMARY KEY,
3    event_date timestamptz NOT NULL,
4    user_id    int NOT NULL
5);

Define logs.user_id to match the type of users.id so the join uses the index on both sides.

Matching types is a small detail at design time that saves you from a confusing, hard-to-spot scan later.

The fastest data to process is the data you never read.

Every column and every row you pull costs disk I/O, memory, and network time. These four techniques shrink the result set as early and as much as possible.

SELECT * pulls every column, including the ones you do not need.

That means more data to read from disk, more to send over the network, and more memory to hold - all for columns your code throws away.

It also blocks covering indexes, where the index alone can answer the query without touching the table:

sql
1-- Bad: fetches every column
2SELECT * FROM customers;
3
4-- Good: only the columns you use
5SELECT customer_id, first_name, last_name
6FROM customers;

Listing columns explicitly is also safer. Your query will not silently change shape or break when someone adds or reorders a column.

The smaller the dataset you work with, the faster everything downstream becomes.

You may have heard the common advice:
Apply your most selective filters to cut the number of rows before joins and aggregations have to process them:

sql
1SELECT o.order_id, o.total
2FROM orders o
3WHERE o.order_date >= '2025-01-01'
4  AND o.order_date <  '2025-02-01';

Note: in most cases you do not have to place these filters by hand. A modern cost-based planner already pushes predicates down - it applies your WHERE conditions as early as it can and reorders joins on its own. Rewriting the textual order of a WHERE clause rarely changes the plan.

What actually helps is giving the planner a selective, sargable filter and an index to apply it with. So focus on making the filter index-friendly, not on where you type it.

OFFSET looks like an easy way to implement pagination, but it gets slower the deeper you go.

To return page 1,000, the database still reads and throws away every row before it. OFFSET 100000 means reading 100,000 rows just to skip them.

Keyset pagination (also called seek pagination) remembers the last value you saw and seeks straight past it:

sql
1-- Key-based pagination on an indexed column
2SELECT *
3FROM orders
4WHERE order_id > 1000
5ORDER BY order_id
6LIMIT 10;

Each page costs the same, whether it is page 2 or page 2,000, because the index jumps directly to order_id > 1000.

The trade-off: keyset pagination gives you fast next- and previous-page navigation, but you can't implement random jumps to any page number.

Re-reading an entire table on every run is wasteful when only a few rows have changed.

Track a watermark - the last point you processed - and fetch only the rows newer than it:

sql
1-- Fetch only records updated since the last run
2SELECT *
3FROM records
4WHERE modified_date > '2025-05-20 00:00';

This incremental pattern turns a full-table scan into a small, indexed range read.

Index modified_date, store the new high-water mark after each run, and your sync job stays fast even as the table grows.

Joins and subqueries are where queries get expensive, and where the biggest clean-ups hide.

The goal is to ask the database to do less work and to express that work in the form it handles best.

Every join is extra work. The fewer tables the database has to match up, the faster the query.

A common mistake is joining a table you never actually read from:

sql
1-- Bad: joins suppliers but never uses it
2SELECT p.product_name, c.category_name
3FROM products p
4JOIN categories c ON p.category_id = c.category_id
5JOIN suppliers  s ON p.supplier_id = s.supplier_id;

Drop the join you do not need, and the database does less work for the same result:

sql
1-- Good: only the joins that contribute to the output
2SELECT p.product_name, c.category_name
3FROM products p
4JOIN categories c ON p.category_id = c.category_id;

Read your SELECT and WHERE lists and remove any joined tables that are not referenced in either list.

The join type changes both the result and the cost.

Use an INNER JOIN when you need matching rows from both sides, a LEFT JOIN only when you genuinely need the unmatched rows too, and EXISTS when you just need to know whether a match exists:

sql
1-- Just checking existence: EXISTS stops at the first match
2SELECT c.customer_id, c.name
3FROM customers c
4WHERE EXISTS (
5    SELECT 1 FROM orders o
6    WHERE o.customer_id = c.customer_id
7      AND o.total > 100
8);

A LEFT JOIN is used where an INNER JOIN would do - forces the database to carry unmatched rows, which it will only discard later.

For a full breakdown of join types and when each one fits, see A Complete Guide to Different Types of Joins in SQL.

A correlated subquery runs once per row, which can be brutal on a large result set.

Here, the subquery fires for every order, just to look up a customer name:

sql
1-- Bad: a subquery executed per row
2SELECT o.order_id,
3       (SELECT c.name FROM customers c
4        WHERE c.customer_id = o.customer_id) AS customer_name
5FROM orders o;

A single join does the same work once, in one pass:

sql
1-- Good: one join instead of a per-row lookup
2SELECT o.order_id, c.name AS customer_name
3FROM orders o
4JOIN customers c ON o.customer_id = c.customer_id;

When the same subquery is needed multiple times in a single statement, lift it into a common table expression (CTE) with WITH so it is written once and is easier to read.

Explore the slow query example:

sql
1-- Bad: the same aggregate subquery is repeated
2SELECT
3  c.customer_id,
4  c.name,
5  (
6    SELECT SUM(o.total_amount)
7    FROM orders o
8    WHERE o.customer_id = c.customer_id
9      AND o.order_date >= DATE '2026-01-01'
10  ) AS total_spent,
11  (
12    SELECT COUNT(*)
13    FROM orders o
14    WHERE o.customer_id = c.customer_id
15      AND o.order_date >= DATE '2026-01-01'
16  ) AS order_count
17FROM customers c;

And here is a faster query with CTA:

sql
1-- Good: calculate the result once and reuse it
2WITH customer_order_totals AS (
3  SELECT
4    customer_id,
5    SUM(total_amount) AS total_spent,
6    COUNT(*) AS order_count
7  FROM orders
8  WHERE order_date >= DATE '2026-01-01'
9  GROUP BY customer_id
10)
11SELECT
12  c.customer_id,
13  c.name,
14  COALESCE(t.total_spent, 0) AS total_spent,
15  COALESCE(t.order_count, 0) AS order_count
16FROM customers c
17LEFT JOIN customer_order_totals t ON t.customer_id = c.customer_id;

EXISTS can short-circuit: it stops at the first matching row, while IN may build the full list of values first.

sql
1-- Existence check that stops at the first hit
2SELECT p.product_id, p.product_name
3FROM products p
4WHERE EXISTS (
5    SELECT 1 FROM order_details od
6    WHERE od.product_id = p.product_id
7);

Note: do not treat "EXISTS always beats IN" as a hard rule. Modern PostgreSQL often rewrites IN, EXISTS, and even some joins into the same execution plan, so the two can perform identically.

The case that still bites is NOT IN with a subquery that can return NULL - it produces surprising results and a worse plan, so prefer NOT EXISTS there. As always, check the plan instead of assuming.

Some queries are slow no matter how you write them, because they recompute the same heavy result over and over.

The fix is to change the shape of your data.

Normalization keeps your data clean and consistent, and it is the right default.

But fully normalized data can be slow to read when a hot query has to join and aggregate the same tables on every request.

For read-heavy paths, it is fine to denormalize: pre-compute a summary and store it.

sql
1-- Pre-aggregate total sold per product into a summary table
2CREATE TABLE sales_summary AS
3SELECT product_id, SUM(quantity) AS total_sold
4FROM order_details
5GROUP BY product_id;

Now the read is a simple lookup rather than a live aggregation across the entire order_details table.

The trade-off is that you must keep the summary in sync - refresh it on a schedule, or when the source data changes. Denormalize on purpose, for specific hot queries, not everywhere.

A materialized view stores the result of a query physically, so reads hit pre-computed rows instead of recalculating them.

It is the built-in version of the summary table above, and PostgreSQL supports it directly:

sql
1-- Store a pre-computed aggregation
2CREATE MATERIALIZED VIEW mv_total_sales AS
3SELECT product_id, SUM(quantity) AS total_qty
4FROM order_details
5GROUP BY product_id;
6
7-- A unique index lets the view refresh without locking readers
8CREATE UNIQUE INDEX idx_mv_total_sales_product
9    ON mv_total_sales (product_id);
10
11-- Refresh on a schedule; readers keep the old data until it is complete
12REFRESH MATERIALIZED VIEW CONCURRENTLY mv_total_sales;

Queries against mv_total_sales are fast because the aggregation has already run.

The data is only as fresh as your last refresh, so use materialized views for expensive aggregations that can tolerate slight staleness - dashboards, reports, and leaderboards. To learn more, see Get Started with Database Views in SQL.

Reads get most of the attention, but slow writes and long transactions cause lock contention, making everyone's queries wait.

Two techniques keep your writes lean.

Running one statement per row floods the database with round-trips and transaction overhead.

But a single statement that touches millions of rows is also a problem - it holds locks for a long time and can blow up your write-ahead log.

The middle ground is batching: process a fixed chunk at a time. This statement moves rows into an archive table 1,000 at a time, deleting each chunk as it copies it:

sql
1-- Move rows in batches of 1000 until none are left
2WITH batch AS (
3    DELETE FROM source_table
4    WHERE ctid IN (
5        SELECT ctid
6        FROM source_table
7        WHERE processed = false
8        LIMIT 1000
9    )
10    RETURNING col1, col2
11)
12INSERT INTO archive_table (col1, col2)
13SELECT col1, col2 FROM batch;

Run this in a loop until it affects zero rows.

Each batch commits quickly, holds few locks, and keeps the system responsive while the bulk job runs.

A transaction holds its locks until it commits, and every other query that needs those rows has to wait.

The longer a transaction stays open, the more contention it creates. Keep the transaction around the writes only, and do slow work - API calls, file I/O, heavy computation - outside it:

sql
1BEGIN;
2
3UPDATE accounts
4SET balance = balance - 100
5WHERE account_id = 1;
6
7INSERT INTO transactions (account_id, amount)
8VALUES (1, -100);
9
10COMMIT;

This transaction opens, makes two related writes, and commits immediately.

Never leave a transaction open while waiting on user input or a network call. To see how locking and isolation interact, read A Complete Guide to Transaction Isolation Levels in SQL.

This last group is about working with the database engine instead of guessing.

The query planner is smarter than it gets credit for. Your job is to feed it good information and then verify the result.

The execution plan is the database telling you exactly how it will run your query.

Before optimizing anything, look at the plan. In PostgreSQL, EXPLAIN ANALYZE runs the query and reports what really happened:

sql
1EXPLAIN ANALYZE
2SELECT * FROM orders WHERE total > 100;

Read it for the expensive operations: a Seq Scan on a big table usually means a missing or unused index, and a large gap between estimated and actual rows points to stale statistics.

The plan turns optimization from guesswork into evidence. You fix what the plan shows is slow, without assumptions.

The planner chooses between a scan and an index seek based on statistics about your data - how many rows there are, how many distinct values, and how they are spread out.

When those statistics are stale, the planner makes bad guesses and picks bad plans, even with perfect indexes.

ANALYZE refreshes them:

sql
1-- Refresh planner statistics for a table
2ANALYZE orders;

PostgreSQL runs autovacuum to keep statistics current, but after a large bulk load or a mass update, it is worth running ANALYZE yourself.

This is the other half of the optimizer story: the engine can only optimize as well as its statistics let it. Good stats matter far more than the exact wording of your query.

A query hint forces the database to run a query your way rather than the planner's.

PostgreSQL deliberately ships without hint syntax. Its philosophy is that you should fix the root cause - indexes, statistics, query shape - rather than override the planner.

You can nudge it with session settings, but treat this as a diagnostic only in the development environment:

sql
1-- Diagnostic only: see what the plan looks like without a sequential scan
2SET enable_seqscan = off;

If you truly need hints, the pg_hint_plan extension adds them - but reach for it last, after indexes and statistics have failed you.

A hint freezes a decision that is right today and may be wrong after your data grows, so it quietly becomes tomorrow's slow query.

Optimization is not a one-time task. Data grows, access patterns shift, and yesterday's fast query becomes today's bottleneck.

Track which queries actually cost the most. The pg_stat_statements extension aggregates execution stats across your whole workload:

sql
1-- Find the slowest queries by average execution time
2SELECT query,
3       calls,
4       mean_exec_time
5FROM pg_stat_statements
6ORDER BY mean_exec_time DESC
7LIMIT 10;

Let this list tell you what to optimize next.

And this is where all the techniques in this post come together. A modern planner already does the clever rewrites - predicate pushdown, join reordering, turning IN into a join. You do not earn much by hand-tuning the text of a WHERE clause. The big wins come from giving the planner what it needs: sargable predicates, fresh statistics, and the right indexes - then measuring to confirm.

Optimizing SQL queries is part art, but it is definitely not rocket science. And it always pays off.

Let's recap the key takeaways:

• Keep queries sargable. Index the columns you filter, join, and sort on, and never hide them behind a function, a leading wildcard, or a type mismatch - that is what lets an index do its job.
• Touch less data. Skip SELECT *, filter to the rows you need, paginate by key instead of a deep OFFSET, and read only what changed.
• Simplify the shape. Drop joins you do not use, pick the right join type, and replace per-row subqueries with joins or CTEs.
• Design for reads. Denormalize hot paths on purpose, and pre-compute heavy aggregations with materialized views.
• Keep writes lean. Batch large operations and keep transactions short to avoid lock contention.
• Trust the optimizer, then verify. Read the execution plan, keep statistics fresh, avoid hints, and let pg_stat_statements point you at the real bottleneck.

Before any optimization, remember: Measure first. Often, you will find the query is already fast enough, and the best optimization is the one you did not need to make.

Hope you find this newsletter useful. See you next time.