The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Query Optimization Techniques

Next ►
►

There's an almost universal reflex when a query runs slowly: create an index. And it's often the wrong answer, because an index can't fix a query that's asking for unnecessary work. A query that brings back 200 columns to display 3, that filters after grouping, that wraps the filtered column in a function or that runs 200 times from the application's loop doesn't have an index problem: it has a writing problem, and no CREATE INDEX is going to solve it.

This lesson is the catalogue of those techniques. First, eight writing rules with their before and after, several of which close promises from modules 2, 4 and 7. Then, how the statistics that feed the planner work and what happens when they go stale. And at the end, the part that saves the day most often: what to do when the problem isn't in the query, starting with the most expensive and most frequent antipattern of all, the N+1. The order of intervention, worth committing to memory: first you rewrite the query, then you index, then you change the architecture, and only at the very end do you buy hardware. It goes from cheap and reversible to expensive and permanent.

Contents

  1. Eight writing rules
  2. Sargability, the rule that governs the rest
  3. Statistics and the planner
  4. Extended statistics for correlated columns
  5. When the problem isn't the query: the N+1
  6. The next steps when that's no longer enough
  7. Diagnostic table: symptom → likely cause → what to try
  8. Common Mistakes and Tips
  9. Exercises
  10. Conclusion

  1. Eight writing rules

Rule 1: don't ask for columns you aren't going to use

-- ⚠️ INCORRECT
SELECT * FROM products AS p WHERE p.category_id = 4;

-- ✅ CORRECT
SELECT p.id, p.name, p.price FROM products AS p WHERE p.category_id = 4;
id name price
14 Organic chamomile tea 20 bags 3.25
15 Ceremonial matcha green tea 30 g 22.00
16 Ginger kombucha 750 ml 4.95
17 Cold-pressed orange juice 1 L 5.40

Three reasons, in order of importance. It prevents the Index Only Scan: if an index on (category_id) INCLUDE (name, price) existed, the second version would be resolved without touching the table, whereas the first forces the nine columns to be fetched row by row. It sends less data over the network: a 4 kB TEXT column nobody displays, times a thousand rows, is 4 MB thrown away. And it's fragile: a SELECT * changes shape when somebody adds a column.

Rule 2: filter as early as possible — WHERE before HAVING

This closes the thread opened in 04-06. Both queries give the same thing:

-- ⚠️ INCORRECT: it groups all 15 customers and then throws 2 groups away
SELECT c.country, COUNT(*) AS customers FROM customers AS c GROUP BY c.country HAVING c.country = 'Spain';

-- ✅ CORRECT: it discards 4 rows before grouping
SELECT c.country, COUNT(*) AS customers FROM customers AS c WHERE c.country = 'Spain' GROUP BY c.country;
country customers
Spain 11

The difference is one of logical order (module 2): WHERE is applied before grouping, HAVING after. The first version builds three groups and discards two; with fifteen customers it makes no difference, with fifteen million the HashAggregate processes three times the data for nothing. And on top of that, the WHERE's condition can take advantage of an index; the HAVING's, never.

The rule: HAVING is exclusively for conditions on aggregates (HAVING COUNT(*) > 1). If the condition can be written without an aggregate, it goes in the WHERE.

Rule 3: the filtered column, always bare

-- ⚠️ INCORRECT: EXTRACT over the column cancels out any index
SELECT COUNT(*) AS orders_2025 FROM orders AS o
WHERE EXTRACT(YEAR FROM o.order_date) = 2025;

-- ✅ CORRECT: a date range, the column appears on its own
SELECT COUNT(*) AS orders_2025 FROM orders AS o
WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';
orders_2025
16

The same 16 orders from 2025, and the second version can use an index on order_date. It's section 2's canonical example. Notice the detail of the upper bound: < '2026-01-01', not <= '2025-12-31'. With a DATE they're equivalent, but if tomorrow the column becomes a TIMESTAMP, the <= would lose everything that happened on 31 December after midnight. The pattern >= start AND < next_start is always correct; get used to it.

