16.4 Tuning Patterns That Matter — The Complete SQL Course

Most slow SQL is not rescued by exotic tricks. It improves when the query gives PostgreSQL a clean path to the rows it needs, moves less data, and avoids asking the engine to walk past work it will throw away.

You now know what the optimizer does, what indexes give it, and how to read a plan. This lesson is the short list of tuning traps that keep showing up in real code: index killers, needless heap fetches, deep pagination, row-by-row work, and denormalization chosen before measurement.

Keep indexed columns bare

An index is sorted by the expression it stores. big_orders_customer_idx is sorted by the integer value of customer_id, so the clean predicate can use it:

SELECT count(*) AS total_orders,
       count(*) FILTER (WHERE customer_id = 42) AS customer_42,
       count(*) FILTER (WHERE id > 50000) AS after_id_50000
FROM big_orders;

 total_orders | customer_42 | after_id_50000
--------------+-------------+----------------
       100000 |         100 |          50000
(1 row)

EXPLAIN (COSTS OFF)
SELECT id, customer_id
FROM big_orders
WHERE customer_id = 42;
 
EXPLAIN (COSTS OFF)
SELECT id, customer_id
FROM big_orders
WHERE customer_id + 0 = 42;
 
EXPLAIN (COSTS OFF)
SELECT id, customer_id
FROM big_orders
WHERE CAST(customer_id AS text) = '42';

                     QUERY PLAN
----------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on big_orders_customer_idx
         Index Cond: (customer_id = 42)
(4 rows)
             QUERY PLAN
------------------------------------
 Seq Scan on big_orders
   Filter: ((customer_id + 0) = 42)
(2 rows)
                  QUERY PLAN
----------------------------------------------
 Seq Scan on big_orders
   Filter: ((customer_id)::text = '42'::text)
(2 rows)

Same answer shape, different access path. The indexed column is no longer recognizable as "search the sorted integer customer_id values" once the query wraps it in arithmetic or a cast. This is the practical meaning of SARGability: write predicates so the indexed value is available as a search argument. Implicit casts can cause the same hazard when PostgreSQL converts the indexed column side; use typed parameters and typed literals so conversions stay on the value side.

Match expression indexes, and avoid leading wildcards

The same rule bites text cleanup. A plain index on email helps exact email lookup, but not lower(email) unless you create an expression index on that exact expression:

CREATE TABLE order_contacts AS
SELECT id,
       CAST('customer' || customer_id || '@example.com' AS text) AS email
FROM big_orders;
CREATE INDEX order_contacts_email_idx
ON order_contacts (email);
ANALYZE order_contacts;
 
EXPLAIN (COSTS OFF)
SELECT id, email
FROM order_contacts
WHERE email = 'customer42@example.com';
 
EXPLAIN (COSTS OFF)
SELECT id, email
FROM order_contacts
WHERE lower(email) = 'customer42@example.com';
 
CREATE INDEX order_contacts_lower_email_idx
ON order_contacts (lower(email));
ANALYZE order_contacts;
 
EXPLAIN (COSTS OFF)
SELECT id, email
FROM order_contacts
WHERE lower(email) = 'customer42@example.com';
 
EXPLAIN (COSTS OFF)
SELECT id, email
FROM order_contacts
WHERE email LIKE '%42@example.com';
 
EXPLAIN (COSTS OFF)
SELECT id, email
FROM order_contacts
WHERE email LIKE 'customer42%';

CREATE TABLE
CREATE INDEX
ANALYZE
                          QUERY PLAN
--------------------------------------------------------------
 Bitmap Heap Scan on order_contacts
   Recheck Cond: (email = 'customer42@example.com'::text)
   ->  Bitmap Index Scan on order_contacts_email_idx
         Index Cond: (email = 'customer42@example.com'::text)
(4 rows)
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on order_contacts
   Filter: (lower(email) = 'customer42@example.com'::text)
(2 rows)
CREATE INDEX
ANALYZE
                             QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on order_contacts
   Recheck Cond: (lower(email) = 'customer42@example.com'::text)
   ->  Bitmap Index Scan on order_contacts_lower_email_idx
         Index Cond: (lower(email) = 'customer42@example.com'::text)
(4 rows)
                  QUERY PLAN
