How to Optimize SQL Queries: Read the Plan First

September 20, 2026 · By the SaturnSQL team

The fastest way to optimize a SQL query is to stop guessing. Ask the database for its execution plan, find the one step that reads the most rows, and fix that step. Almost every slow query comes down to one of five causes, and the plan tells you which one you have. This guide shows what each cause looks like in real plan output from Postgres, MySQL, SQL Server and Oracle, and what the plan looks like after the fix.

Every plan below was produced while writing this post, against throwaway databases with a 400,000-row orders table on Postgres and 50,000 to 100,000 rows on the others. The numbers are small on purpose: the shapes are what matter, and they are the same shapes you will see at a hundred times the size.

The five reasons a query is slow

1. It reads the whole table because the column it filters on has no index.
2. It has an index but cannot use it because the column is wrapped in a function, compared to a value of a different type, or searched with a leading wildcard.
3. It asks for too much: every column, every row, and the database has to fetch it all before it can hand back the ten rows you wanted.
4. It joins badly: the join column is not indexed, the filter is applied after the join instead of before, or a subquery runs once per row.
5. The planner is working from stale statistics, so it estimates a hundred rows, gets a million, and picks a plan that would have been right for a hundred.

You cannot tell which one you have from the SQL alone. The same query is fine on one table and terrible on another. That is why the first step is always the plan.

Step 1: get the execution plan

Every engine has a statement that returns the plan instead of the rows. Two flavours exist: the estimated plan, which the optimizer produces without running anything, and the actual plan, which runs the query and adds real row counts and timings. The actual plan is more useful; the estimated one is safe to take on anything, including a DELETE.

SQL Server and Oracle make this a two-step ritual, which is why most people only ever look at plans in one tool. In SaturnSQL the Explain button next to Run takes care of the engine-specific incantation and shows the estimated plan for whatever is in the editor, on every engine in the table above except BigQuery, where it shows the dry-run cost instead.

Step 2: read the plan

A plan is a tree. Each line is an operation, indented lines feed the line above them, and the query's result comes out at the top. Read it from the most indented line outwards: that is the order the work happens in, and the deepest, widest step is usually the problem. Four things are worth reading on every line:

• The access method. Seq Scan (Postgres), Table scan (MySQL), Clustered Index Scan or Table Scan (SQL Server) and TABLE ACCESS FULL (Oracle) all mean the same thing: every row in the table was read. Index Scan, Index range scan, Index Seek and INDEX RANGE SCAN mean an index narrowed the search first.
• Estimated rows versus actual rows. Only an actual plan has both. When they differ by more than about ten times, the planner chose a plan for data that does not exist, and that is a statistics problem before it is anything else.
• Rows removed by a filter. Postgres prints Rows Removed by Filter outright. A large number here means the database fetched those rows only to throw them away, which is the signature of a missing or wrong index.
• Cost. A unitless number, not milliseconds. It is only useful for comparing two plans of the same query on the same server, which is exactly what you will be doing.

A worked example on Postgres

Yesterday's paid orders, from a 400,000-row table with a primary key and nothing else:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE created_at > now() - interval '1 day'
  AND status = 'paid';

