Performance Optimization — Learn Covering Indexes, LATERAL, MVs, and Partitioning in Practice

QUESTION 6

Covering Indexes — Avoid the table with Index Only Scan

INCLUDEIndex Only ScanVisibility MapIndex Design

Background ▼

A normal Index Scan has two stages: “identify the location of a row in the index → jump to the heap (the table itself) and read the values.” If every column to return is contained in the index, the heap visit itself can be skipped and PostgreSQL can choose Index Only Scan. In PostgreSQL 11+, the INCLUDE clause adds columns that are not search keys but should be stored alongside the index leaf entries.

-- ✓ Covering index: amount is included, not used as a search key
CREATE INDEX idx_orders_cover
  ON orders (customer_id, created_at) INCLUDE (amount);

-- ✗ Only (customer_id, created_at) → a heap visit is needed for amount on every row

Visibility Map: Index Only Scan can “truly avoid reading the heap” only when the target page is recorded as all-visible. If VACUUM has not run, Heap Fetches increase and the benefit shrinks, so checking Heap Fetches: 0 in EXPLAIN (ANALYZE) is the operational pass line.

Problem ▼

For orders (10 million rows), a dashboard runs the list of order dates and amounts for customer 101 from 2024-06-01 onward several hundred times per second. Define an index that can return the result with zero heap visits (Index Only Scan), and write the SELECT statement that uses it.

Source table (conceptual · 8 rows)

- orders (10 million actual rows / 8 example rows)

order_id	customer_id	created_at	amount	note (large column)
1	100	2024-05-01	1200	…
2	101	2024-06-10	800	…
3	101	2024-07-22	2000	…
4	101	2024-08-30	3000	…
5	102	2024-04-15	500	…
6	101	2024-09-05	1500	…
7	103	2024-07-01	700	…
8	101	2024-05-20	900	…

※ customer_id=101 and created_at >= '2024-06-01' produce 4 rows: id=2,3,4,6. The only selected columns are created_at and amount.

Expected Output ▼

Expected output (created_at ascending):

created_at	amount
2024-06-10	800
2024-07-22	2000
2024-08-30	3000
2024-09-05	1500

Model Answer ▼

CREATE INDEX idx_orders_cover          -- Covering index: equality → range search keys + output column in INCLUDE
  ON orders (customer_id, created_at)  -- Search keys (determine B-tree order)
  INCLUDE (amount);                    -- Included column (leaf-only payload, not part of ordering)

SELECT   created_at, amount          -- Select only columns inside the index (coverage achieved)
FROM     orders
WHERE    customer_id = 101           -- Equality: narrow with the leftmost key
  AND    created_at >= '2024-06-01'  -- Range: scan the second key’s range
ORDER BY created_at;                 -- Already in index order → no additional sort

/*
  Logical execution order (Index Only Scan plan):
  1. Binary-search the B-tree by the composite key        → reach the starting leaf
  2. Scan the leaves to the right                 → read keys + included columns in the index
  3. Check the Visibility Map                → no heap visit when all-visible
  4. Stop scanning when customer_id is past the target    → scan only the range
  5. Index order = created_at ascending  → no sort stage
  */

Explanation (table transitions & key points) ▼

CREATE INDEX idx_orders_cover
  ON orders (customer_id, created_at)
  INCLUDE (amount);

SELECT   created_at, amount
FROM     orders
WHERE    customer_id = 101
  AND    created_at >= '2024-06-01'
ORDER BY created_at;

LEGEND ▼

Rows read / loaded

1. Table and index structure — heap and B-tree leaves ▼

orders + idx (customer_id, created_at) INCLUDE (amount)The index leaves contain the search keys (customer_id, created_at) and amount included with INCLUDE. A large column such as note exists only in the heap. When all selected columns are contained in the index, the heap does not need to be read.

1 / 5

Leaf position	customer_id	created_at	amount (included)	Heap reference
#1	100	2024-05-01	1200	→ heap p.12
#2	101	2024-05-20	900	→ heap p.3
#3	101	2024-06-10	800	→ heap p.7
#4	101	2024-07-22	2000	→ heap p.18
#5	101	2024-08-30	3000	→ heap p.2
#6	101	2024-09-05	1500	→ heap p.9
#7	102	2024-04-15	500	→ heap p.5
#8	103	2024-07-01	700	→ heap p.14

