1.0: is GROUP BY date_bin(...) vectorized? Measure, then make it so

Part of the 1.0 time-series set the owner chose on 2026-10-12. All seven are listed on #1283.

The workload

The core time-series query groups by a time bucket:

SELECT date_bin('5 minutes', ts, TIMESTAMP '2000-01-01') AS bucket, avg(v)
FROM metrics WHERE ts >= now() - interval '1 day'
GROUP BY 1 ORDER BY 1;

date_bin is core PostgreSQL (14 and later), and date_trunc is the older form.

Unknown today

Whether pgcolumnar's vectorized group-aggregate path takes a GROUP BY on an expression of a column, or falls back to core's slower Agg. Nothing in docs/features.md says either way.

Measure first

On a table of at least 10 million rows sorted by ts:

Use EXPLAIN to find which node runs for GROUP BY date_bin(...), for GROUP BY date_trunc(...), and for GROUP BY ts as the control.
Count backend instructions for each, with vectorization on and off.

Done when

If the expression falls back: the vectorized path accepts date_bin and date_trunc keys. An arm asserts the plan node and the answer against heap, and a measurement shows the gain.
If it is already taken: an arm pins it, and docs/features.md says so.

🤖 Generated with Claude Code

jdatcmd is starting this: measurement first, no code yet

What the docs already say (docs/features.md): vectorized GROUP BY (Columnar Batch Fold) is opt-in, off by default (pgcolumnar.enable_group_vectorization). It applies only when the query groups by plain columns and aggregates with count, sum or avg. So GROUP BY date_bin(...) very likely falls back to the row-at-a-time path, which memory puts at about 920 instructions per row for a grouped aggregate. That is a reading of the docs, not a measurement, so the measurement follows.

Plan

Build: PG17 non-assert (/usr/local/pg17_nc), from main, in its own worktree and cluster.
Table: 10,000,000 rows of (device int, ts timestamptz, v float8), 1000 devices, ts sorted (vacuum_sorted), ANALYZE.
Arms, each with enable_group_vectorization off (the default) and on:
1. GROUP BY device, the plain-column control
2. GROUP BY date_bin('5 minutes', ts, TIMESTAMPTZ '2000-01-01')
3. GROUP BY date_trunc('minute', ts)
All three aggregate count(*), avg(v), with a WHERE ts >= ... filter, so nothing is answered from metadata.
Recorded per arm:
- whether the plan shows Columnar Batch Fold
- backend instructions per row, via perf stat -p with PGAPPNAME, the pg_sleep attach window, and the PMU that counted at least 95% of the window
- an md5 of the result rows, so the arms are compared on the same answer

What the result decides

If arms 2 and 3 show no Batch Fold, this issue becomes the work: accept date_bin/date_trunc keys in the grouping fold, and possibly turn it on by default for them.
If they already fold, I add an arm that pins it and close with the numbers.

🤖 Generated with Claude Code

Measured: `date_bin` grouping IS vectorized, but only behind a setting that is off by default

PG17 non-assert (/usr/local/pg17_nc), 10,000,000 rows, main at ede9a3c.

The plans, read in full

enable_group_vectorization off (the default): core HashAggregate, with date_bin computed per row in a Result node above the columnar scan.
on: the columnar scan groups the rows itself (Columnar Vectorized Group Keys: 1, Columnar Vectorized Aggregates: 2). That holds for date_bin, for date_trunc and for a plain column. None of them shows Batch Fold: yes, so this is the vectorized grouping node, not the batch fold.
With it on, min/max are vectorized too.

docs/features.md is out of date on both counts. It says the path needs plain columns and only count, sum and avg.

A correction to my own first reading. My script counted grep -c 'Columnar Batch Fold', which also matches Columnar Batch Fold: no. Every "batch_fold=1" it printed meant only that the line was present. The plans above are what the node actually is.

Instructions per row

Backend instructions, taken from the PMU that counted at least 95% of the window. Three runs of each query, count(*), avg(v) with a ts >= filter, and an identical result-set md5 in both columns of each row:

key	off (default)	on	change
`date_bin('5 minutes', ts, ...)`	3186	1798	1.77x fewer
`date_trunc('minute', ts)`	5576	2729	2.04x fewer
`device` (plain column)	refused	1822	-

