Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19566: redundant HAVING COUNT(*) > 0 changes a parallel hash
 join into a substantially faster indexed
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 2320415112@qq.com
Reply-To: 2320415112@qq.com, pgsql-bugs@lists.postgresql.org
Date: Tue, 21 Jul 2026 14:44:34 +0000
Message-ID: <19566-eddca5914dd780b5@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19566-eddca5914dd780b5%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19566
Logged by:          cl hl
Email address:      2320415112@qq.com
PostgreSQL version: 17.10
Operating system:   Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
Description:       =20

## Description

This issue concerns `HAVING COUNT(*) > 0` on a normal grouped query. Every
group is formed from at least one input row, so its `COUNT(*)` is
necessarily positive. Adding this condition cannot change the result.
PostgreSQL nevertheless applies a selectivity estimate to the redundant
condition, which changes the estimated number of groups and leads to a
radically different join strategy.

### Expected Behaviour

PostgreSQL should recognize that `COUNT(*) > 0` is always true for groups
emitted by `GROUP BY`, or at least avoid letting this redundant predicate
cause a major plan-quality difference. The forms with and without the
predicate should return the same rows and receive plans with comparable
execution cost.

### Actual Behaviour

Without the redundant `HAVING`, PostgreSQL chooses a parallel hash join. In
the minimized case, each of three processes independently scans and
aggregates the one-million-row input, so the full fact table is processed
three times. The lookup table is also read using a parallel sequential scan.

Adding `HAVING COUNT(*) > 0` reduces the estimated group count from
approximately 10,017 to 3,339. This causes PostgreSQL to select a parallel
partial/final aggregate followed by an indexed nested loop. The fact table
is scanned once across the workers and the lookup table is accessed by
index.

Measured results from the standalone case:

| Query | Result | Execution time |
|---|---:|---:|
| Without HAVING | 9999 | 712.523 ms |
| With redundant HAVING | 9999 | 43.047 ms |

The redundant form is approximately 16.55x faster. The original generated
pair 3193 showed the same behavior class: median execution time decreased
from 25.870 ms to 1.857 ms, approximately 13.93x.

## How to repeat

Run the following complete SQL in a new PostgreSQL session:

```sql
DROP TABLE IF EXISTS having_count_fact;
DROP TABLE IF EXISTS having_count_lookup;

CREATE TABLE having_count_fact (
    group_id INTEGER NOT NULL,
    payload INTEGER NOT NULL
);

CREATE TABLE having_count_lookup (
    group_id INTEGER NOT NULL,
    padding TEXT NOT NULL
);

CREATE INDEX having_count_lookup_group_id_idx
    ON having_count_lookup (group_id);

-- 1,000,000 rows distributed over 10,000 non-empty groups.
INSERT INTO having_count_fact (group_id, payload)
SELECT g % 10000, g
FROM generate_series(1, 1000000) AS g;

-- The lookup contains keys 1 through 1,000,000. Group 0 has no match, so
-- both queries return 9,999.
INSERT INTO having_count_lookup (group_id, padding)
SELECT g, repeat('x', 50)
FROM generate_series(1, 1000000) AS g;

ANALYZE having_count_fact;
ANALYZE having_count_lookup;

-- Original form.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM (
    SELECT group_id, COUNT(*) AS row_count
    FROM having_count_fact
    GROUP BY group_id
) AS grouped_fact
JOIN having_count_lookup AS lookup
    USING (group_id);

-- Semantically equivalent form with redundant HAVING.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM (
    SELECT group_id, COUNT(*) AS row_count
    FROM having_count_fact
    GROUP BY group_id
    HAVING COUNT(*) > 0
) AS grouped_fact
JOIN having_count_lookup AS lookup
    USING (group_id);
```

On the tested version, the original plan contains:

```text
Gather
  -> Partial Aggregate
       -> Hash Join
            -> Parallel Seq Scan on having_count_lookup
            -> Hash
                 -> HashAggregate
                      -> Seq Scan on having_count_fact
Execution Time: 712.523 ms
```

The redundant-HAVING plan contains:

```text
Nested Loop
  -> Finalize HashAggregate
       Filter: count(*) > 0
       -> Gather
            -> Partial HashAggregate
                 -> Parallel Seq Scan on having_count_fact
  -> Index Only Scan on having_count_lookup
Execution Time: 43.047 ms
```

Exact timings depend on hardware and cache state, but both the large
performance difference and the plan change were reproduced on PostgreSQL
17.10 after `ANALYZE`.