Re: Performance improvement for joins where outer side is unique - Mailing list pgsql-hackers

From David Rowley
Subject Re: Performance improvement for joins where outer side is unique
Date
Msg-id CAApHDvrwHVdibDgf=nMDeC6Re=CbOBTdXUcgK06msUhwVSZBQA@mail.gmail.com
Whole thread Raw
In response to Re: Performance improvement for joins where outer side is unique  (David Rowley <dgrowleyml@gmail.com>)
List pgsql-hackers
On 14 March 2015 at 14:51, David Rowley <dgrowleyml@gmail.com> wrote:
On 13 March 2015 at 20:34, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.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

pgsql-hackers by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: MD5 authentication needs help -SCRAM
Next
From: David Rowley
Date:
Subject: Re: Performance improvement for joins where outer side is unique