Re: Performance improvement for joins where outer side is unique

From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Performance improvement for joins where outer side is unique
Date: 2015-08-23 23:35:54
Message-ID: CAKJS1f-9UTo6fA+h8rojd30CujSKscZamLn5YnKw5+ASbV0Wwg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
unique_joins_2015-08-24.patch application/octet-stream 53.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2015-08-23 23:53:22 Re: Declarative partitioning
Previous Message Greg Stark 2015-08-23 22:37:00 Re: PostgreSQL for VAX on NetBSD/OpenBSD