The same goes for type conversions —WHERE o.order_date::text LIKE '2025-03%' converts the column, so it cancels the index, and it has to be written as the range >= '2025-03-01' AND < '2025-04-01'— and for calculations: WHERE p.price * 1.21 > 15 doesn't use an index; WHERE p.price > 15 / 1.21 does, because the calculation is on the constant's side.

Rule 4: LIMIT with an ORDER BY on an indexed column

SELECT p.id, p.name, p.price
FROM products AS p
ORDER BY p.price DESC
LIMIT 5;
id name price
15 Ceremonial matcha green tea 30 g 22.00
6 Aloe vera face cream 50 ml 18.90
20 Spirulina capsules 120 units 16.40
8 Almond body oil 200 ml 14.25
13 Soy wax candles (pack of 2) 13.75

With no index on price, the engine has to examine every row to know which are the top five. With many rows it uses the top-N heapsort you saw in 02-05, which avoids sorting them all but still has to read them all. With an index on price, it reads five entries from the end of the tree and stops: a cost independent of the table's size. It's one of the best effort/benefit ratios there is, because "top 10" lists are on every home screen of every application.

Rule 5: EXISTS, not COUNT(*) > 0

Picking up 07-03:

-- ⚠️ INCORRECT: it counts product 1's 5 lines just to know there's at least one
SELECT p.id, p.name FROM products AS p
WHERE (SELECT COUNT(*) FROM order_lines AS ol WHERE ol.product_id = p.id) > 0;

-- ✅ CORRECT: it stops at the first match
SELECT p.id, p.name FROM products AS p
WHERE EXISTS (SELECT 1 FROM order_lines AS ol WHERE ol.product_id = p.id);

The same 17 products sold. COUNT(*) forces all of each product's lines to be walked; EXISTS short-circuits at the first one. With 5 lines the difference is nil; with 40,000 sales of a product, it's reading them all against reading one. And there's a plan bonus: EXISTS becomes a semi-join (07-05), whereas the subquery with COUNT is almost never transformed.

Rule 6: UNION ALL when there can be no duplicates

-- ⚠️ INCORRECT if you know the sets are disjoint
SELECT id, order_date FROM orders WHERE order_date <  '2026-01-01'
UNION
SELECT id, order_date FROM orders WHERE order_date >= '2026-01-01';

-- ✅ CORRECT
SELECT id, order_date FROM orders WHERE order_date <  '2026-01-01'
UNION ALL
SELECT id, order_date FROM orders WHERE order_date >= '2026-01-01';

The same 20 rows (16 from 2025 + 4 from 2026), because an order can't be on both sides of a date. But UNION removes duplicates, and that forces the engine to sort the 20 rows or build a hash table with all of them before returning anything; UNION ALL concatenates them and that's that. The rule: UNION ALL by default, UNION only when you genuinely need to deduplicate. Free deduplication doesn't exist.

Rule 7: DISTINCT isn't a patch for a badly framed JOIN

This closes 02-04 and confirms what was said in 07-05:

-- ⚠️ INCORRECT: it generates 47 rows and discards 30
SELECT DISTINCT p.id, p.name FROM products AS p
JOIN order_lines AS ol ON ol.product_id = p.id;

-- ✅ CORRECT: it returns 17 from the start
SELECT p.id, p.name FROM products AS p
WHERE EXISTS (SELECT 1 FROM order_lines AS ol WHERE ol.product_id = p.id);

17 products in both cases, but the first produces 47 rows —one per order line, with the olive oil repeated five times— and then deduplicates them: work done just to undo it. A DISTINCT in your query is a diagnostic signal: ask yourself which JOIN is multiplying rows and whether you really needed it. When the DISTINCT is legitimate —"the list of cities where we have customers or employees", 11 cities out of 23 rows— there's nothing to correct.

