Re: Performance improvement for joins where outer side is unique

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