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.
