From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
---|---|
To: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
Cc: | David Rowley <dgrowleyml(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, rushabh(dot)lathia(at)gmail(dot)com, Ashutosh Bapat <ashutosh(dot)bapat(at)2ndquadrant(dot)com> |
Subject: | Re: [PATCH] Keeps tracking the uniqueness with UniqueKey |
Date: | 2020-05-13 11:59:08 |
Message-ID: | CAKU4AWpOM3_J-B=wQtCeU1TGr89MhpJBBkv2he1tAeQz6i4XNw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
The attached is the v8-patches. The main improvements are based on
Ashutosh's
review (reduce the SRF impact and partition level UniqueKey). I also update
the
README.uniquekey based on the discussion. So anyone who don't want to go
through the long email can read the README.uniquekey first.
===
Just copy some content from the README for easy discussion.
As for inherit table, we maintain the UnqiueKey on childrel as usual. But
for
partitioned table we need to maintain 2 different kinds of UnqiueKey.
1). UniqueKey on the parent relation 2). UniqueKey on child relation for
partition wise query.
Example:
CREATE TABLE p (a int not null, b int not null) partition by list (a);
CREATE TABLE p0 partition of p for values in (1);
CREATE TABLE p1 partition of p for values in (2);
create unique index p0_b on p0(b);
create unique index p1_b on p1(b);
Now b is unique on partition level only, so the distinct can't be removed on
the following cases. SELECT DISTINCT b FROM p; However for query
SELECT DISTINCT b FROM p WHERE a = 1; where only one
partition is chosen, the UniqueKey on child relation is same as the
UniqueKey
on parent relation. The distinct can be removed.
Another usage of UniqueKey on partition level is it be helpful for
partition-wise join.
As for the UniqueKey on parent table level, it comes with 2 different ways.
1). the UniqueKey is also derived in Unique index, but the index must be
same
in all the related children relations and the unique index must contains
Partition Key in it. Example:
CREATE UNIQUE INDEX p_ab ON p(a, b); -- where a is the partition key.
-- Query
SELECT a, b FROM p; -- the (a, b) is a UniqueKey of p.
2). If the parent relation has only one childrel, the UniqueKey on
childrel is
the UniqueKey on parent as well.
The patch structure is not changed, you can see [1] for reference. The
patches is
based on latest commit ac3a4866c0.
Best Regards
Andy Fan
>
Attachment | Content-Type | Size |
---|---|---|
v8-0004-Remove-distinct-node-AggNode-if-the-input-is-uniq.patch | application/octet-stream | 27.9 KB |
v8-0003-Refactor-existing-uniqueness-related-code-to-use-.patch | application/octet-stream | 19.2 KB |
v8-0001-Introduce-RelOptInfo-notnullattrs-attribute.patch | application/octet-stream | 4.7 KB |
v8-0005-If-the-group-by-clause-is-unique-and-we-have-aggr.patch | application/octet-stream | 13.2 KB |
v8-0002-Introuduce-RelOptInfo.uniquekeys-attribute.patch | application/octet-stream | 54.0 KB |
v8-0006-Join-removal-at-run-time-with-UniqueKey.patch | application/octet-stream | 13.1 KB |
v8-0007-Renamed-adjust_inherited_tlist-List-tlist-AppendR.patch | application/octet-stream | 3.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2020-05-13 12:04:17 | Re: [PATCH] Keeps tracking the uniqueness with UniqueKey |
Previous Message | Sumanta Mukherjee | 2020-05-13 11:54:15 | Re: refactoring basebackup.c |