PGM-H

A decision-support workload derived from TPC-H: the 8-table schema of parts, suppliers, customers, orders and line items, and 22 read-only analytical queries with random parameters. It is meant for a single client and read by the per-query response times; the headline figure is queries per hour.

pgmeter spec new pgm-h -o .
pgmeter -d benchmark init --spec pgm-h -s 1 -j 4
pgmeter -d benchmark run  --spec pgm-h -c 1 --once      # power test: each query once
pgmeter -d benchmark run  --spec pgm-h -c 4 --once      # throughput test: four streams

Scale and data

The scale unit is the TPC-H scale factor. Per unit:

region (5 rows) and nation (25) are loaded once. Scale factor 1 is about 2 GB with indexes and loads in a minute or two on a fast server; scale factor 10 is ten times that. At scale factor 1 there is a single unit, so the load itself runs on one connection; -j still parallelizes the keys, the indexes and the vacuum. Keys are integer and dates are date, as the TPC-H data types prescribe. Besides the primary keys and eight foreign keys, the spec creates the indexes an analytical PostgreSQL setup usually has: lineitem on ship date, order key, supplier key and (part key, supplier key); orders on customer key and order date; partsupp on supplier key; customer and supplier on nation key. There is no single-column index on lineitem’s part key: the (part key, supplier key) index starts with it and serves the same lookups at the same speed. The order-key index repeats the first column of the primary key, but it is narrower and the planner prefers it for the order-to-line-item joins (Q10 runs twice as fast with it). The indexes are listed in the spec and easy to remove if your comparison needs a narrower schema.

The queries

The 22 queries follow the TPC-H query definitions, with their parameters drawn at random within the ranges the specification gives (dates, regions, brands, discounts, quantities):

With --once each client runs the 22 queries once in its own random order, which is TPC-H’s stream model: one client is a power test, several clients are a throughput test. Without it the run is a continuous random stream with equal weights, for as long as -T says.

The queries live in scripts/pgmh_queries.lua as a dispatch table; each transaction’s script is one call into it, so changing a query or its parameter ranges is a Lua edit.

Reading a run

The spec reports a stream: the elapsed time, the geometric mean of the query times and where the time went, query by query, in the console and the HTML report, which adds a timeline per client. The elapsed time of a --once run is the stream’s result. The queries per hour headline is a plain rate over the queries executed; it is not the TPC-H QphH composite, which only an audited run may report.

Deviations from TPC-H

• Each client’s query order is a random permutation rather than the fixed permutations the specification assigns to its streams.
• No refresh functions: the data is read-only during a run.
• The data comes from the spec’s Lua loader, not from the official generator. The loader follows dbgen’s cardinalities, value ranges and distributions, including the ones the queries depend on: a third of the customers have no orders (Q13, Q22), the order status follows the lines shipped by mid-1995 (Q21), one part type in six is PROMO (Q14), and the comments carry the word pairs Q13 looks for and the Customer ... Complaints markers Q16 excludes, at dbgen’s rates. What differs: the text columns (addresses, comments, part names) are random words or characters rather than dbgen’s text grammar, the order keys are dense rather than dbgen’s sparse numbering (no query depends on it), and the random sequences are pgmeter’s, so the rows themselves are not dbgen’s rows and query results differ from a dbgen database of the same scale.
• Secondary indexes the specification leaves to the implementer, as listed above.