Re: UniqueKey on Partitioned table. - Mailing list pgsql-hackers

From David Rowley
Subject Re: UniqueKey on Partitioned table.
Date
Msg-id CAApHDvrTKNX0W-9KpQuDJojV_EByoL_vNjpwHAXJUB9AYvcADw@mail.gmail.com
Whole thread Raw
In response to Re: UniqueKey on Partitioned table.  (Andy Fan <zhihui.fan1213@gmail.com>)
Responses Re: UniqueKey on Partitioned table.
List pgsql-hackers
On Tue, 6 Apr 2021 at 22:31, Andy Fan <zhihui.fan1213@gmail.com> wrote:
> On Wed, Mar 31, 2021 at 9:12 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
>> I think the reason we add ECs for sort expressions is to use
>> transitive relationship. The EC may start with a single member but
>> later in the planning that member might find partners which are all
>> equivalent. Result ordered by one is also ordered by the other. The
>> same logic applies to UniqueKey as well, isn't it. In a result if a
>> set of columns makes a row unique, the set of columns represented by
>> the other EC member should be unique. Though a key will start as a
>> singleton it might EC partners later and thus thus unique key will
>> transition to all the members. With that logic UniqueKey should use
>> just ECs instead of bare expressions.
>
>
> TBH, I haven't thought about this too hard, but I think when we build the
> UniqueKey, all the ECs have been built already.  So can you think out an
> case we start with an EC with a single member at the beginning and
> have more members later for UniqueKey cases?

I don't really know if it matters which order things happen. We still
end up with a single EC containing {a,b} whether we process ORDER BY b
or WHERE a=b first.

In any case, the reason we want PathKeys to be ECs rather than just
Exprs is to allow cases such as the following to use an index to
perform the sort.

# create table ab (a int, b int);
# create index on ab(a);
# set enable_seqscan=0;
# explain select * from ab where a=b order by b;
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using ab_a_idx on ab  (cost=0.15..83.70 rows=11 width=8)
   Filter: (a = b)
(2 rows)

Of course, we couldn't use this index to provide pre-sorted results if
"where a=b" hadn't been there.

David



pgsql-hackers by date:

Previous
From: Andy Fan
Date:
Subject: Re: UniqueKey on Partitioned table.
Next
From: Pavel Borisov
Date:
Subject: Re: [PATCH] Covering SPGiST index