Rule 8: keyset pagination, not a large OFFSET

This closes 02-06. Both return the fourth page:

-- ⚠️ INCORRECT with large offsets
SELECT id, order_date FROM orders ORDER BY id LIMIT 5 OFFSET 15;

-- ✅ CORRECT: it remembers where the previous page left off
SELECT id, order_date FROM orders WHERE id > 15 ORDER BY id LIMIT 5;
id order_date
16 2025-12-19
17 2026-01-13
18 2026-01-27
19 2026-02-09
20 2026-02-21

The problem with OFFSET is that it doesn't skip: it reads and discards. To serve page 1,000 with 20 items, the engine reads 20,000 rows and throws away 19,980.

OFFSET Keyset
Cost of page N Grows linearly with N Constant
Takes advantage of the index Only for sorting For sorting and for positioning
Jumping to "page 500" ✅ Direct ❌ You have to chain
Duplicated or lost rows if somebody inserts Yes No
Typical use Numbered paginators Infinite scroll, APIs, exports

With a composite ordering, keyset pagination uses tuple comparison —WHERE (order_date, id) < ('2026-01-13', 17) ORDER BY order_date DESC, id DESC LIMIT 5—, which is what modern API cursors do underneath.

  1. Sargability, the rule that governs the rest

Rules 3, 4 and 8 are the same idea in three disguises, and that idea has a name: sargability. It comes from SARG, Search ARGument.

A condition is sargable when the engine can translate it into "position yourself at a point in the index and move forward". In practice: the column appears on its own on one side of the comparison, with no functions, no calculations and no type conversions.

Condition Sargable? Sargable rewrite
EXTRACT(YEAR FROM order_date) = 2025 ❌ order_date >= '2025-01-01' AND order_date < '2026-01-01'
LOWER(email) = '[email protected]' ❌ An index on LOWER(email) (08-02), or normalise on save
name LIKE '%organic%' ❌ pg_trgm + GIN (08-03)
name LIKE 'Organic%', quantity BETWEEN 2 AND 5 ✅ —
price * 1.21 > 15 ❌ price > 15 / 1.21
order_date::text LIKE '2025%' ❌ A date range
id + 0 = 7 ❌ id = 7
status <> 'delivered' ⚠️ Technically yes, useless in practice status IN ('pending','paid','shipped','cancelled'), or a partial index

Internalising this word saves you memorising the list: every time you write a WHERE, look at whether the column is bare. If it isn't, you have three ways out, in this order of preference: rewrite the condition, index the expression, or normalise the data on save (for example, storing the email already lowercased and saving yourself the LOWER for good).

  1. Statistics and the planner

The planner doesn't guess: it estimates. And it estimates from statistics PostgreSQL keeps about each table and each column: the number of rows and of pages, the fraction of nulls, the number of distinct values (08-03's cardinality), the most frequent values with their frequency, and a histogram that spreads the rest into buckets to estimate ranges. With that it answers the key question: "how many rows will WHERE status = 'delivered' return?". If it estimates 14 of 20 (70 %), it chooses Seq Scan. If it estimates 1 of 2,000,000, it chooses Index Scan. The whole quality of the plan depends on that estimate being reasonable.

SELECT attname          AS column_name,
       n_distinct       AS distinct_values,
       null_frac        AS null_fraction,
       most_common_vals AS most_frequent
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'employee_id');
column_name distinct_values null_fraction most_frequent
status 5 0 {delivered,shipped,paid,cancelled,pending}
employee_id 3 0.5 {4,5,6}

