agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedFrom: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Improve UNION's output row count estimate
Date: Wed, 01 Jul 2026 06:13:21 +0000
Message-ID: <E1weoCH-000sLJ-2l@gemulon.postgresql.org> (raw)
Improve UNION's output row count estimate
A UNION (not UNION ALL) removes duplicates, so its output has no more
rows than its input. The planner did not account for this: it set the
set-op relation's row count to the total size of the appended input,
as though dedup removed nothing. That inflated estimate then
propagated to every node above the UNION, leading to poor plan choices
such as a hash join with a full table scan where an index nested loop
would have been cheaper.
This patch estimates the number of distinct output rows as the sum of
the per-child distinct-group estimates instead. This relies on the
fact that:
distinct(A union B) <= distinct(A) + distinct(B)
that is, the union cannot have more distinct rows than its children do
in total. And because each child's distinct-group estimate never
exceeds that child's row-count estimate, this sum is never larger than
the old estimate, so it only tightens the previous over-estimate.
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Chengpeng Yan <chengpeng_yan@outlook.com>
Discussion: https://postgr.es/m/CAMbWs48Fu1nhGXPa60oc+adj7ge4dn0nHhqngqKvOVVQP61duA@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/be69a5ff1fd96cdbfe917ac4739142e52aab126e
Modified Files
--------------
src/backend/optimizer/prep/prepunion.c | 68 +++++++++++++++-------------------
src/test/regress/expected/union.out | 17 +++++++++
src/test/regress/sql/union.sql | 6 +++
3 files changed, 53 insertions(+), 38 deletions(-)
Message-ID: <E1weoCH-000sLJ-2l@gemulon.postgresql.org>
Permalink: ../E1weoCH-000sLJ-2l@gemulon.postgresql.org/
Also on: postgresql.org/message-id/E1weoCH-000sLJ-2l@gemulon.postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: rguo@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Improve UNION's output row count estimate
In-Reply-To: <E1weoCH-000sLJ-2l@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox