The computable value of pruning

What is pruning worth in dollars, and why is cost concentrated in the tail of queries?

The dollar value of a data-reduction technique is

and the techniques multiply.

Two conditions the simple formula hides

1. Skipping depends on correlation, not on the presence of statistics. Min/max metadata on a column that is not correlated with the file sort order prunes nothing, yet it costs storage and maintenance. (If every file contains rows from all dates, each file's min/max range covers every date, and no file can be skipped.) This is the most common way a data-skipping investment returns zero.
2. All three values depend on a query distribution. Layout optimization is a bet on a stable workload. When the workload changes, the layout is not neutral. It is actively wrong. Layout is a property of the table plus the workload, never of the table alone. And the price of moving from a bad layout to a good one, rewriting 10 TB of files with its compute bill, downtime risk and engineering cost, is what makes most cheaper designs unreachable: a design is only "better" if you can afford to get there.

Cost is concentrated in the tail

Cost is spread over queries far more unevenly than latency. It is routine for the top 1% of queries to carry the majority of scan spend, because bytes scanned span about six orders of magnitude, while latency spans about two. You can check this with a query on your own query log (the table and column names here are examples):

-- Share of total scanned bytes carried by the most expensive 1% of queries
WITH ranked AS (
  SELECT bytes_scanned,
         PERCENT_RANK() OVER (ORDER BY bytes_scanned DESC) AS pr
  FROM query_log
  WHERE start_time >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT SUM(CASE WHEN pr <= 0.01 THEN bytes_scanned END) * 1.0
       / SUM(bytes_scanned) AS top1pct_share
FROM ranked;