There it is, in two rows, what the planner knows about orders: that status has 5 values and that half the employee_ids are null (01-06's ten web orders).

How they're collected and when they go stale

They're collected by ANALYZE table; when you run it, by autovacuum when a table accumulates a certain percentage of changes, and by VACUUM ANALYZE table; at the same time as it cleans up. The problem appears when they get old, and there are three classic situations:

  1. Right after a bulk load. You insert 5 million rows; until autovacuum comes round, the planner still believes the table has 100 and will choose a Nested Loop over millions of rows. After any large load, run ANALYZE by hand.
  2. After a migration that changes a column's distribution (05-06).
  3. On fast-growing tables, where autovacuum's threshold arrives too late.

The symptom is unmistakable and you'll see it in 08-05: an enormous divergence between the estimated and the actual rows in the execution plan. And when the problem isn't that they're old but that the sample is too small, you raise the detail —default_statistics_target, which defaults to 100 and controls how many frequent values and how many histogram buckets are stored:

ANALYZE orders;                                                -- one table
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;      -- more detail, that column only
ANALYZE orders;                                                -- indispensable afterwards

Raising it improves the estimates on columns with very uneven distributions in exchange for a slower ANALYZE and a slightly slower planner. Raise it per column, never globally "just in case", and only when a plan has proved to you that the estimate is wrong.

  1. Extended statistics for correlated columns

There's an estimation failure no amount of sampling fixes: the planner assumes the columns are independent, and in the real world they almost never are. In GreenStore, category_id and supplier_id are clearly related: the cosmetics come from Maison Nature and Verde Atlántico, the food from Huerta del Turia and BioSierra.

SELECT COUNT(DISTINCT category_id) AS categories,
       COUNT(DISTINCT supplier_id) AS suppliers,
       (SELECT COUNT(*) FROM (SELECT DISTINCT category_id, supplier_id FROM products) AS x)
                                   AS actual_combinations
FROM products;
categories suppliers actual_combinations
6 5 12

Six times five is thirty possible combinations, but only twelve exist. Faced with WHERE category_id = 2 AND supplier_id = 4, the planner multiplies selectivities as if they were independent: 1/6 × 1/5 = 1/30, and estimates fewer than one row. The reality is 3 products. With 20 rows it's irrelevant; with 20 million, an underestimate like that makes the engine choose a Nested Loop where a Hash Join was needed, and the query goes from seconds to hours.

The solution is extended statistics, available since PostgreSQL 10, with three kinds: ndistinct captures the actual number of distinct combinations (12, not 30); dependencies, the functional dependencies ("knowing the supplier almost determines the category"); mcv, the most frequent specific combinations.

CREATE STATISTICS stat_products_cat_sup (ndistinct, dependencies)
    ON category_id, supplier_id FROM products;
ANALYZE products;

SELECT statistics_name, attnames, kinds FROM pg_stats_ext;
statistics_name attnames kinds
stat_products_cat_sup {category_id,supplier_id} {d,f}

The typical candidates are the pairs that "go together": postcode and city, make and model, country and currency, category and supplier. Create them when a plan shows you an estimate far removed from reality, not before.

  1. When the problem isn't the query: the N+1

And now the most expensive of them all, which can't even be seen from the database. The N+1 happens when the application fires one query to get a list and then one more query for each item in that list.

1 query:    SELECT id, customer_id, order_date FROM orders;        -- 20 rows
20 queries: SELECT name, last_name FROM customers WHERE id = ?;    -- one per order
------------------------------------------------------------------------
Total: 21 queries to paint one table

Against a single query:

SELECT o.id, o.order_date, c.name || ' ' || c.last_name AS customer
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id ORDER BY o.id;

One query, 20 rows. The problem isn't each query's time —each one takes 0.2 ms— but the fixed cost paid 21 times: network round trip, parsing, planning, execution, transfer. And it scales terribly: a screen of 500 orders is 501 queries.

N+1 (21 queries) One JOIN (1 query)
Network round trips and plannings 21 1
Time with 1 ms latency ~25 ms ~2 ms
With 500 items ~520 ms ~4 ms
With 500 items and 20 ms latency ~10 s ~25 ms

It's extremely hard to detect by looking at the database, because each individual query is lightning fast: it doesn't show up in the slow-query ranking. It shows up in pg_stat_statements as a query with a minimal mean_exec_time and an absurd number of calls — another reason to sort by total time. It almost always comes out of an ORM: a loop walking objects and accessing a related property, firing one query per iteration without it being visible in the code. They all have the remedy (JOIN FETCH in JPA, select_related/prefetch_related in Django, includes in Rails, Include in Entity Framework); the problem is remembering to use it.

The other two application problems are its cousins. Not batching: inserting 10,000 rows with 10,000 INSERTs instead of one with 10,000 tuples, or with COPY — same mechanism, fixed cost multiplied, and the reason 01-06's script uses multi-row INSERTs. And fetching rows that are never displayed: downloading two million orders so the application can keep 20. If the screen shows 20, the query should ask for 20, with a LIMIT and with the filtering done in SQL; filtering in the application's memory is the most silent waste there is.

  1. The next steps when that's no longer enough

When the query is already well written, the indexes are in place and it still isn't enough, these steps remain — from least to most invasive:

Technique When it applies Cost / risk
Materialized views An expensive report queried many times that can live with data a few hours old They have to be refreshed; the data isn't instantaneous → 10-01
Summary tables Aggregates queried constantly (sales by day and category) They have to be kept in sync, with triggers (10-05) or a scheduled process
Application cache Data that changes little and is read a great deal (the catalogue, the categories) Invalidation: the genuinely hard problem
Partitioning An enormous table with a natural cut-off criterion, typically the date It changes the DDL and queries have to filter by the partition key
Read replicas Far more reads than writes; reports competing with operations Replication lag: the replica runs a few milliseconds behind
Sharding When neither one machine nor replicas are enough Enormous: it spreads the data across servers and complicates every query

The last two deserve a note. Partitioning physically divides a table into pieces (for example, one per year of order_date) so that a bounded query only reads the one it needs and so that a whole year can be deleted without a massive DELETE; it's an advanced administration tool and this course doesn't cover it. Sharding spreads the data across several servers and is the last card. And a general warning: these steps don't fix a badly written query, they hide it. A materialized view over a SELECT with an unnecessary DISTINCT still has the unnecessary DISTINCT, only now in the refresh process.

  1. Diagnostic table: symptom → likely cause → what to try

Symptom Likely cause What to try
A query that used to be fine has become slow with no changes Stale statistics after growth or a load ANALYZE table; and compare the plan
Seq Scan over a large table with a very selective filter The index is missing, or the condition isn't sargable Create the index; check for functions over the column
The index exists but isn't used Low selectivity, non-sargable condition, an expression that doesn't match, bad statistics EXPLAIN (08-05); check how many rows the filter returns
Lots of almost identical, lightning-fast queries N+1 from the application Sort pg_stat_statements by calls; use a JOIN or eager loading
Slow only on page 300 of the listing A large OFFSET Keyset pagination
Slow ever since an ORDER BY was added Sorting with no index, a Sort spilling to disk An index that gives the order; raise work_mem; review the LIMIT
The INSERT is slow and wasn't before Too many indexes, or GIN indexes Review pg_stat_user_indexes and drop the dead ones
Estimates far removed from the actual rows Correlated columns, or a short sample CREATE STATISTICS; raise the column's STATISTICS
Everything is slow at once It isn't the query: memory, disk, locks, pending VACUUM System metrics; locks → module 9
A one-row DELETE that takes seconds An unindexed FK on a child table with CASCADE An index on the FK column (08-01)

Common Mistakes and Tips

  • Optimizing before measuring. Rewriting a readable query into a cryptic one on a hunch makes you lose readability and, almost always, gain nothing. Measure first (08-05).
  • Putting in the HAVING what belongs in the WHERE. HAVING is only for conditions on aggregates.
  • Using <= on a date range's upper bound. < '2026-01-01' is always correct, even if the column becomes a TIMESTAMP.
  • Adding DISTINCT to "fix" repeated rows. The DISTINCT hides the surplus JOIN instead of removing it.
  • Using UNION out of habit. Deduplicating costs a full sort or a full hash table.
  • Paginating with OFFSET in an API. As well as being slow, it skips and repeats rows when somebody inserts while the user is browsing.
  • Forgetting ANALYZE after a bulk load. It's the number-one cause of "the same query that flew yesterday won't finish today". And don't raise default_statistics_target globally to fix two columns: do it per column.
  • Tip: count the queries, not just the milliseconds. A query counter per request detects an N+1 in five minutes; the slow-query ranking never will.
  • Tip: keep the original query commented out when you rewrite it for performance, with a note on what improved. It's the cheapest documentation there is.
  • Tip: apply the rules in order of cost. Rewriting is free and reversible; buying hardware is expensive and permanent.

Exercises

Exercise 1

Rewrite these four queries to make them sargable or more efficient, and explain what each change improves.

-- a)
SELECT * FROM orders WHERE EXTRACT(MONTH FROM order_date) = 3
                       AND EXTRACT(YEAR FROM order_date) = 2025;
