Horton

Re: Pathify RHS unique-ification for semijoin planning

Richard Guo <guofenglinux@gmail.com>

View thread
From: Richard Guo <guofenglinux@gmail.com>
To: wenhui qiu <qiuwenhuifx@gmail.com>
Cc: Alexandra Wang <alexandra.wang.oss@gmail.com>, Álvaro Herrera <alvherre@kurilemu.de>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Andy Fan <zhihuifan1213@163.com>
Date: 2025-08-01T01:53:37Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Simplify relation_has_unique_index_for()

  2. Pathify RHS unique-ification for semijoin planning

  3. Convert varatt.h access macros to static inline functions.

  4. Re-export a few of createplan.c's make_xxx() functions.

On Thu, Jul 31, 2025 at 9:49 PM wenhui qiu <qiuwenhuifx@gmail.com> wrote:

> > This seems to be another case where the planner chooses a suboptimal
> >  plan due to inaccurate cost estimates.

> Agree ,I increased some rows , set enable_hashagg to on and off ,There's no difference in execution time. The execution plan is the same.

Yeah, I found that if you increase the total number of rows in the
table from 1000 to 1083 or more, you consistently get the more
efficient plan -- regardless of whether enable_hashagg is on or off.

> > #define IS_UNIQUEIFIED_REL(rel, sjinfo, nominal_jointype) \
>  >   ((nominal_jointype) == JOIN_INNER && (sjinfo)->jointype == JOIN_SEMI && \
> >     bms_equal((sjinfo)->syn_righthand, (rel)->relids))
> >
> >... and then the check in final_cost_hashjoin() becomes:
> >
>  >   if (IS_UNIQUEIFIED_REL(inner_path->parent, extra->sjinfo,
> >                           path->jpath.jointype))
> >    {
> >        innerbucketsize = 1.0 / virtualbuckets;
>  >       innermcvfreq = 0.0;
> >    }
> >
> >Would this be a better approach?  Any thoughts?

> This approach does indeed more accurately capture the fact that the relation has been unique-ified, especially in cases where a semi join has been transformed into an inner join. Compared to the current heuristic checks in costsize.c that rely on inner_path->rows, this method is more semantically meaningful and robust.

The current check in costsize.c relies on the path type, not the path
rows as you mentioned.  However, I agree that the check I proposed is
more robust and extensible: if additional path types are introduced to
represent unique-ification, this check wouldn't need to change.  So I
plan to go this way, unless there are any objections.

Thanks
Richard