Hi Hackers:
This is a patch for unique elimination rewrite for distinct query.
it will cost much for a big result set and some times it is not
necessary. The basic idea is the unique node like in the following
can be eliminated.
1. select distinct pk, ... from t;
2. select distinct uk-col1, uk-col2, ...
from t where uk-col1 is not null and uk-col2 is not null;
3. select distinct a, b .... from t group by a, b;
4. select distinct t1.pk, t2.pk, ... from t1, t2.
The distinct keyword in above sql is obviously redundant,
But a SQL may join a lot of tables with tens of columns in target
list and a number of indexes. Finally the sql is hidden in
hundreds of SQL in system, it will be hard to find it out.
That's why I want the kernel can keep watching it,
based on that it will not cost too much. Oracle has similar rewrite
as well.
The rule for single relation is:
a). The primary key is choose in target list.
b). The unique key is choose in the target list,
and we can tell the result of the related column is not nullable.
we can tell it by catalog and qual.
c). The group-by columns is choose in target list.
d). The target list in subquery has a distinct already.
(select distinct xxx from (select distinct xxx from t2));
The rule for multi-relations join is:
e). if any relation yield a unique result, then the result of join will be
unique as well
If an sql matches any rule of above, we can remove the unique node.
Rule d) is not so common and complex to implement, so it is not
included in this patch.
Implementation:
f). I choose the target list per table, if there is hasDistinctOn, the source
is the target list intersect distinctClause. or else, the source is target list only.
g). the pk/uk columns information is gathered by RelationGetIndexAttrBitmap.
a new filed RelationData.plain_uk_ukattrs is added and gathered as well.
h). As last if any rule matches, Query->distinctClause & Query->hasDistinctOn
will be cleared to avoid generating the related path.
There are also some fast paths to return earlier:
i). If a table in join-list, but no columns is choose in target list.
j). The join-list contains sub-query. (this rewrite happens after sub-query pull-up)
k). Based on the cost of the checking, we check group by first and
then PK and then UK + not null.
There is no impact for non-distinct query, as for distinct query, this rule will
increase the total cost a bit if the distinct can't be removed. The unique
check is most expensive, so here is the data to show the impact, a 4
columns table, no pk, 1 uk with 2 columns.
With this feature disable: avg plan time: 0.095ms
With this feature enabled: avg plan time: 0.102ms
Basically I think the cost would be ok.
Concurrency:
l). When we see a pk or uk index, so we remove the index on another session,
I think this would be ok because of MVCC rules.
m). When we are creating an index in another session but it is not completed,
suppose we can't get it with RelationGetIndexAttrBitmap. so it should be ok
as well.
The behavior can be changed online with enable_unique_elimination,
it is true by default.
The patch is generated with the latest code on github,
and the current HEAD is 34a0a81bfb388504deaa51b16a8bb531b827e519.
The make installcheck-world & check-world has pass.
Test case join.sql and sysview.sql are impacted by this change
and they are expected, the changed expected.out file is included in this patch.
Please let me know if you have any questions.
Thank you
