From: Antonin Houska <ah@cybertec.at>
To: Andy Fan <zhihuifan1213@163.com>
cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
    David Rowley <dgrowleyml@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
    jian he <jian.universality@gmail.com>
Subject: Re: UniqueKey v2
In-reply-to: <87h6f1kqr6.fsf@163.com>
References: <7mlamswjp81p.fsf@e18c07352.et15sqa> <871qczm9oc.fsf@163.com> <7971.1713526758@antos> <875xvrnzga.fsf@163.com> <3739.1715594150@antos> <87h6f1kqr6.fsf@163.com>
Comments: In-reply-to Andy Fan <zhihuifan1213@163.com>
   message dated "Tue, 14 May 2024 11:15:58 +0800."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <21672.1717510459.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 04 Jun 2024 16:14:19 +0200
Message-ID: <21674.1717510459@antos>
Archived-At: <https://www.postgresql.org/message-id/21674.1717510459%40antos>
Precedence: bulk

Andy Fan <zhihuifan1213@163.com> wrote:
> Antonin Houska <ah@cybertec.at> writes:
> =

> >> Could you make the reason clearer for adding 'List *opfamily_lists;'
> >> into UniqueKey?  You said "This is needed to create ECs in the parent
> >> query if the upper relation represents a subquery." but I didn't get =
the
> >> it. Since we need to maintain the UniqueKey in the many places, I'd l=
ike
> >> to keep it as simple as possbile. Of course, anything essentical shou=
ld
> >> be added for sure. =

> >
> > If unique keys are generated for a subquery output, they also need to =
be
> > created for the corresponding relation in the upper query ("sub" in th=
e
> > following example):
> =

> OK.
> >
> > select * from tab1 left join (select * from tab2) sub;
> >
> > However, to create an unique key for "sub", you need an EC for each ex=
pression
> > of the key.
> =

> OK.
> > And to create an EC, you in turn need the list of operator
> > families.
> =

> I'm thinking if we need to "create" any EC. Can you find out a user case
> where the outer EC is missed and the UniqueKey is still interesting? I
> don't have an example now.  =

> =

> convert_subquery_pathkeys has a similar sistuation and has the following
> codes:
> =

> 				outer_ec =3D
> 					get_eclass_for_sort_expr(root,
> 											 (Expr *) outer_var,
> 											 sub_eclass->ec_opfamilies,
> 											 sub_member->em_datatype,
> 											 sub_eclass->ec_collation,
> 											 0,
> 											 rel->relids,
> 											 NULL,
> 											 false);
> =

> 				/*
> 				 * If we don't find a matching EC, sub-pathkey isn't
> 				 * interesting to the outer query
> 				 */
> 				if (outer_ec)
> 					best_pathkey =3D
> 						make_canonical_pathkey(root,
> 											   outer_ec,
> 											   sub_pathkey->pk_opfamily,
> 											   sub_pathkey->pk_strategy,
> 											   sub_pathkey->pk_nulls_first);
> 			}

I think that convert_subquery_pathkeys() just does not try that hard to
achieve its goal.

The example where it's important to create the EC in the outer query is wh=
at I
added to the subselect.sql regression test in the 0004- diff in [1]:

create table tabx as select * from generate_series(1,100) idx;
create table taby as select * from generate_series(1,100) idy;
create unique index on taby using btree (idy);
create view view_barrier with (security_barrier=3Dtrue) as select * from t=
aby;
analyze tabx, taby;
explain (costs off, verbose on) select * from tabx x left join view_barrie=
r y on idy =3D idx;

If you modify find_ec_position_matching_expr() to return -1 instead of
creating the EC, you will get this plan

Hash Left Join
   Output: x.idx, taby.idy
   Hash Cond: (x.idx =3D taby.idy)
   ->  Seq Scan on public.tabx x
         Output: x.idx
   ->  Hash
         Output: taby.idy
         ->  Seq Scan on public.taby
               Output: taby.idy

instead of this

Hash Left Join
   Output: x.idx, taby.idy
   Inner Unique: true
   Hash Cond: (x.idx =3D taby.idy)
   ->  Seq Scan on public.tabx x
         Output: x.idx
   ->  Hash
         Output: taby.idy
         ->  Seq Scan on public.taby
               Output: taby.idy

> >> > * uniquekey_useful_for_merging()
> >> >
> >> >   How does uniqueness relate to merge join? In README.uniquekey you=
 seem to
> >> >   point out that a single row is always sorted, but I don't think t=
his
> >> >   function is related to that fact. (Instead, I'd expect that pathk=
eys are
> >> >   added to all paths for a single-row relation, but I'm not sure yo=
u do that
> >> >   in the current version of the patch.)
> >> =

> >> The merging is for "mergejoinable join clauses", see function
> >> eclass_useful_for_merging. Usually I think it as operator "t1.a =3D t=
2.a";
> >
> > My question is: why is the uniqueness important specifically to merge =
join? I
> > understand that join evaluation can be more efficient if we know that =
one
> > input relation is unique (i.e. we only scan that relation until we fin=
d the
> > first match), but this is not specific to merge join.
> =

> So the answer is the "merging" in uniquekey_useful_for_merging() has
> nothing with merge join. =


I don't understand. The function comment does mention merge join:

/*
 * uniquekey_useful_for_merging
 *	Check if the uniquekey is useful for mergejoins above the given relatio=
n.
 *
 * similar with pathkeys_useful_for_merging.
 */
static bool
uniquekey_useful_for_merging(PlannerInfo *root, UniqueKey * ukey, RelOptIn=
fo *rel)


[1] https://www.postgresql.org/message-id/7971.1713526758%40antos

-- =

Antonin Houska
Web: https://www.cybertec-postgresql.com