Re: BUG #19566: redundant HAVING COUNT(*) > 0 changes a parallel hash join into a substantially faster indexed

On Wed, 22 Jul 2026 at 11:04, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19566
> Logged by: cl hl
> Email address: 2320415112(at)qq(dot)com
> PostgreSQL version: 17.10
> Operating system: Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
> Description:
>
> ## 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`.

This one also doesn't look like a bug, and there seems to be a
misunderstanding as to why you get the fast plan.

So count(*) > 0 is always true for a GROUP BY query since every group
has at least one row. But the proposed fix of recognising that and
dropping the predicate would make your fast query slow because the
fast plan only exists as a side effect of Postgres *not* recognising
it.

If I run your example (I set random_page_cost back to default), I get
a fast plan but only because of a misestimate. Since count(*) has no
statistics, the count(*) > 0 predicate gets the default 33.3%
selectivity which drops the estimated group count from around 10,000
to around 3,300:

Finalize HashAggregate (cost=22824.93..23149.54 rows=3329 width=12)
(actual rows=10000.00 loops=1)

vs

HashAggregate (cost=36925.00..37224.64 rows=9988 width=12) (actual
rows=10000.00 loops=3)

A nested loop's cost increases with the number of outer rows, so that
smaller estimate is just enough to push the planner from the parallel
hash join to an index nested loop. If you remove that predicate, you
lose this accidental benefit.

And then your script runs ANALYZE, but it doesn't run VACUUM, which
means the visibility map hasn't yet been populated and therefore makes
an index-only scan look expensive, which is why the plan avoids the
nested loop. Once a VACUUM has run, both pick the fast nested-loop
plan.

So the solution is really just to run VACUUM ANALYZE after the INSERTs.

Thom