Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8rW-00000000idP-2mMH for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 13:59:42 +0000 Received: from localhost ([127.0.0.1]:46710 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR8rV-0000000BMB8-1IMW for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 13:59:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:52602) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8mA-0000000BHbz-35ra for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:54:11 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8m7-00000000TL9-0SU7 for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:54:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=mGlsctvN1Im/wxIlMD6ZSYYEx+q199jSPbldbXKx2I4=; b=H7vL/DO9xCxccCVeuNyL3/hlkK ILBgHzdXlLuDNgxE/2IeqKb0huJzKg5/vhTcezNIGpcliEk9uBc5yUxWN67Fu2xqJViRqiJBqFTIl McGeFEbm2n7YjYZiq/EYCEprlKSrpc+s+hJqlZmNOwEhYRclWdKQZZxJi8uKmfTr6t/jeZr7ai0hQ APlhO16zqGhe5daUYt5856wmHPbXGJ9NKFcv/+2Ln7TU17wgQZgWzHHK/+kBwfvwrWK4cqvyC00/I Agbvtl8SFvPv8vgRxjaBND1m0/kR0mfXt/dq0HmA1LwP70MV+dBbYvHbszHj3PtxL/J2CH2CD4ZKT 3hx/Z4IQ==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8m3-000000013Ex-3iJX for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:54:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR8m3-00000002jXc-0esw for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:54:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19882: Parallel HashAggregate loses worker spill statistics after Gather rescan To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 1548140642@qq.com Reply-To: 1548140642@qq.com, pgsql-bugs@lists.postgresql.org Date: Wed, 11 Nov 2026 13:53:46 +0000 Message-ID: <19882-1ff869bb8e72439e@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19882 Logged by: Milo Email address: 1548140642@qq.com PostgreSQL version: 18.6 Operating system: Linux/amd64 Description: =20 When a rescannable parallel subtree executes a spilling Partial HashAggregate and then executes a much smaller scan, EXPLAIN ANALYZE reports only the last generation's per-worker spill statistics. In the recorded case, Worker 0 initially used 33 batches and 22288kB of disk, but the two-pass plan reports one batch and no disk usage for that worker. Per-worker instrumentation should accumulate statistics across worker generations that reuse the same slot. The query results remain correct; the defect is in EXPLAIN's instrumentation. How to repeat: Save the following as reproducer.sql and run `psql -X -f reproducer.sql`: ```sql DROP TABLE IF EXISTS hashagg_data; CREATE TABLE hashagg_data AS SELECT value AS item, value % 10000 AS bucket FROM generate_series(1, 1000000) AS value; ALTER TABLE hashagg_data SET (parallel_workers =3D 2); ANALYZE hashagg_data; SET max_parallel_workers_per_gather =3D 2; SET max_parallel_workers =3D 1; SET parallel_leader_participation =3D off; SET parallel_setup_cost =3D 0; SET parallel_tuple_cost =3D 0; SET min_parallel_table_scan_size =3D 0; SET work_mem =3D '64kB'; SET hash_mem_multiplier =3D 2; SET enable_sort =3D off; SELECT 'BASELINE_BEGIN'; EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT count(*) FROM (SELECT bucket, count(*) FROM hashagg_data WHERE item <=3D 1000000 GROUP BY bucket) AS grouped; SELECT 'BASELINE_END'; SELECT 'RESCAN_BEGIN'; EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT limits.maximum, (SELECT count(*) FROM (SELECT bucket, count(*) FROM hashagg_data WHERE item <=3D (SELECT limits.maximum) GROUP BY bucket) AS grouped) FROM (VALUES (1000000), (10)) AS limits(maximum); SELECT 'RESCAN_END'; DROP TABLE hashagg_data; ``` The baseline contains: ``` -> Partial HashAggregate (actual rows=3D10000.00 loops=3D1) Worker 0: actual rows=3D10000.00 loops=3D1 Batches: 33 Memory Usage: 177kB Disk Usage: 22288kB ``` The rescanned subtree instead contains: ``` -> Gather (actual rows=3D5005.00 loops=3D2) Workers Launched: 1 -> Partial HashAggregate (actual rows=3D5005.00 loops=3D2) Worker 0: actual rows=3D5005.00 loops=3D2 Batches: 1 Memory Usage: 65kB ``` The `loops=3D2` value establishes that the subtree ran twice. Because the first execution spilled, cumulative worker instrumentation should retain a batch count greater than one and positive disk usage rather than showing only the final small execution's values. The issue was reproduced on PostgreSQL 18.6 on Linux/amd64. This occurs when a rescannable parallel subtree runs a spilling partial HashAggregate and later reuses the worker slot for a smaller scan.