PGM-DS

A decision-support workload derived from TPC-DS: the full 24-table snowflake schema of a retailer selling through stores, catalogs and the web, its foreign-key graph, and the 99 analytical queries of the specification. Like PGM-H it is meant to be run as query streams with --once and read by the per-query response times; the headline figure is queries per hour.

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

Scale and data

The scale unit is a scale factor. The dimensions that do not scale are loaded once; the rest grows with the scale factor.

Fixed dimensions: date_dim (every day from 1900 to 2100, about 73,000 rows), time_dim (86,400 seconds of a day), customer_demographics (1,920,800), household_demographics (7,200), catalog_page (11,718), promotion (300), reason (35), income_band (20), ship_mode (20), store (12), call_center (6), web_site (30), web_page (60), warehouse (5).

Scale factor 1 is about 2.4 GB with indexes and loads in a few minutes; start there. At scale factor 1 there is a single unit, so the fact tables load on one connection; -j still parallelizes the keys, the indexes and the vacuum. Primary keys on every table, the 106 foreign keys of the TPC-DS referential graph (skipped with --no-fkey), and secondary indexes chosen for the queries rather than one per foreign key: the sales tables’ sold-date keys, store_sales’ customer and store keys, store_returns’ customer and returned-date keys, inventory’s item key, customer’s address key, and the item attributes the queries filter on.

The queries

All 99 TPC-DS queries, numbered as in the specification, one transaction each with equal weights. The SQL follows the query templates, written for PostgreSQL, and the substitution parameters are drawn at random on every execution within the template ranges: years and dates within the loaded sales window, categories, colours, states, demographics and the other vocabularies the loader writes, and lists the specification takes from the data (zip codes, counties, cities, store states) sampled from the database once per client. The four multi-statement queries (14, 23, 24 and 39) run both of their parts.

They live in scripts/pgmds_queries.lua as a dispatch table, Q[1] to Q[99]; each transaction’s script is one call into it, so changing a query or its parameter ranges is a Lua edit. The query subjects are the specification’s: the description of each transaction in spec.yaml summarises it, and pgmeter spec list prints them.

Seven queries are restructured for PostgreSQL, with the same result rows. Q4, Q11 and Q74 share in the templates one year_total CTE over every channel and year that is then self-joined four or six times; PostgreSQL has no statistics for a materialized CTE and picked plans that ran for hours at scale factor 1, so the port aggregates each channel and year separately. Q1, Q6, Q30 and Q81 evaluate a correlated average subquery for every row in the templates, up to a minute each here; the port joins a per-group average instead. Everything else is the template’s shape.

With --once each client runs the 99 queries once in its own random order, which is TPC-DS’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. A few queries (q95, q14, q10, q4, q39, q22, q23, q67, q74, q11) take far longer than the rest on PostgreSQL; they count once per stream like the others.

The spec reports a stream: the elapsed time, the geometric mean of the query times and where the time went, query by query, with a timeline per client in the HTML report. In one single-stream run at scale factor 1 on a test machine, q95 and q14 together took more than a quarter of the time.

Deviations from TPC-DS

• Each client’s query order is a random permutation rather than the fixed permutations the specification assigns to its streams, and there is no data maintenance run.
• The data is generated by the spec’s Lua loaders with TPC-DS cardinalities and the vocabularies the queries filter on, not by the official generator; text columns are synthetic and the distributions are uniform where the generator’s are skewed.
• Q4, Q11, Q74, Q1, Q6, Q30 and Q81 are restructured as described above.
• The headline figure is a plain rate over the queries executed, not the QphDS composite, which only an audited run may report.