Index leaves are already sorted by key order

LEARNING POINTS

Design “search keys” and “included columns” separately: Columns in INCLUDE do not participate in B-tree ordering; they live only as leaf payload, keeping internal nodes from growing. Adding non-search columns to the key side makes the entire index wider and worsens write cost and cache efficiency. The advanced rule is “columns for filtering are keys; columns for returning are INCLUDE.”

Index Only Scan has three conditions: (1) every column in SELECT, WHERE, and ORDER BY is inside the index, (2) the planner judges it cost-effective, and (3) the Visibility Map is all-visible. The third is operational: when Heap Fetches is large in EXPLAIN (ANALYZE), VACUUM or a review of autovacuum settings is the remedy.

SELECT * destroys coverage: The basic principle “specify columns” becomes an “assumption for making Index Only Scan possible” at the advanced level. Add even one column outside the index, especially a large note or JSON column, and heap visits return for every row. Treat API response design and index design as a pair.

ANTI-PATTERNS

Put everything into INCLUDE: Including large columns “just in case” makes the number of leaf pages grow and the index scan itself slower, while also increasing WAL on writes. Limit INCLUDE to output columns of high-frequency queries; for infrequent queries, letting them read the heap directly is cheaper overall.

Rely on Index Only Scan for a frequently updated table: Frequent UPDATE/DELETE operations clear the all-visible bit in the Visibility Map, so Heap Fetches can accumulate in production even when a benchmark is fast. For hot tables, “keep rows small and strengthen autovacuum” comes before designing around Index Only Scan.

Practical note: Covering indexes also buy read-replica capacity

The value of Index Only Scan is not limited to the speed of a single query. When heap visits disappear, only index pages need to be kept in shared_buffers, so more data fits in the same memory and cache hit coverage increases. In other words, a covering index for high-frequency reads directly improves cache efficiency and can save server instances. At the same time, an index is a “partial copy of the table,” so every additional index increases INSERT/UPDATE cost. Regularly inspect idx_scan in pg_stat_user_indexes and remove unused indexes; that inventory is part of covering-index design. In Q7, we will use an index to solve “Top-N per group” in JOIN form with LATERAL.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 7

LATERAL JOIN — Hit the index directly for “Top-N per row”

LATERALTop-N per groupIndex ScanCorrelated Subquery

Background ▼

LATERAL lets a subquery placed in a JOIN refer to rows on its left. It directly expresses “the latest N rows for each customer” as a Nested Loop that looks up the index once per customer. Unlike the window-function approach, which assigns ROW_NUMBER to all rows and then discards most of them, its strength is that scanning stops at LIMIT N for each group.

-- For each c, loop over the index to get only c’s latest 2 rows
FROM customers c
CROSS JOIN LATERAL (
  SELECT ... FROM orders o
  WHERE  o.customer_id = c.customer_id   -- The left-side c is visible here!
  ORDER BY o.created_at DESC LIMIT 2
) recent

Rule of thumb: If there are few customers and orders is huge, use LATERAL (number of groups × Index Scan); for a batch that processes every customer × every order at once, use a window function (one-pass full scan). Both rely on the same prerequisite index: (customer_id, created_at DESC).

Problem ▼

From customers (3 rows) and orders (10 million actual rows / 8 examples, indexed on (customer_id, created_at DESC)), retrieve the latest 2 orders for each customer. Return name, created_at, amount, ordered by name ascending and then created_at descending. Customers with no orders do not need to appear.

Source tables

- customers (3 rows · driving table)

customer_id	name
101	Alice
102	Bob
103	Carol

- orders (8 examples · indexed on (customer_id, created_at DESC))

order_id	customer_id	created_at	amount
1	101	2024-05-01	1200
2	101	2024-06-10	800
3	101	2024-07-22	2000
4	102	2024-08-30	3000
5	102	2024-04-15	500
6	103	2024-09-05	1500
7	103	2024-07-01	700
8	103	2024-05-20	900

Expected Output ▼

Expected output (name ascending → created_at descending):

name	created_at	amount
Alice	2024-07-22	2000
Alice	2024-06-10	800
Bob	2024-08-30	3000
Bob	2024-04-15	500
Carol	2024-09-05	1500
Carol	2024-07-01	700