----------------------------------------------
 Seq Scan on order_contacts
   Filter: (email ~~ '%42@example.com'::text)
(2 rows)
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on order_contacts
   Filter: (email ~~ 'customer42%'::text)
   ->  Bitmap Index Scan on order_contacts_email_idx
         Index Cond: ((email >= 'customer42'::text) AND (email < 'customer43'::text))
(4 rows)

Two patterns in one place:

lower(email) needs either normalized stored email plus a bare-column predicate, or an expression index on lower(email).
LIKE '%42@example.com' starts with a wildcard, so there is no left edge to jump to in the phone book. LIKE 'customer42%' has a fixed prefix, so the B-tree can narrow to a range before filtering.

For fuzzy search, substring search, stemming, typo tolerance, and relevance ranking, do not keep torturing a B-tree. Use the right search tool: full-text search, trigram indexes, or a dedicated search engine depending on the product need.

`SELECT *` can defeat a covering index

In 16.2 you saw covering indexes and index-only scans. The query has to cooperate. If the selected columns are all in the covering index, PostgreSQL can stay in the index:

CREATE INDEX big_orders_customer_cover_idx
ON big_orders (customer_id) INCLUDE (placed_on, amount);
VACUUM big_orders;
ANALYZE big_orders;
 
EXPLAIN (COSTS OFF)
SELECT customer_id, placed_on, amount
FROM big_orders
WHERE customer_id = 42;
 
EXPLAIN (COSTS OFF)
SELECT *
FROM big_orders
WHERE customer_id = 42;

CREATE INDEX
VACUUM
ANALYZE
                            QUERY PLAN
-------------------------------------------------------------------
 Index Only Scan using big_orders_customer_cover_idx on big_orders
   Index Cond: (customer_id = 42)
(2 rows)
                     QUERY PLAN
----------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on big_orders_customer_idx
         Index Cond: (customer_id = 42)
(4 rows)

The SELECT * version asks for id too, which is not covered by big_orders_customer_cover_idx. PostgreSQL has to fetch table rows. This is the Part IV version of the Part I habit: name the columns your code actually needs.

Deep OFFSET walks past work

OFFSET is convenient for page two. It is a bad shape for page thousands. The database still has to produce the ordered stream, skip the offset rows, then return the next page:

EXPLAIN (COSTS OFF)
SELECT id, customer_id, amount
FROM big_orders
ORDER BY id
LIMIT 20 OFFSET 50000;
 
EXPLAIN (COSTS OFF)
SELECT id, customer_id, amount
FROM big_orders
WHERE id > 50000
ORDER BY id
LIMIT 20;

                      QUERY PLAN
------------------------------------------------------
 Limit
   ->  Index Scan using big_orders_pkey on big_orders
(2 rows)
                      QUERY PLAN
------------------------------------------------------
 Limit
   ->  Index Scan using big_orders_pkey on big_orders
         Index Cond: (id > 50000)
(3 rows)

The keyset version gives the index a starting point: "after the last id I saw." Real cursor APIs usually encode a tuple, such as (created_at, id), so ties stay stable. The pattern is the same: use the ordered key to seek, not a deep count of rows to discard.

Batch work, then denormalize only after measuring

Performance bugs also come from shape outside one SELECT. Row-by-row application loops create repeated parse/plan/network/lock work: fetch one id, update one row, repeat. The structural fix is set-based work — one statement that names the whole target set — with the safe-write ritual from 8.2 when you are changing data: rehearse the target with SELECT, write once, verify.

The N+1 query problem is the read-side version: one list query fetches rows, then the app runs one more query per row to load related data. ORMs can hide this through lazy-loaded associations. The fixes are structural too: log the generated SQL, then join, preload, or rewrite as a set-based query. 18.3 gives the full ORM story.

Denormalization sits even later in the decision tree. Copying a value, storing a precomputed total, or maintaining a summary table can be the right move for a read path you have measured and cannot make cheap enough with indexes and query shape. It is also a maintenance contract. Name the source of truth, the refresh rule, and the acceptable staleness before you copy facts.

sql — playgroundlive

⌘/Ctrl + Enter to run

The pattern underneath all of this is simple: make the useful access path obvious, move only the columns you need, and measure before you add machinery.

Checkpoint

Answer all three to mark this lesson complete