| From: | Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | David Rowley <dgrowleyml(at)gmail(dot)com> |
| Subject: | HashAggregate loses worker spill stats on rescan |
| Date: | 2026-10-18 17:25:01 |
| Message-ID: | CAJTYsWW5evhmWDWAgknx=GXNMJG=k4-T+d435pZpg8Lqxc=kFQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
HashAgg seems to lose the worker's spill stats when Gather is rescanned.
In the example below, Worker 0 spills on the first scan, but EXPLAIN shows
one batch and no disk usage after the second:
CREATE TABLE hashagg_stats AS
SELECT value AS item, value % 10000 AS bucket
FROM generate_series(1, 1000000) AS value;
ALTER TABLE hashagg_stats SET (parallel_workers = 2);
ANALYZE hashagg_stats;
SET max_parallel_workers_per_gather = 2;
SET max_parallel_workers = 1;
SET parallel_leader_participation = off;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SET work_mem = '64kB';
SET hash_mem_multiplier = 2;
SET enable_sort = off;
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT limits.maximum,
(SELECT count(*)
FROM (SELECT bucket, count(*) FROM hashagg_stats
WHERE item <= (SELECT limits.maximum)
GROUP BY bucket) AS grouped)
FROM (VALUES (1000000), (10)) AS limits(maximum);
AFAICS, ExecEndAgg() clobbers the shared slot on each worker exit, rather
than combining the stats from successive scans.
Patch attached to keep the memory/disk peaks and add the spill batches.
I think we want to count the initial batch only once, as in the
non-parallel case. Otherwise, wouldn't even a non-spilling rescan add
another batch?
Regards,
Ayush
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Accumulate-HashAggregate-parallel-worker-stats-across-rescans.patch | application/octet-stream | 1.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Manu | 2026-10-18 17:13:11 | Re: ILIKE '%' never matches a non-ASCII byte in single-byte C-ctype databases |