Model Answer ▼

SELECT   c.name,
         recent.created_at,
         recent.amount
FROM     customers c                      -- Outer side = driving table (3 rows)
CROSS JOIN LATERAL (                      -- Subquery evaluated for each c row
  SELECT o.created_at, o.amount
  FROM   orders o
  WHERE  o.customer_id = c.customer_id    -- Refer to left-side c (the body of LATERAL)
  ORDER BY o.created_at DESC              -- Use the index order directly
  LIMIT  2                                -- Stop scanning after 2 rows per customer
) recent
ORDER BY c.name, recent.created_at DESC;  -- Output ordering

/*
  Logical execution order (Nested Loop + LATERAL):
  1. Scan customers one row at a time (driving table, 3 rows)
  2. Execute the LATERAL subquery for each c
  3. A customer with 0 orders disappears with CROSS JOIN LATERAL
  4. Sort 6 rows by name and created_at DESC
  */

Explanation (table transitions & key points) ▼

SELECT   c.name, recent.created_at, recent.amount
FROM     customers c
CROSS JOIN LATERAL (
  SELECT o.created_at, o.amount
  FROM   orders o
  WHERE  o.customer_id = c.customer_id
  ORDER BY o.created_at DESC
  LIMIT  2
) recent
ORDER BY c.name, recent.created_at DESC;

LEGEND ▼

Rows read / loaded

1. Read 2 tables — customers and indexed orders ▼

FROM customers c / idx (customer_id, created_at DESC)The orders index is sorted by created_at descending within each customer_id. This means “latest N orders per customer” can be retrieved by reading only N rows from the start of each index group.

1 / 5

src	customer_id	created_at (descending)	amount
idx	101	2024-07-22	2000
idx	101	2024-06-10	800
idx	101	2024-05-01	1200
idx	102	2024-08-30	3000
idx	102	2024-04-15	500
idx	103	2024-09-05	1500
idx	103	2024-07-01	700
idx	103	2024-05-20	900

customers = 3, orders = 8 (already index-ordered)

LEARNING POINTS

LATERAL makes a row-by-row LIMIT hit the index directly: An ordinary JOIN subquery cannot refer to left-side rows, but LATERAL allows WHERE o.customer_id = c.customer_id, making a plan that reads only N rows from the start of index (customer_id, created_at DESC) for each outer row. “Stop scanning within each group” is an optimization that LATERAL expresses directly.

Choose the approach from “number of target groups × rows per group”: Latest 2 for 100 customers → LATERAL (200 rows scanned). Latest 2 for every customer in 100 million rows → one-pass window function. You can decide by checking which is smaller: all table rows or group count × N, even before looking at EXPLAIN.

Match the index order, including DESC: ORDER BY created_at DESC LIMIT 2 inside LATERAL finishes without Sort because the index is (customer_id, created_at DESC) (or because an ascending index uses a Backward Scan) and matches the requested order. If the order does not match, the plan degenerates into reading and sorting every row in the group, eliminating LATERAL’s benefit.

ANTI-PATTERNS

Use LATERAL with a huge driving table: LATERAL is fundamentally a Nested Loop, so a million outer rows means a million subquery executions. It has the same structure as the N+1 pattern from Q8 in the basic set. If the outer side is large, filter it first with WHERE or switch to a window approach or pre-aggregation.

Confuse CROSS and LEFT: CROSS JOIN LATERAL silently removes an outer row when its subquery returns 0 rows. “A customer with no orders disappeared from the list” is the classic incident. To retain every customer, write LEFT JOIN LATERAL (...) ON true. Choose the join type from the requirement (whether to retain rows), not from performance.

Practical note: Read LATERAL as writing a for loop in SQL

A useful way to read LATERAL for the first time is as a “for loop flowing from left to right in FROM”. FROM A CROSS JOIN LATERAL (B using A) is simply for a in A: for b in B(a). Once this model is familiar, tasks that tend to become an application loop plus N+1 queries — attach the latest exchange rate to each trade, retrieve the latest value for each sensor, or get the top 3 comments for each article — can be folded into one index-friendly SQL query. In Q8, we will dig deeper into the one-pass window-function side and learn moving aggregation with an explicit frame.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 8

Window Frames — Calculate a moving average in one pass with ROWS BETWEEN

