Re: Performance improvement for joins where outer side is unique

On 14 March 2015 at 14:51, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On 13 March 2015 at 20:34, Kyotaro HORIGUCHI <
> horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
>>
>>
> For all that, I agree that the opition that this kind of separate
>> multiple-nested loops on relations, joins or ECs and so on for
>> searching something should be avoided. I personally feel that
>> additional time to such an extent (around 1%) would be tolerable
>> if it affected a wide range of queries or it brought more obvious
>> gain.
>>
>>
> For testing, I added some code to mark_unique_joins() to spit out a NOTICE:
>
> if (eclassjoin_is_unique_join(root, joinlist, rtr))
> {
> root->simple_rel_array[rtr->rtindex]->is_unique_join = true;
> elog(NOTICE, "Unique Join: Yes");
> }
> else
> elog(NOTICE, "Unique Join: No");
>
> and the same below for special joins too.
>
> On running the regression tests I see:
>
> "Unique Join: Yes" 1557 times
> "Unique Join: No" 11563 times
>

With this notice emitting code in place, I opened up pgAdmin and had a
click around for a few minutes.

If I search the log file I see:

Unique Join: No 940 times
Unique Join: Yes 585 times

It seems that joins with a unique inner side are quite common here.

Regards

David Rowley