Gather  (cost=1000.00..8741.78 rows=939 width=28) (actual time=0.166..18.314 rows=863 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  Buffers: shared hit=2942
  ->  Parallel Seq Scan on orders  (cost=0.00..7647.88 rows=552 width=28) (actual time=6.434..14.763 rows=432 loops=2)
        Filter: ((status = 'paid'::text) AND (created_at > (now() - '1 day'::interval)))
        Rows Removed by Filter: 199568
        Buffers: shared hit=2942
Planning Time: 0.022 ms
Execution Time: 18.339 ms

Read from the inside: a Parallel Seq Scan touched every row, two workers each discarded 199,568 of them, and 863 survived. The estimate (939) is close to the actual (863), so statistics are fine. The problem is reason one: nothing lets the database skip straight to yesterday.

CREATE INDEX orders_created_at_idx ON orders (created_at);

Index Scan using orders_created_at_idx on orders  (cost=0.43..63.59 rows=879 width=28) (actual time=0.011..0.131 rows=863 loops=1)
  Index Cond: (created_at > (now() - '1 day'::interval))
  Filter: (status = 'paid'::text)
  Rows Removed by Filter: 576
  Buffers: shared hit=14 read=4
Planning Time: 0.160 ms
Execution Time: 0.160 ms

Same query, 18.3 ms to 0.16 ms, 2,942 buffer reads to 18. The Index Cond line is the range the index answered; the Filter line is what still had to be checked row by row. 576 rows removed by that filter is the remaining cost of not indexing status, and whether that is worth a second column in the index is a judgement call the next section covers.

Fix 1: add the index the WHERE clause needs

When the plan shows a full scan with a big Rows Removed by Filter, index the column that removes the most rows. That is the most selective condition, not necessarily the first one you wrote. A date range on a table that grows daily is almost always selective; a status column with five values almost never is on its own.

For a multi-column index, put the columns compared with = first and the range column last: (status, created_at) lets the database jump to the paid orders and then walk the date range, while (created_at, status) walks the date range and checks status on each row. Both beat no index. The first is better for this query, and useless for a query that filters on date alone, which is the trade-off you are making.

The same query on MySQL 8, which prints its plan as a tree with the same shape:

-> Filter: ((orders.status = 'paid') and (orders.created_at > <cache>((now() - interval 1 day))))  (cost=9426 rows=3335)
    -> Table scan on orders  (cost=9426 rows=100050)

-> Filter: (orders.status = 'paid')  (cost=648 rows=144)
    -> Index range scan on orders using orders_created_at_idx over ('2026-09-19 16:44:07' < created_at),
       with index condition: (orders.created_at > <cache>((now() - interval 1 day)))  (cost=648 rows=1439)

Do not index every column that ever appears in a WHERE clause. Each index is a second copy of that column that every INSERT and UPDATE has to maintain, and an index the planner never chooses is pure cost. Add the one the plan is asking for, run EXPLAIN again, and only keep it if the plan changed. The per-engine syntax is in the learn hub: Postgres, MySQL, SQL Server.

Fix 2: stop hiding the column from its index

The second most common plan is a full scan on a table that has the right index. The index is there; the query is written so the planner cannot use it. Three patterns cause nearly all of these.

A function on the column

An index on created_at stores created_at values. It does not store date_trunc('day', created_at), so a condition on that expression has to compute it for every row:

-- Same table, index in place. Still a full scan:
EXPLAIN SELECT * FROM orders
WHERE date_trunc('day', created_at) = date_trunc('day', now());

Gather  (cost=1000.00..8847.88 rows=2000 width=28)
  Workers Planned: 1
  ->  Parallel Seq Scan on orders  (cost=0.00..7647.88 rows=1176 width=28)
        Filter: (date_trunc('day'::text, created_at) = date_trunc('day'::text, now()))

Move the function to the other side of the comparison and it becomes a plain range the index can answer:

SELECT * FROM orders
WHERE created_at >= date_trunc('day', now())
  AND created_at <  date_trunc('day', now()) + interval '1 day';

The same rule catches YEAR(created_at) = 2026, LOWER(email) = '…', CAST(id AS TEXT) = '42' and total * 1.25 > 100. If you cannot rewrite the query, Postgres and Oracle let you index the expression itself, and SQL Server and MySQL let you index a computed or generated column.

A type mismatch you did not write

This one is invisible in the SQL. On Oracle, created_at is a TIMESTAMP and SYSTIMESTAMP is a TIMESTAMP WITH TIME ZONE. To compare them Oracle converts the column, and a converted column is a function on the column:

EXPLAIN PLAN FOR
SELECT * FROM orders_perf
WHERE created_at > SYSTIMESTAMP - INTERVAL '1' DAY AND status = 'paid';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST +PREDICATE'));

| Id  | Operation         | Name        | Rows | Cost (%CPU)|
|   0 | SELECT STATEMENT  |             | 3000 |   172   (2)|
|*  1 |  TABLE ACCESS FULL| ORDERS_PERF | 3000 |   172   (2)|

Predicate Information (identified by operation id):
   1 - filter("STATUS"='paid' AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("CREATED_AT"))
              >SYS_EXTRACT_UTC(SYSTIMESTAMP(6)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0)))

The predicate section gives it away: INTERNAL_FUNCTION("CREATED_AT") is Oracle telling you it had to convert your column. Compare against a value of the column's own type and the index comes back:

| Id  | Operation                           | Name                       | Rows | Cost (%CPU)|
|   0 | SELECT STATEMENT                    |                            |  480 |    14   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS_PERF                |  480 |    14   (0)|
|*  2 |   INDEX RANGE SCAN                  | ORDERS_PERF_CREATED_AT_IDX | 1439 |     6   (0)|

   1 - filter("STATUS"='paid')
   2 - access("CREATED_AT">LOCALTIMESTAMP(6)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))

Cost 172 to 14, from changing which clock function you call. The MySQL version of this trap is comparing a VARCHAR column to a number (WHERE phone = 5551234), and the SQL Server version is an NVARCHAR parameter against a VARCHAR column. In every case the fix is the same: make the literal or parameter match the column, never the other way round.

Leading wildcards and OR

LIKE '%gmail.com' cannot use a normal index because the index is sorted from the first character. If you need suffix search, store the reversed string and index that, or use a trigram index on Postgres. And WHERE a = 1 OR b = 2 across two columns often becomes a full scan even with both columns indexed; UNION ALL of the two single-column queries usually gets two index scans.

Fix 3: ask for less