Window FunctionsROWS BETWEENMoving AverageROWS vs RANGE

Background ▼

A window-function frame clause determines which range of rows is included in the calculation as seen from each row. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means “the current row and the previous 2 rows,” or a 3-row moving average. Once the table is sorted, the moving aggregation is completed by a single pass, without a self-join.

AVG(sales) OVER (
  ORDER BY sales_date                          -- Frame ordering
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW    -- current + previous 2 rows = 3-row window
)

ROWS and RANGE differ: ROWS frames by physical row count, while RANGE frames by a value range (all equal values are included together). An even more important trap: the default when the frame is omitted is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With duplicate dates, that becomes a cumulative average including all peers with the same value and may diverge from the intent. Always specify ROWS for moving aggregates.

Problem ▼

From daily_sales, retrieve a list ordered by date with a 3-day moving average. Return sales_date, sales, ma3, rounding the moving average to 1 decimal place. For the first 2 days, average only the rows that exist.

Source table

- daily_sales (6 rows · index on sales_date)

sales_date	sales
2024-09-01	100
2024-09-02	200
2024-09-03	300
2024-09-04	600
2024-09-05	300
2024-09-06	900

Expected Output ▼

Expected output (sales_date ascending):

sales_date	sales	ma3
2024-09-01	100	100.0
2024-09-02	200	150.0
2024-09-03	300	200.0
2024-09-04	600	366.7
2024-09-05	300	400.0
2024-09-06	900	600.0

Model Answer ▼

SELECT sales_date,
       sales,
       ROUND(
         AVG(sales) OVER (
           ORDER BY sales_date                       -- Frame ordering
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Physical 3-row window (explicit ROWS is required)
         ), 1)                                       -- Round to 1 decimal place
       AS ma3
FROM   daily_sales
ORDER BY sales_date;                                 -- Output order (same as the window)

/*
  Logical execution order:
  1. Read daily_sales     → omit sorting with an Index Scan
  2. Window processing             → AVG over current + previous 2 rows
  3. Apply ROUND in SELECT  → finalize the column
  4. ORDER BY            → no additional sort because it matches the window order
  */

Explanation (table transitions & key points) ▼

SELECT sales_date, sales,
  ROUND(
    AVG(sales) OVER (
      ORDER BY sales_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 1) AS ma3
FROM   daily_sales
ORDER BY sales_date;

LEGEND ▼

Rows read / loaded

1. Read data — sorted by sales_date ascending ▼

FROM daily_sales (already sorted by index)Window processing assumes the rows are ordered by ORDER BY. When sales_date has an index, Index Scan supplies already-sorted data and the Sort node disappears.

1 / 5

sales_date	sales
2024-09-01	100
2024-09-02	200
2024-09-03	300
2024-09-04	600
2024-09-05	300
2024-09-06	900

6 rows (already sorted ascending)

LEARNING POINTS

The frame clause is the third component that defines the window shape: Window functions have a three-layer structure: PARTITION BY (group) → ORDER BY (order) → frame (range). Basic ROW_NUMBER did not use a frame, but the frame starts to matter when aggregate functions such as AVG / SUM / MAX are used as windows. Once you can state “from where to where, as seen from this row?”, moving averages, cumulative values, and recent maxima all use the same pattern.

Collapse self-join O(N × W) into one pass for moving aggregates: Writing the same result as JOIN daily_sales d2 ON d2.sales_date BETWEEN ... scans a window of width W for every row. A window frame runs as incremental calculation over a sorted stream, so the scan is exactly N rows. The difference is decisive at million-row scale.

An index on the ORDER BY column removes the sort: Window processing always needs input ordered by PARTITION BY + ORDER BY. If (sales_date) has an index, Index Scan supplies sorted input and Sort disappears from EXPLAIN. When a window aggregation over huge data is slow, suspect the preceding Sort before blaming the window function.

ANTI-PATTERNS

Assume an omitted frame means a moving average: AVG(x) OVER (ORDER BY d) is a cumulative average (and RANGE also includes peer rows with equal values), not a moving average. It is one of the hardest bugs to find in review; suspect it when a graph gradually becomes unnaturally smooth. Whenever you write aggregate + OVER + ORDER BY, point at the frame and confirm that it is present.

Assume “3 rows = 3 days” while ignoring missing dates: ROWS counts physical rows, so if 9/3 is missing, a “3-row window” spans 4 calendar days. If the requirement is exactly 3 calendar days, LEFT JOIN a calendar table and fill missing dates with 0, or use RANGE BETWEEN INTERVAL '2 days' PRECEDING when the database supports it. Always clarify in requirements whether the window is “rows” or “time.”

Practical note: Four practical patterns enabled by explicit frames

Once you can write frames freely, common analytical SQL patterns can all be handled in one pass. 1. Moving average: ROWS BETWEEN k PRECEDING AND CURRENT ROW. 2. Cumulative total: ROWS UNBOUNDED PRECEDING (explicit rather than relying on the RANGE default). 3. Share of total: SUM(x) OVER () (the full partition with no frame specification) for composition ratios. 4. Before/after comparison: LAG / LEAD are offset functions that need no frame. Combining these moves much of the preprocessing formerly left to BI tools into SQL, sharply reducing transfer volume and recalculation cost. Still, recalculating the same heavy aggregation every time can itself be wasteful. In Q9, we will learn the answer: materialized views.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 9

Materialized Views — Precompute and save heavy aggregations

MATERIALIZED VIEWREFRESH CONCURRENTLYPrecomputationFreshness Design

Background ▼

Running the same heavy aggregation (such as monthly sales) every time is wasteful. A materialized view (MV) saves query results as a physical table, so the read side only reads a small precomputed table. Freshness is controlled by the timing of REFRESH MATERIALIZED VIEW.

-- The aggregation runs once at definition time and the result is materialized
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS total
FROM orders GROUP BY 1;

-- Update: CONCURRENTLY does not block reads (a unique index is required)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

Difference from a normal VIEW: A VIEW is an “alias for a query” and aggregates the source tables on every read. An MV is a “snapshot of the result”: reads are fast, but freshness becomes older. Start MV design by deciding the acceptable age (for example, 1 hour) and refreshing at that interval.

Problem ▼

A monthly sales aggregation over orders (10 million rows) runs every minute in a dashboard and is the main cause of database load. Write: (1) a monthly-sales MV definition, (2) a unique index so it can be refreshed without stopping reads, and (3) the dashboard SELECT ordered by month ascending.

Source table (conceptual · 6 rows)

- orders (10 million actual rows / 6 example rows)

order_id	created_at	amount
1	2024-07-03	1200
2	2024-07-18	800
3	2024-08-02	2000
4	2024-08-21	3000
5	2024-09-05	1500
6	2024-09-28	700

Expected Output ▼

Expected output (month ascending):

month	total	order_count
2024-07-01	2000	2
2024-08-01	5000	2
2024-09-01	2200	2

Model Answer ▼

CREATE MATERIALIZED VIEW mv_monthly_sales AS                          -- (1) Materialize the monthly aggregate: scan 10 million rows once at creation
SELECT   DATE_TRUNC('month', created_at)::date AS month,
         SUM(amount)   AS total,
         COUNT(*)      AS order_count
FROM     orders
GROUP BY 1;
CREATE UNIQUE INDEX ux_mv_monthly_sales ON mv_monthly_sales (month);  -- (2) Prerequisite for CONCURRENTLY: unique index on the MV

-- (3) The dashboard reads only these 3 rows (does not touch the original 10 million)
SELECT   month, total, order_count
FROM     mv_monthly_sales
ORDER BY month;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;  -- Refresh freshness (run periodically via cron, etc. / does not block reads)

/*
  Logical execution order:
  [Only at creation / REFRESH time]
  1. Scan 10 million orders and derive a month key with DATE_TRUNC('month')
  2. Aggregate SUM / COUNT with GROUP BY month → materialize the 3-row result on disk
  3. With CONCURRENTLY: build the new result in temporary space,
     then use unique index (month) to replace only changed rows (reads are not blocked)
  [When the dashboard reads, every minute]
  4. Read 3 rows from mv_monthly_sales → ORDER BY month
  → Result: 3 rows × 3 columns (zero aggregation cost at read time)
*/

Explanation (table transitions & key points) ▼

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT DATE_TRUNC('month', created_at)::date AS month,
       SUM(amount) AS total, COUNT(*) AS order_count
FROM   orders
GROUP BY 1;

CREATE UNIQUE INDEX ux_mv_monthly_sales
  ON mv_monthly_sales (month);

SELECT month, total, order_count
FROM   mv_monthly_sales
ORDER BY month;

REFRESH MATERIALIZED VIEW CONCURRENTLY
  mv_monthly_sales;

LEGEND ▼

Rows read / loaded

1. Original table — 10 million rows aggregated every minute ▼

FROM orders (full scan on every read without MV)Before the MV, every dashboard request each minute scanned 10 million rows + GROUP BY. The same answer was being recomputed over and over.

1 / 5

order_id	created_at	amount
1	2024-07-03	1200
2	2024-07-18	800
3	2024-08-02	2000
4	2024-08-21	3000
5	2024-09-05	1500
6	2024-09-28	700

10 million actual rows (6 examples, grouped by month)

LEARNING POINTS

An MV shifts when the computation cost is paid: It is a mechanism for paying the aggregation cost in one batch at REFRESH time instead of on every read. The criterion is simple: if “read frequency × aggregation cost ≫ refresh frequency × REFRESH cost”, an MV wins. A dashboard read every minute with one-hour freshness is a typical winning pattern.

CONCURRENTLY requires a unique index: Non-blocking refresh compares old and new results row by row and replaces differences, so an index that uniquely identifies each row is required. Forgetting it makes REFRESH ... CONCURRENTLY fail in production. Remember: creating an MV and adding its unique index are one package.

MVs can have indexes too: An MV is a physical table, so you can add a normal index for read patterns such as WHERE month >= .... “Huge fact table → aggregate into MV → index the MV” is one of the most reproducible remedies for slow BI queries.

ANTI-PATTERNS

Materialize without deciding the freshness requirement: Complaints that “the amount is not real-time after moving to an MV” are a requirements-definition failure, not a technical failure. Agree with the business on how old the data may be before introduction and show “Last updated: hh:mm” on the screen. Numbers that must be real-time belong to incremental aggregation (triggers / streams), not an MV.

Create a long REFRESH chain or schedule everything at the same time: Stacking MVs on top of MVs creates REFRESH order dependencies, so one delay propagates. Refreshing every MV at the same time also creates an I/O spike that can take down the main queries. Draw the dependencies and stagger the REFRESH schedules as basic operations practice.

Practical note: Choosing among VIEW, MV, and an aggregate table

There are three similar tools. VIEW = share logic (no performance gain, always current). MV = declarative precomputation (full re-aggregation with REFRESH, simple to operate). Hand-built aggregate table = incremental updates through triggers or batches (fastest and freshest, but implementation and consistency management are heavy). When unsure, the least-accident-prone rollout is “start with VIEW, move to MV if slow, and move to incremental aggregation if MV REFRESH is too heavy.” The main reason REFRESH is heavy is full re-aggregation, so physically partitioning the source table by time can drastically reduce scanning. That is the next topic, Q10: partitioning.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 10

Partition Pruning — Use WHERE to reduce the partitions read

PARTITION BY RANGEPruningPartition KeyFunction-on-key trap

Background ▼

Table partitioning physically divides a huge table into multiple child tables using a partition key, often a date. When a WHERE clause identifies a key range, the planner excludes irrelevant partitions in the plan itself (pruning). An index “narrows the rows,” while pruning “narrows the tables to read,” making it a higher-level first cut.

CREATE TABLE orders (
  order_id bigint, created_at date, amount int, ...
) PARTITION BY RANGE (created_at);            -- Partition key

CREATE TABLE orders_2024_08 PARTITION OF orders
  FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');  -- Monthly partition

When pruning works: WHERE must use a plain comparison (=, <, >, BETWEEN) against the partition key. Wrapping the key in a function, such as DATE_TRUNC(created_at), prevents the range from being identified and scans every partition (the Sargable principle applies directly).

Problem ▼

From monthly RANGE-partitioned orders (12 partitions × approximately 1 million rows each), retrieve the August 2024 sales total and count. Use a WHERE clause that enables pruning, with a half-open range using >= / <. Return total, order_count.

Partition structure (conceptual)

- orders (parent) = a set of 12 child partitions

Child table	Range (FROM → TO)	Rows
orders_2024_07	07-01 → 08-01	about 1 million
orders_2024_08	08-01 → 09-01	about 1 million
orders_2024_09	09-01 → 10-01	about 1 million
… (other 9 partitions)	…	…

- inside orders_2024_08 (3 example rows)

order_id	created_at	amount
3	2024-08-02	2000
4	2024-08-21	3000
9	2024-08-30	1000

Expected Output ▼

Expected output (1 row):

total	order_count
6000	3

Model Answer ▼

SELECT SUM(amount) AS total,
       COUNT(*)    AS order_count
FROM   orders                      -- Write normally against the parent table
WHERE  created_at >= '2024-08-01'  -- Leave the key column bare (do not wrap it in a function)
  AND  created_at <  '2024-09-01';    -- Half-open interval: no month-end or time-of-day omissions

/*
  Logical execution order:
  1. Compare the WHERE range with partition definitions  → keep only matching partitions (Pruning)
  2. Scan only matching partitions        → remove all other partitions from the plan
  3. Aggregate SUM / COUNT   → produce one summary row
  */

Explanation (table transitions & key points) ▼

SELECT SUM(amount) AS total,
       COUNT(*) AS order_count
FROM   orders
WHERE  created_at >= '2024-08-01'
  AND  created_at <  '2024-09-01';

LEGEND ▼

Rows read / loaded

1. Partition structure — the parent is a bundle of partitions ▼

orders = 12 partitions BY RANGE (created_at)The parent table orders has no physical rows; it is a set of 12 monthly child tables. Each partition stores the FOR VALUES FROM ... TO ... definition range as metadata, which the planner uses to decide what to read.

1 / 5

Child table	Defined range	Rows
orders_2024_06	[06-01, 07-01)	about 1 million
orders_2024_07	[07-01, 08-01)	about 1 million
orders_2024_08	[08-01, 09-01)	about 1 million
orders_2024_09	[09-01, 10-01)	about 1 million
… (other 8 partitions)	…	…

About 12 million rows total / 12 partitions

LEARNING POINTS

Pruning is “zero-stage filtering” before indexes: The hierarchy is (0) discard partitions with Pruning → (1) discard rows with an index → (2) discard rows with a filter. Discarding at a higher level is cheaper; Pruning is strongest because it removes entire tables from the plan, not merely I/O. For huge time-series tables (logs, orders, events), consider date partitioning as the first key design.

The half-open interval >= AND < is the correct date-range form: BETWEEN '08-01' AND '08-31' misses timestamps after midnight on 08-31, and hard-coding a month-end day creates bugs from leap years and varying month lengths. >= month start AND < next month start is independent of type and month length, and is perfectly compatible with pruning — a universal form.

Operational work becomes dramatically lighter: “Delete data older than 13 months” normally means DELETE (huge WAL + VACUUM pain), but with partitions it becomes one metadata operation: DETACH PARTITION + DROP. Backups, ANALYZE, and REINDEX can also run per partition. This is why partitioning’s real value is often said to be operational performance rather than search performance.

ANTI-PATTERNS

Partition while most queries do not filter on the partition key: If the workload mostly uses WHERE customer_id = ? and you partition by date, every query hits every partition (and searches every partition’s index), which can be slower than an unpartitioned table. The decision criterion is whether most major queries can narrow on the key.

Forget to create the next partition and let INSERTs fail: A RANGE partitioned table errors on INSERT when no matching partition exists. A DEFAULT partition prevents the error, but if it grows large it undermines pruning. Automating next-month partition creation and monitoring is one package with partition operations.

Practical note: Advanced-set summary — 5 higher-level tools behind fast SELECTs

Q6–Q10 can be summarized as “reduce the amount read at a higher level.” 1. Covering INDEX: remove heap visits (Index Only Scan). 2. LATERAL: stop scanning within each group (hit the index directly for Top-N). 3. Window frames: collapse self-joins into one pass (moving aggregation). 4. Materialized views: move computation out of read time (precomputation + freshness design). 5. Partitioning: do not read tables at all (Pruning + operational performance). The basic set’s 10 principles were about “how to write queries”; the advanced set is about designing data structures and where to place cost. If you can read Heap Fetches, Rows Removed, and the number of pruned partitions (or its equivalent) in EXPLAIN (ANALYZE, BUFFERS), you can apply these 5 tools to most practical performance problems.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.