-- b)
SELECT c.country, COUNT(*) FROM customers c GROUP BY c.country HAVING c.country <> 'Spain';
-- c)
SELECT DISTINCT c.id, c.name
FROM customers c JOIN orders o ON o.customer_id = c.id JOIN order_lines ol ON ol.order_id = o.id;
-- d)
SELECT id, name FROM products ORDER BY id LIMIT 5 OFFSET 15;

Exercise 2

GreenStore's "latest orders" screen shows 20 orders with the customer's name, the sales rep's name and the number of lines of each one. The team reports that it takes 4 seconds and that the log shows 61 queries per page load.

  1. Which antipattern is it and where exactly do the 61 queries come from?
  2. Write a single query returning everything needed.
  3. Which indexes would help, given what 08-01 said about foreign keys?

Exercise 3

A nightly report that took 20 seconds now takes 40 minutes. Neither the SQL nor the indexes have changed. The only thing that's happened is that last night 12 million historical rows were loaded into orders.

  1. State the most likely hypothesis and explain the mechanism.
  2. Say what you'd check, with the specific query or command.
  3. Propose the immediate solution and the preventive measure.

Solutions

Solution 1

-- a) Sargable: a single range over the bare column.
SELECT id, customer_id, order_date, status
FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

-- b) The filter uses no aggregate: it goes in the WHERE, before grouping
SELECT c.country, COUNT(*) AS customers FROM customers AS c WHERE c.country <> 'Spain' GROUP BY c.country;

