Re: Performance improvement for joins where outer side is unique

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Performance improvement for joins where outer side is unique
Date: 2015-03-22 06:42:21
Message-ID: CAApHDvrKwMmTwkXfn4uazYZA9jQL1c7UwBjBtuwFR69rqLVKfA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 20 March 2015 at 21:11, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

>
> I can continue working on your patch if you like? Or are you planning to
> go further with it?
>
>
I've been working on this more over the weekend and I've re-factored things
to allow LEFT JOINs to be properly marked as unique.
I've also made changes to re-add support for detecting the uniqueness of
sub-queries.

Also, I've added modified the costing for hash and nested loop joins to
reduce the cost for unique inner joins to cost the join the same as it does
for SEMI joins. This has tipped the scales on a few plans in the regression
tests.

Also, please see attached unijoin_analysis.patch. This just adds some code
which spouts out notices when join nodes are initialised which states if
the join is unique or not. Running the regression tests with this patch in
places gives:

Unique Inner: Yes == 753 hits
Unique Inner: No == 1430 hits

So it seems we can increase the speed of about 1 third of joins by about
10%.
A quick scan of the "No"s seems to show quite a few cases which do not look
that real world like. e.g cartesian join.

It would be great if someone could run some PostgreSQL application with
these 2 patches applied, and then grep the logs for the Unique Inner
results... Just to get a better idea of how many joins in a real world case
will benefit from this patch.

Regards

David Rowley

Attachment Content-Type Size
unijoin_2015-03-22_87bc41e.patch application/octet-stream 57.4 KB
unijoin_analysis.patch application/octet-stream 1.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2015-03-22 07:14:52 Re: PATCH: numeric timestamp in log_line_prefix
Previous Message Pavel Stehule 2015-03-22 06:11:22 Re: proposal: searching in array function - array_position