Re: UniqueKey on Partitioned table.

On Tue, Mar 30, 2021 at 4:16 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 30 Mar 2021 at 02:27, Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> >
> > On Sat, Mar 27, 2021 at 11:44 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
> wrote:
> > >
> > > On Sat, Mar 27, 2021 at 3:07 AM Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
> wrote:
> > >> Thanks for the patch. After a short look through it I'm a bit confused
> > >> and wanted to clarify, now uniquekeys list could contain both Expr and
> > >> EquivalenceClass?
> > >
> > >
> > > Yes, That's because I don't want to create a new EquivalenceClass
> (which
> > > would make the PlannerInfo->eq_classes longer) if we don't have
> > > one , then I just used one Expr instead for this case.
> > > However during the
> > > test, I found some EquivalenceClass with only 1 EquivalenceMember
> > > unexpectedly.
> > >
> >
> > Pathkeys may induce single member ECs. Why UniqueKeys are an exception?
>
>
When working with UniqueKey, I do want to make PlannerInfo.eq_classes short,
so I don't want to create a new EC for UniqueKey only. After I realized we
have
so single-member ECs, I doubt if the "Expr in UniqueKey" will be executed
in real.
I still didn't get enough time to do more research about this.

I doubt that it should be. get_eclass_for_sort_expr() makes
> single-member ECs for sorts.

Thanks for this hint. I can check more cases like this.

> I imagine the UniqueKey stuff should
> copy that... However, get_eclass_for_sort_expr() can often dominate

the planning effort in queries to partitioned tables with a large
> number of partitions when the query has an ORDER BY. Perhaps Andy is
> trying to sidestep that issue?
>

Yes. a long PlannerInfo.eq_classes may make some finding slow, and in
my UniqueKey patch, I am trying to not make it longer.

> I mentioned a few things in [1] on what I think about this.
>
> David
>
> [1]
> https://www.postgresql.org/message-id/CAApHDvoDMyw=hTuW-258yqNK4bhW6CpguJU_GZBh4x+rnoem3w@mail.gmail.com
>

I appreciate all of the people who helped on this patch and others. I would
like to share more of my planning. As for the UniqueKey patch, there are
some
design decisions that need to be made. In my mind, the order would be:
a). How to present the notnullattrs probably in [1] b). How to present
the element
in UniqueKey. Prue EquivalenceClasses or Mix of Expr and EquivalenceClass
as
we just talked about. c). How to maintain the UniqueKey Partitioned table
in the
beginning of this thread. As for a) & c). I have my current proposal for
discussion.
as for b) I think I need more thinking about this. Based on the idea
above, I am
not willing to move too fast on the following issue unless the previous
issue
can be addressed. Any feedback/suggestion about my current planning is
welcome.

[1]
https://www.postgresql.org/message-id/CAKU4AWp3WKyrMKNdg46BvQRD7xkNL9UsLLcLhd5ao%3DFSbnaN_Q%40mail.gmail.com

--
Best Regards
Andy Fan (https://www.aliyun.com/)