-- c) An existence question: EXISTS instead of two JOINs and a DISTINCT
SELECT c.id, c.name FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

-- d) Keyset instead of OFFSET
SELECT id, name FROM products WHERE id > 15 ORDER BY id LIMIT 5;

In a) two things are corrected: the SELECT * and the two EXTRACT calls, which together prevent any index. In b), with three countries the gain is symbolic, but the habit is the right one and the WHERE can use an index. In c), the second JOIN with order_lines contributes no column to the result and multiplies each customer by their lines: the JOIN+DISTINCT would generate 47 rows to return 12 buying customers; the EXISTS returns 12 straight away and doesn't need order_lines at all. In d), with 20 rows it makes no difference; with 2 million, OFFSET 1000000 reads and discards a million rows.

Solution 2

1. It's an N+1, doubled up. The 61 queries are: 1 for the order list, 20 for each one's customer name, 20 for the sales rep's and 20 to count the lines. 1 + 20 + 20 + 20 = 61. Each takes less than a millisecond; what costs 4 seconds is the 61 round trips.

2. A single query. Two decisions that aren't about performance but about correctness, and that come from module 3: LEFT JOIN with employees, because ten of the twenty orders are web orders and have employee_id NULL (an INNER JOIN would make them disappear), with COALESCE to display "Web"; and LEFT JOIN with order_lines, so that an order with no lines counts 0 instead of vanishing.

