Re: Performance improvement for joins where outer side is unique

On 24 August 2015 at 07:31, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

>
> dim 100k rows, fact 1M rows
> ---------------------------
>
> master patched
> ------- -------
>
..

> med 280.994 261.406 (-7%)
>
>
> dim 1M rows, fact 10M rows
> --------------------------
>
> master patched
> -------- --------
>
..

>
> med 4360.157 3648.333 (-17%)
>
>
> dim 10M rows, fact 100M rows
> ----------------------------
>
> master patched
> -------- --------
>
..

> med 45898.408 40810.203 (-10%)
>
>
> So the gains seem quite solid - it's not something that would make the
> query an order of magnitude faster, but it's well above the noise.
>
> Of course, in practice the queries will be more complicated, making the
> improvement less significant, but I don't think that's a reason not to
> apply it.
>
>
Many thanks for doing that performance testing.

> Two minor comments on the patch:
>
> 1) the 'subquery' variable in specialjoin_is_unique_join is unused
>
> 2) in the explain output, there should probably be a space before the
> '(inner unique)' text, so
>
> Hash Join (inner unique) ...
>
> instead of
>
> Hash Join(inner unique)
>
> but that's just nitpicking at this point. Otherwise the patch seems quite
> solid to me.
>

The attached fixes these two issues.

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services