SELECT * is slow in a way that only shows up in the plan. An index holds the indexed columns and a pointer to the row; if the query wants columns the index does not have, every matching row costs a second lookup in the table. Past a few percent of the table, the planner decides that is more expensive than reading the whole table once, and ignores your index entirely. SQL Server shows this most clearly:

SELECT * FROM dbo.orders WHERE created_at > DATEADD(day, -1, SYSDATETIME()) AND status = 'paid'
  |--Clustered Index Scan(OBJECT:([dbo].[orders].[PK__orders]), WHERE:([created_at]>dateadd(day,(-1),sysdatetime()) AND [status]=N'paid'))

The index exists and the planner refused it: a clustered index scan is a full table read. Narrow the SELECT and the range, and the index is used, but every hit still costs a Clustered Index Seek back into the table (the "key lookup"):

  |--Nested Loops(Inner Join, OUTER REFERENCES:([id], [Expr1003]) WITH UNORDERED PREFETCH)
       |--Index Seek(OBJECT:([dbo].[orders].[orders_created_at_idx]), SEEK:([created_at] > dateadd(hour,(-2),sysdatetime())) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([dbo].[orders].[PK__orders]), SEEK:([id]=[id]), WHERE:([status]=N'paid') LOOKUP ORDERED FORWARD)

Give the index the columns the query reads and the lookup disappears. This is a covering index: INCLUDE on SQL Server and Postgres 11+, extra trailing columns in the index on MySQL and Oracle.

CREATE INDEX orders_created_status_idx
  ON dbo.orders (created_at) INCLUDE (status, total);

  |--Index Seek(OBJECT:([dbo].[orders].[orders_created_status_idx]), SEEK:([created_at] > dateadd(day,(-1),sysdatetime())), WHERE:([status]=N'paid') ORDERED FORWARD)

One operation, no table access at all. The other half of asking for less is rows. Paginating with OFFSET 100000 LIMIT 20 makes the database produce and discard 100,000 rows to show you 20; paginating with WHERE id > :last_seen ORDER BY id LIMIT 20 (keyset pagination) reads 20. And an aggregate belongs in the database: SELECT count(*) over an indexed column is a fraction of the cost of pulling the rows into your application to count them there.

Fix 4: fix the join, not the query

Joins get blamed for slowness they did not cause. Modern planners are good at joins: in the Postgres plans above, an IN (SELECT …) subquery became a Hash Join without any help. When a join really is the slow step, it is usually one of three things visible in the plan.

• The join column on the many side has no index. Foreign keys are not indexed automatically on Postgres, SQL Server or Oracle. A join from customers to orders on orders.customer_id needs an index on orders.customer_id, or every customer costs a scan of orders. The plan shows it as a Seq Scan or Table scan under a Nested Loop.
• The filter runs after the join. If the plan joins a million rows and then filters to a thousand, move the filter into the smaller side first, either as a WHERE the planner can push down or as a subquery or CTE that is filtered before it is joined. On Postgres 12 and later a CTE is inlined by default; on older versions it is a wall the planner cannot see through, and MATERIALIZED or a subquery makes the choice explicit.
• A subquery runs once per row. A correlated subquery in the SELECT list (SELECT c.name, (SELECT count(*) FROM orders WHERE customer_id = c.id)) shows up as a SubPlan or DEPENDENT SUBQUERY with loops equal to the outer row count. Rewrite it as a LEFT JOIN to a grouped subquery, and it runs once.

Fix 5: give the planner fresh statistics

The planner does not look at your data when it plans. It looks at a summary: row counts, how many distinct values a column has, which values are common. When that summary is stale, after a bulk load, a big delete, or on a table that just grew ten times, the planner confidently picks a plan for the table it remembers. The symptom is an actual plan where rows= estimates and actuals disagree wildly, often a nested loop that expected ten rows and processed a million.

All four engines refresh statistics automatically in the background, so this is rarely the whole story on a healthy database. But it is free to run and it is the first thing to try when the estimates are wrong, because none of the other fixes will land while the planner is being lied to.

The five-minute checklist

1. Get the actual plan with EXPLAIN ANALYZE or the engine's equivalent. If the query writes data, take the estimated plan instead.
2. Find the deepest step with the biggest row count. That is the step you are fixing. Ignore the rest for now.
3. Full scan on a filtered column? Index the most selective column, equality columns first, range last.
4. Full scan with an index present? Look for a function, a cast, a type mismatch or a leading wildcard on the column, and move it to the other side.
5. Index used but still slow? Check for key lookups and SELECT *. Narrow the columns or make the index covering. Check the LIMIT and the pagination.
6. Estimates far from actuals? Refresh statistics and take the plan again before changing anything else.
7. Change one thing, re-plan, compare. Keep the change only if the plan got cheaper. Then go back to step 2.

Frequently asked questions