SELECT o.id, o.order_date,
       c.name || ' ' || c.last_name AS customer,
       COALESCE(e.name || ' ' || e.last_name, 'Web') AS sales_rep,
       COUNT(ol.id) AS lines
FROM orders            AS o
JOIN customers         AS c  ON c.id = o.customer_id
LEFT JOIN employees    AS e  ON e.id = o.employee_id
LEFT JOIN order_lines  AS ol ON ol.order_id = o.id
GROUP BY o.id, o.order_date, c.name, c.last_name, e.name, e.last_name
ORDER BY o.order_date DESC
LIMIT 20;

3. Indexes on the foreign keys involved: orders(customer_id), orders(employee_id) —better partial, WHERE employee_id IS NOT NULL, because half are null— and above all order_lines(order_id), which is the one that avoids walking the whole table to count. And for the ORDER BY ... LIMIT 20, an index on orders(order_date): with it, the engine reads 20 entries from the end of the tree and stops.

Solution 3

1. The hypothesis: the statistics are stale. With 12 million new rows and no ANALYZE, pg_stats still describes the previous table. The planner believes orders is small, estimates that a filter will return dozens of rows when it will return hundreds of thousands, and chooses a Nested Loop —perfect for few rows, catastrophic for many— instead of a Hash Join. The query hasn't changed; the plan has.

2. What to check:

SELECT relname, n_live_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'orders';

If last_analyze predates the load and n_live_tup still shows the old count, the hypothesis is confirmed. The definitive proof is EXPLAIN ANALYZE over the report and comparing the estimated rows with the actual rows: a divergence of several orders of magnitude is this problem's exact signature (08-05).

3. The solution and the prevention. Immediate: ANALYZE orders;, which takes seconds and gives the good plan back. Preventive: include ANALYZE at the end of every bulk-load process, as one more step of the script, and don't count on autovacuum arriving in time: its thresholds are designed for day-to-day trickle, not for a load of 12 million rows in one go.

Conclusion

You now have the complete catalogue of what can be done without touching an index:

  • Eight writing rules: don't ask for surplus columns, filter with WHERE and not with HAVING, leave the column bare in the condition, back a LIMIT with an indexed ORDER BY, EXISTS instead of COUNT(*) > 0, UNION ALL by default, DISTINCT only when legitimate, and keyset instead of a large OFFSET.
  • They all boil down to one word: sargability. If the filtered column appears on its own, the index can be used; if it's wrapped in a function, a calculation or a conversion, it can't.
  • The planner estimates from the statistics, and when they go stale it chooses bad plans with correct data. ANALYZE after every bulk load is mandatory; default_statistics_target is raised per column; and extended statistics fix the independence assumption — in GreenStore, 6 × 5 = 30 possible combinations of category and supplier of which only 12 exist.
  • Often the problem isn't in the query: the N+1 turns one screen into 21 or 501 queries, each lightning fast and none of them suspicious, and it can't be seen from the slow-query ranking.
  • When that's no longer enough, there are further steps: materialized views (10-01), summary tables, caching, partitioning, read replicas and, as a last resort, sharding. None of them fixes a badly written query: it hides it.

Everything you've read in the module so far has the same drawback: they're rules. Good rules, but rules all the same, and in performance rules get things wrong. Is your query really using that index? Does EXISTS really become a semi-join, as 07-05 promised? Are the statistics really wrong? In lesson 08-05, Analyzing Query Performance, you stop assuming: EXPLAIN and EXPLAIN ANALYZE, how to read an execution plan node by node, what cost, rows, width, actual time and loops mean, the signal that gives stale statistics away, and a real demonstration —over a two-million-row table built for the occasion— of what an index changes.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project

The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved