Difficulty: Advanced Intermediate | Prerequisites: SQL Querying on TPC-H Data study notes (Part 1), comfort with JOINs, GROUP BY, HAVING, ORDER BY. Familiarity with PostgreSQL and pgAdmin is helpful.
Once you can write SQL queries against the TPC-H schema, the next question is performance: why do some queries take seconds and others take minutes? This unit introduces two core ideas. First, the standard TPC-H benchmark queries (Q1 and Q3) that combine heavy aggregation and multi-table joins, giving you a feel for real analytical workloads. Second, table partitioning in PostgreSQL, a physical design technique that splits a large table into smaller pieces so the query planner can skip irrelevant data entirely. You will use EXPLAIN (ANALYZE, BUFFERS) throughout to see exactly what PostgreSQL does under the hood, comparing unpartitioned and partitioned tables side by side.
TPC-H Q1 scans millions of line items and aggregates pricing metrics; Q3 joins three tables and sorts by revenue. Both are slow on unpartitioned tables. Range partitioning the orders table by date (and list partitioning customer by market segment) lets PostgreSQL skip irrelevant partitions, cutting I/O and execution time. EXPLAIN (ANALYZE, BUFFERS) is how you prove it.
ANALYZE (PostgreSQL command)
A command that collects table statistics (row counts, histograms, most common values) so the query planner can choose efficient execution plans.
Think of it as telling PostgreSQL to take inventory of its data so it can plan queries intelligently rather than guessing.
work_mem
A PostgreSQL session parameter that controls how much memory a single sort or hash operation can use before spilling to disk.
In simple terms, increasing work_mem lets PostgreSQL do more work in memory, which avoids slower temporary files on disk.
EXPLAIN ANALYZE
A PostgreSQL command that runs a query and reports the actual execution plan, including real timings, row counts and (with the BUFFERS option) I/O statistics.
Think of it as an X-ray of your query: it shows you exactly what PostgreSQL did, not just what it planned to do.
BUFFERS (in EXPLAIN)
When added to EXPLAIN ANALYZE, the BUFFERS option reports how many 8 KB pages were read from the buffer cache ("shared hit") versus from disk ("shared read").
In simple terms, fewer buffers read means less I/O, which means faster queries.
Sequential scan (Seq Scan)
An access method where PostgreSQL reads every row of a table from start to finish. This is the default when there is no useful index or partition pruning.
Think of it as reading a book cover to cover rather than looking something up in the index.
Partition pruning
The optimiser's ability to skip partitions that cannot contain relevant rows, based on the query's WHERE clause and the partition boundaries.
In simple terms, if your query asks for 1993 data and each year has its own partition, PostgreSQL reads only the 1993 partition and ignores the rest.
Range partitioning
A partitioning strategy that splits a table into partitions based on non-overlapping ranges of a column's values (typically dates).
Think of it as dividing a filing cabinet by year: all 1992 orders in one drawer, all 1993 orders in the next.
List partitioning
A partitioning strategy that assigns rows to partitions based on a discrete set of values (e.g., market segments: BUILDING, AUTOMOBILE, MACHINERY).
In simple terms, each partition holds all rows with a specific value in the partition key column.
Horizontal fragmentation
The database theory term for splitting a table's rows across multiple physical storage units (partitions, shards or fragments) while keeping the same schema.
Think of it as the formal name for what PostgreSQL calls partitioning: same columns, different subsets of rows.
Partition key
The column (or columns) used to determine which partition a row belongs to.
In simple terms, the partition key is the column PostgreSQL looks at to decide where to put each row.
Before running benchmark queries, three setup steps ensure fair and reproducible timings.
Run ANALYZE; after bulk-loading the TPC-H data. This updates row counts, histograms and most-common-value lists so the planner picks sensible join orders and access paths.
SET work_mem = '256MB'; for the session. Many TPC-H queries involve large GROUP BYs, ORDER BYs and hash joins. A higher work_mem keeps these in memory instead of spilling to temporary files on disk, making timings cleaner and more consistent.
Run a quick row count on the largest tables to confirm the dataset loaded correctly and to get a feel for scale:
SELECT 'orders' AS table_name, COUNT(*) AS rows FROM orders
UNION ALL
SELECT 'lineitem', COUNT(*) FROM lineitem;At scale factor 1, orders has about 1.5 million rows and lineitem about 6 million. These are the tables where query performance matters most.
Q1 is the classic TPC-H query. It scans a very large portion of the lineitem table, filters by ship date and produces aggregate pricing metrics.
For all line items shipped on or before 1998-09-02 (90 days before 1998-12-01), group by return flag (l_returnflag) and line status (l_linestatus) and report:
total quantity
total base price (sum of l_extendedprice)
total discounted price
total charge (discounted price with tax)
average quantity, price and discount
count of line items
Sort the output by return flag, then line status.
sum_base_price = SUM(l_extendedprice)
sum_disc_price = SUM(l_extendedprice * (1 - l_discount))
sum_charge = SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax))
Averages use AVG over the same columns.
SELECT
l_returnflag, l_linestatus,
SUM(l_quantity) AS sum_qty,
SUM(l_extendedprice) AS sum_base_price,
SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
AVG(l_quantity) AS avg_qty,
AVG(l_extendedprice) AS avg_price,
AVG(l_discount) AS avg_disc,
COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90 days'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;Prefix the query with EXPLAIN (ANALYZE, BUFFERS) to see what PostgreSQL actually does. Key things to look for:
Seq Scan on lineitem: Q1 typically triggers a full sequential scan because the date filter keeps most rows (roughly 98% of lineitem).
Actual Rows: millions of rows processed.
BUFFERS shared hit/read: how many 8 KB pages came from the cache versus disk.
Elapsed time: shown at the bottom of the plan and in pgAdmin's Messages tab.
Q3 is a multi-table join query. It finds the top-10 unshipped orders by revenue for customers in the BUILDING market segment.
Join customer, orders and lineitem. Filter to customers in the BUILDING segment, orders placed before 1995-03-15 and line items shipped after 1995-03-15. Group by order key, order date and ship priority. Sort by revenue descending, then by order date. Return the top 10.
SELECT
o_orderkey,
SUM(l_extendedprice * (1 - l_discount)) AS revenue,
o_orderdate,
o_shippriority
FROM customer
JOIN orders ON c_custkey = o_custkey
JOIN lineitem ON l_orderkey = o_orderkey
WHERE c_mktsegment = 'BUILDING'
AND o_orderdate < DATE '1995-03-15'
AND l_shipdate > DATE '1995-03-15'
GROUP BY o_orderkey, o_orderdate, o_shippriority
ORDER BY revenue DESC, o_orderdate
LIMIT 10;Q3 joins three tables (customer, orders, lineitem), two of which are very large. PostgreSQL typically uses hash joins and must scan substantial portions of each table. The GROUP BY and ORDER BY add sorting overhead on top.
Running EXPLAIN (ANALYZE, BUFFERS) on Q3 shows the join strategy (usually Hash Join), the number of rows flowing through each join, and the total buffers consumed. This query is the motivation for partitioning: if you partition customer by market segment, the BUILDING filter prunes all other segments before the join even starts.
This task creates a range-partitioned copy of the orders table, split by calendar year on o_orderdate.
CREATE TABLE orders_partitioned (
o_orderkey INT,
o_custkey INT,
o_orderstatus CHAR(1),
o_totalprice NUMERIC,
o_orderdate DATE NOT NULL,
o_orderpriority CHAR(15),
o_clerk CHAR(15),
o_shippriority INT,
o_comment VARCHAR(79)
) PARTITION BY RANGE (o_orderdate);The PARTITION BY RANGE (o_orderdate) clause tells PostgreSQL this is a partitioned table and the partition key is the order date. No data is stored in the parent itself; each row goes into a child partition.
A PL/pgSQL block reads the min and max years from the existing orders table and creates one partition per year:
DO $$
DECLARE
y_start int; y_end int; y int;
BEGIN
SELECT EXTRACT(YEAR FROM MIN(o_orderdate))::int,
EXTRACT(YEAR FROM MAX(o_orderdate))::int
INTO y_start, y_end
FROM orders;
FOR y IN y_start..y_end LOOP
EXECUTE format(
'CREATE TABLE IF NOT EXISTS orders_%s
PARTITION OF orders_partitioned
FOR VALUES FROM (''%s-01-01'') TO (''%s-01-01'');',
y, y, y+1
);
END LOOP;
END$$;Each partition covers one calendar year. The FOR VALUES FROM ... TO range is inclusive on the lower bound and exclusive on the upper bound (e.g., orders_1993 holds dates from 1993-01-01 up to but not including 1994-01-01).
INSERT INTO orders_partitioned (
o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate,
o_orderpriority, o_clerk, o_shippriority, o_comment
)
SELECT
o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate,
o_orderpriority, o_clerk, o_shippriority, o_comment
FROM orders;
ANALYZE orders;
ANALYZE orders_partitioned;Running ANALYZE on both tables ensures the planner has accurate statistics for a fair comparison.
SELECT '1992' AS year, COUNT(*) AS rows
FROM orders_partitioned
WHERE o_orderdate >= DATE '1992-01-01' AND o_orderdate < DATE '1993-01-01'
UNION ALL
SELECT '1993', COUNT(*)
FROM orders_partitioned
WHERE o_orderdate >= DATE '1993-01-01' AND o_orderdate < DATE '1994-01-01';This confirms that each year's partition has the expected number of rows.
The point of partitioning is measurable: you run the same date-filtered query on the unpartitioned table and the partitioned table, then compare the EXPLAIN ANALYZE output.
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders
WHERE o_orderdate >= DATE '1993-01-01'
AND o_orderdate < DATE '1994-01-01';PostgreSQL must scan the entire orders table (Seq Scan), read all buffers and filter out every row outside 1993. The "Rows Removed by Filter" count will be high.
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders_partitioned
WHERE o_orderdate >= DATE '1993-01-01'
AND o_orderdate < DATE '1994-01-01';With partition pruning enabled (it is on by default: SET enable_partition_pruning = on;), PostgreSQL scans only the orders_1993 partition. The plan will show a scan of that single partition with dramatically fewer buffers and rows.
Scan node targets: the unpartitioned plan shows one Seq Scan on orders; the partitioned plan shows a Seq Scan on orders_1993 only.
Actual Rows: far lower in the partitioned case because irrelevant years are never read.
Rows Removed by Filter: nearly zero in the partitioned case (all rows in the partition match the date range).
BUFFERS shared hit/read: substantially lower in the partitioned case, confirming less I/O.
Execution Time: lower in the partitioned case.
When the date range spans two partitions (e.g., 1992-06-01 to 1994-01-01), PostgreSQL prunes the rest but scans both orders_1992 and orders_1993. The plan shows an Append node with two child scans. This is still faster than a full table scan.
Partitioning reduces work by skipping irrelevant fragments. Fewer I/O buffers read means lower execution time. It works best when the query's WHERE clause aligns with the partition key (date ranges on a date-partitioned table, segment filters on a segment-partitioned table).
The caveat: over-partitioning (too many tiny partitions) can add planning overhead because the planner must evaluate each partition's boundaries.
Task 02 applies list partitioning to the customer table, splitting by c_mktsegment (AUTOMOBILE, BUILDING, FURNITURE, HOUSEHOLD, MACHINERY).
The approach mirrors Task 01:
Create a parent table with PARTITION BY LIST (c_mktsegment).
Create one child partition per segment: CREATE TABLE customer_building PARTITION OF customer_partitioned FOR VALUES IN ('BUILDING'); and so on for each segment.
Load data from the original customer table and run ANALYZE on both.
Compare EXPLAIN (ANALYZE, BUFFERS) for a query filtering on c_mktsegment = 'BUILDING' against the unpartitioned and partitioned tables.
The practical payoff appears in Q3: when customer is list-partitioned by segment, the join to orders and lineitem starts with a much smaller input (only BUILDING customers) because partition pruning discards the other four segments before the join runs.
List partitioning suits columns with a small, fixed set of discrete values. It would be a poor choice for a column with thousands of distinct values because you would end up with thousands of partitions.
Students often think partitioning always makes queries faster. It does not. A query that touches all partitions (no selective predicate on the partition key) can be slower than a single-table scan because of the added Append overhead.
Students confuse EXPLAIN with EXPLAIN ANALYZE. Plain EXPLAIN shows the planner's estimated plan without running the query. EXPLAIN ANALYZE actually runs the query and shows real timings and row counts. Always use EXPLAIN ANALYZE for benchmarking.
Students assume partition pruning happens automatically for any filter. It only works when the WHERE clause uses the partition key column directly. A function wrapped around the column (e.g., WHERE EXTRACT(YEAR FROM o_orderdate) = 1993) can prevent pruning.
Students forget to run ANALYZE after loading data into a partitioned table. Without up-to-date statistics, the planner may choose poor plans, making the comparison unfair.
⚠️ Know the Q1 pricing formulas cold: sum_base_price, sum_disc_price, sum_charge. These are classic exam material.
⚠️ Be able to read an EXPLAIN ANALYZE plan and identify: Seq Scan vs Index Scan, Actual Rows, Rows Removed by Filter, BUFFERS shared hit/read, and total Execution Time.
⚠️ Understand the syntax for creating a range-partitioned table and its child partitions with FOR VALUES FROM ... TO. The FROM bound is inclusive; the TO bound is exclusive.
⚠️ Be prepared to explain why partitioning helps for selective queries (partition pruning skips irrelevant data) and when it does not help (queries spanning all partitions, or predicates that do not use the partition key).
⚠️ Know the difference between range and list partitioning and when to use each: ranges for continuous values (dates), lists for discrete categories (market segments).
True or false: EXPLAIN ANALYZE shows estimated costs without running the query. (False. EXPLAIN ANALYZE actually executes the query and reports real timings.)
Fill in the blank: The TPC-H Q1 formula for total charge is SUM(l_extendedprice * (1 - l_discount) * (1 + ______)). (l_tax)
True or false: Range partitioning on o_orderdate with yearly partitions means each partition's upper bound is inclusive. (False. The TO bound is exclusive.)
Fill in the blank: The PostgreSQL command that collects table statistics for the query planner is called ______. (ANALYZE)
True or false: Partitioning always reduces query execution time. (False. Queries that touch all partitions may be slower due to Append overhead.)
Q: In TPC-H Q1, why does PostgreSQL use a sequential scan on lineitem even though there is a date filter?
A: The date filter (shipped on or before 1998-09-02) keeps roughly 98% of all rows. An index scan would be slower than a sequential scan for such a non-selective predicate because of the random I/O overhead.
Q: Write the SQL to create a range-partitioned table for orders, partitioned by o_orderdate.
A: CREATE TABLE orders_partitioned ( ... columns ... ) PARTITION BY RANGE (o_orderdate); followed by child partitions created with FOR VALUES FROM ('YYYY-01-01') TO ('YYYY+1-01-01') for each year.
Q: What is the difference between range and list partitioning? Give an example of when you would use each.
A: Range partitioning splits on continuous value ranges (e.g., orders by date). List partitioning splits on discrete values (e.g., customers by market segment). Use range when values form a continuum; use list when there is a small, fixed set of categories.
Q: You run EXPLAIN (ANALYZE, BUFFERS) on a date-filtered COUNT query against both the unpartitioned orders table and the partitioned copy. What three metrics should differ, and in which direction?
A: Execution time (lower for partitioned), BUFFERS shared hit/read (fewer for partitioned), and Actual Rows scanned (fewer for partitioned, because partition pruning skips irrelevant years).
Q: Why must you run ANALYZE after loading data into a partitioned table?
A: Without ANALYZE, the planner has no statistics for the new partitions and may choose poor plans (wrong join order, wrong access method), making the before/after comparison unreliable.
Partitioning is PostgreSQL's implementation of horizontal fragmentation, a concept you will encounter again when studying distributed databases: each partition could live on a different node. The EXPLAIN ANALYZE skills from this unit carry directly into index tuning, materialised views and query plan analysis in later coursework.
The TPC-H benchmark queries (Q1 through Q22) are a standard reference across database courses and industry. Understanding Q1 and Q3 deeply gives you a template for reading any complex analytical query.
TPC-H, CSE 512, distributed database systems, ASU, PostgreSQL, pgAdmin, ANALYZE, work_mem, EXPLAIN, EXPLAIN ANALYZE, BUFFERS, sequential scan, Seq Scan, index scan, query plan, execution plan, partition pruning, range partitioning, list partitioning, horizontal fragmentation, partition key, PARTITION BY RANGE, PARTITION BY LIST, FOR VALUES FROM TO, FOR VALUES IN, Append node, Q1 pricing summary, Q3 shipping priority, l_returnflag, l_linestatus, sum_disc_price, sum_charge, o_orderdate, c_mktsegment, BUILDING, lineitem, orders, customer, hash join, sort spill, benchmark, performance tuning