The device/off reading was refused by the instrument: the backend spent 6% of the window on an E-core, below the 95% rule. I will re-take it pinned to the P-cores (taskset -p -c 0-3 before attaching perf, per memory).

So the issue's question changes: why is it off by default, and can it be on?

It has been off since it landed (#289, 1afe6d6, "foundation"). The "before-default-on" findings in that review (87fb8da) were fixed, and nobody changed the default afterwards. One property blocks a simple flip:

pgcolumnar.groupagg_max_groups (default 1,000,000) is enforced at execution against the real group count, and over the cap the query errors rather than falling back, since the plan is fixed by then.

With the setting on by default, date_bin('1 second', ...) over two weeks (about 1.2M groups), or GROUP BY a high-cardinality key, would turn from a working query into an error.

Options, for the owner's decision

Leave it off by default. Document it in docs/features.md and best-practices as the switch time-series users should turn on. Costs nothing and helps only users who read the docs.
On by default, gated by the planner's group estimate: take the path only when the estimate is well under the cap, for example 10%, and keep the runtime error as the backstop. The risk is a bad estimate.
On by default, never erroring: past the cap, spill the open-addressing table, or hand the remaining rows to core aggregation. This is the most work, and it is the only option that cannot turn a working query into an error.

My recommendation is 2 now, 3 as a follow-up. Option 2 is small and testable: an arm where a mis-estimate makes the error appear, and an arm where a correct estimate declines the path. I am asking the owner before writing code.

🤖 Generated with Claude Code

Owner's decision: gate by the estimate, on by default, then spill. Measured first: the estimate cannot gate time buckets yet

The owner chose option 2 now and option 3 as a follow-up. Before writing the gate, I measured the estimate it would gate on. PG17 non-assert, 2,000,000 rows, ANALYZEd, enable_group_vectorization=on, and the custom node's rows= against count(*) of the grouped query:

key	estimated	actual	over-estimate
`device`	1,000	1,000	1.00x
`v`	9,940	9,973	1.00x
`ts`	1,999,800	2,000,000	1.00x
`date_bin('1 second', ts, ...)`	1,999,800	2,000,000	1.00x
`date_trunc('minute', ts)`	1,999,800	33,334	60x
`date_bin('5 minutes', ts, ...)`	1,999,800	6,667	300x
`date_trunc('hour', ts)`	1,999,800	556	3,597x

The #369 bound (pgcolumnar_group_estimate_bound) does not bring these down on this table. Gating on this estimate would decline the fast path for exactly the queries this issue is about. The code's own comment says so ("deliberately NOT a gate ... would wrongly disable this path on exactly the large tables it helps").

Updated plan

Estimate time buckets. For date_bin(constant interval, ts, constant origin) and date_trunc(constant unit, ts), estimate groups as (max - min of ts) / width, from the column's statistics, or from zone maps where those are tighter. Verified by arms comparing the estimate with the actual count across bucket widths. The target is within a small factor, not 60x to 3600x.
Gate and default on. Take the path only when the estimate is well under groupagg_max_groups (proposed: 10%). Turn enable_group_vectorization on by default. Arms:
- a bucket query is taken;
- a query whose estimate exceeds the gate is declined;
- a deliberately mis-estimated query still errors with guidance at the runtime cap.
Docs: docs/features.md is wrong today. It says plain columns only and count/sum/avg only; date_bin, date_trunc, min and max are taken.
Spill instead of error: a separate follow-up issue.

First I find out why #369's bound is not applying here, because the comment above it says it handles date_trunc('minute', ts).

🤖 Generated with Claude Code

@OffgridwithJD

@OffgridwithJD, the owner changed the release gate today: alpha5 is not tagged until all seven time-series features (#1290–#1296) are complete.

What this changes for us:

Nothing is held for the tag any more. opt: estimate time-bucket groups from the zone maps, and recognise date_bin (#1292) #1304, the bucket estimate, goes ahead now. Next on 1.0: is GROUP BY date_bin(...) vectorized? Measure, then make it so #1292 is phase 2: gate the path on the estimate and turn it on by default, using your option 3 vs 5 measurement. Grouped vectorized aggregate: spill or hand off to core past groupagg_max_groups, instead of erroring #1300 (spill) follows.
Still open: 1.0: the maintenance daemon should run expire() for tables that declare a TTL #1290 (daemon expire, mine), 1.0: is GROUP BY date_bin(...) vectorized? Measure, then make it so #1292 (mine), 1.0: incremental rollups (continuous aggregates) over columnar tables #1294 (incremental rollups, joint, design first), 1.0: tier cold partitions to Parquet on object storage #1295 (tiering, yours) and 1.0: gap filling, LOCF and approximate aggregates: recipes, compatibility, or build #1296 (gap filling and approximate aggregates, mine). 1.0: partition lifecycle for time-partitioned columnar tables, via pg_partman #1291 and 1.0: answer max(ts) and latest-per-device queries from zone maps #1293 are closed.
The tag-day steps wait until all seven are merged. Those are the release date, the CHANGELOG rename, the upgrade test from alpha4 on 15–19, and the tag. The upgrade test passes on all five majors at fix: a missing pyarrow declines a suite instead of failing it (#1215) #1286's head today, but I'll rerun it on the exact tagged tree.

#1304 conflicts with main in expected_tests.txt after #1302. I'll rebase it once #1286 merges, then ask you to review.

test: the group estimate must bound a time bucket from the zone maps (#…

opt: estimate time-bucket groups from the zone maps, and recognise da…

Merge pull request #1304 from commandprompt/fix/1292-bucket-estimate

#1304 is merged (dd08f4e), after your approval at 4aeccd0 and 15 green checks. Thanks for the sweep and the cost table.

Agreed: option 5 lands before phase 2 turns the gate on by default. Your numbers settle it. The single scan reads every zone-map row, all columns plus the per-vector rows, then filters in C: 1006 planning buffers against 120 on main at 100M rows. Option 5 is +3 at every size, with write cost in the noise. A gated-on default can't pay the first; it can pay the second.

Please build option 5, as you offered: a typed comparison, widened per flush, and recomputed on rewrite. Phase 2 waits for it. One thing I'd want an arm for: the extent must stay a true bound after expire and compact_rewrite retire or rewrite groups. A stale extent that is too wide only over-estimates, the safe direction; one that is too narrow would under-estimate. A shrink is fine only if it's recomputed, never if it's guessed.

Meanwhile I'm on #1290: the suite is 16/16 on PG17, and the pytest twin is next. Then I'll review #1306.

@OffgridwithJD

Phase 2 plan: gate on the estimate, then on by default

Option 5 (#1325) and #1304's bucket estimate are both merged, so the estimate this gate needs exists. I'm taking phase 2. @OffgridwithJD, please attack the plan, especially the threshold and the misestimate arm.

Goal

pgcolumnar.enable_group_vectorization is on by default. A grouped query takes the vectorized path only when its estimated group count is at most 10% of pgcolumnar.groupagg_max_groups (100,000 at the default 1,000,000). A query estimated over that limit gets the ordinary plan, not an error.

Not in scope

Spilling instead of erroring when the actual count passes the cap is Grouped vectorized aggregate: spill or hand off to core past groupagg_max_groups, instead of erroring #1300.
The 1.77x and 2.04x speed-ups are not re-measured. This phase changes when the path is taken, not what it does.

Decided, and why

Threshold: 10%, fixed, no new setting. A user who wants more groups on the fast path raises groupagg_max_groups, and the gate rises with it. The 10x margin covers estimates that are off by up to 10x. opt: estimate time-bucket groups from the zone maps, and recognise date_bin (#1292) #1304's bucket estimate is within a small factor, and plain columns use real statistics.
Uninformed estimates are declined. With no statistics and no bound, the estimate is near the row count, so the path is declined. That is the safe direction: the query runs as it does today.

Phases, each with its proof

Red first, in native_groupagg.sh and its pytest twin:
- the setting is on by default (SHOW);
- by default, a date_bin('5 minutes', ts, ...) query over an analyzed table takes the vectorized node;
- a GROUP BY on an analyzed key with about 200,000 distinct values is declined at the default cap, and the answer equals the off plan's;
- raising groupagg_max_groups to 10,000,000 takes that same query;
- misestimate: analyze 50 distinct keys, insert 5,000 more without analyze, and set the cap to 1,000. The estimate passes the gate, the actual count exceeds the cap, and the query errors with the existing hint. This shows the runtime cap is still the backstop.
Proof: the shell twin red on main for the intended reason, then green.
Gate and default flip, in columnar_vector.c and the setting's definition. Proof: both twins green on 15–19, and mutations recorded in the ledger: gate removed, gate at 100%, default off.
The full matrix on 15–19, because the default changes plans everywhere. Seventeen shell suites and nine pytest files set the setting explicitly and are unaffected. Any other suite that asserts a default plan will show up there. Each one is either pinned off, where the test is about the other plan, or updated, where the new plan is right; the PR lists each. Proof: run_all_versions.sh green on 15–19.
Docs. docs/features.md is wrong today: it says plain columns only and count/sum/avg only, but date_bin, date_trunc, min and max are taken. Also the configuration reference (the default and the gate) and the CHANGELOG. Proof: docs_style green.

Risks and open questions

Phase 3 is the unknown size. If many suites assert default plans, the PR grows. I'll report the count before fixing them.
Parallel plans: the gated path still competes on cost with the parallel arm (The grouped parallel arm loses to the serial node because the Finalize is priced off estimate_num_groups on an expression key #369), and nothing changes there.
Open: is 10% right, or would you rather have it as a setting?

test: the grouped path on by default and gated on its estimate, red f…

feat: the grouped vectorized path is on by default, gated on its esti…

test: suites that assumed the grouped path off, and the pytest twin o…

test: TESTS.md and expected_tests for the renamed and added gate tests (

docs: the grouped path is on by default and gated on its estimate (#1292

Merge pull request #1330 from commandprompt/feat/1292-gate-default-on

@OffgridwithJD

Complete. These were merged:

opt: estimate time-bucket groups from the zone maps, and recognise date_bin (#1292) #1304: time-bucket group estimates from the zone maps, and recognising date_bin.
feat: a per-column extent the planner reads in one lookup, recomputed whenever groups go (#1304 option 5) #1325: option 5, the per-column extent the planner reads in one lookup.
feat: GROUP BY is vectorized by default, gated on the group estimate (#1292) #1330 (b55cb23): GROUP BY vectorized by default, approved by @OffgridwithJD at e62d472, with 15 green checks.

The path is taken only when an upper bound on the group count is within pgcolumnar.groupagg_max_groups: the table's live row count, or the time buckets the zone maps allow. So the default never errors. The first version gated on the estimate, and @OffgridwithJD's review showed a skewed key fooling ANALYZE 68x; a bound can't be fooled that way.

Still to come:

spilling instead of erroring when a cap is lowered after planning: Grouped vectorized aggregate: spill or hand off to core past groupagg_max_groups, instead of erroring #1300;
a test for the NULL group in the bucket bound, from the review: a follow-up PR.

Navigation Menu

Repository navigation

1.0: is GROUP BY date_bin(...) vectorized? Measure, then make it so #1292

The workload

Unknown today

Measure first

Done when

jdatcmd is starting this: measurement first, no code yet

Plan

What the result decides

Measured: `date_bin` grouping IS vectorized, but only behind a setting that is off by default

The plans, read in full

Instructions per row

So the issue's question changes: why is it off by default, and can it be on?

Options, for the owner's decision

Owner's decision: gate by the estimate, on by default, then spill. Measured first: the estimate cannot gate time buckets yet

Updated plan

Phase 2 plan: gate on the estimate, then on by default

Goal

Not in scope

Decided, and why

Phases, each with its proof

Risks and open questions

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

1.0: is GROUP BY date_bin(...) vectorized? Measure, then make it so #1292

Description

The workload

Unknown today

Measure first

Done when

Activity

jdatcmd commented on Oct 12, 2026

jdatcmd is starting this: measurement first, no code yet

Plan

What the result decides

jdatcmd commented on Oct 12, 2026

Measured: date_bin grouping IS vectorized, but only behind a setting that is off by default

The plans, read in full

Instructions per row

So the issue's question changes: why is it off by default, and can it be on?

Options, for the owner's decision

jdatcmd commented on Oct 12, 2026

Owner's decision: gate by the estimate, on by default, then spill. Measured first: the estimate cannot gate time buckets yet

Updated plan

jdatcmd commented on Oct 12, 2026

jdatcmd commented on Oct 12, 2026

jdatcmd commented on Oct 13, 2026

Phase 2 plan: gate on the estimate, then on by default

Goal

Not in scope

Decided, and why

Phases, each with its proof

Risks and open questions

jdatcmd commented on Oct 13, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Measured: `date_bin` grouping IS vectorized, but only behind a setting that is off by default