Horton

Re: WIP: [[Parallel] Shared] Hash

Rafia Sabih <rafia.sabih@enterprisedb.com>

View thread View on postgr.es
From: Rafia Sabih <rafia.sabih@enterprisedb.com>
To: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Andres Freund <andres@anarazel.de>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Haribabu Kommi <kommi.haribabu@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>, Robert Haas <robertmhaas@gmail.com>
Date: 2017-03-28T05:41:03Z
Lists: pgsql-hackers

Attachments

On Mon, Mar 27, 2017 at 12:20 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
>
> On Sun, Mar 26, 2017 at 3:56 PM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
> > But... what you said above must be a problem for Windows.  I believe
> > it doesn't allow files to be unlinked if they are open, and I see that
> > DSM segments are cleaned up in resowner's phase ==
> > RESOURCE_RELEASE_BEFORE_LOCKS and files are closed in phase ==
> > RESOURCE_RELEASE_AFTER_LOCKS.
>
> I thought this last point about Windows might be fatal to my design,
> but it seems that Windows since at least version 2000 has support for
> Unixoid unlinkability via the special flag FILE_SHARE_DELETE.

On testing v10 of this patch over commit
b54aad8e34bd6299093e965c50f4a23da96d7cc3 and applying the tweak
mentioned in [1], for TPC-H queries I found the results quite
encouraging,

Experimental setup:
TPC-H scale factor - 20
work_mem = 1GB
shared_buffers = 10GB
effective_cache_size = 10GB
random_page_cost = seq_page_cost = 0.1
max_parallel_workers_per_gather = 4

Performance numbers:
(Time in seconds)
Query  |  Head | Patch |
-------------------------------
Q3       | 73       | 37      |
Q5       | 56       | 31      |
Q7       | 40       | 30      |
Q8       | 8         | 8        |
Q9       | 85       | 42      |
Q10     | 86       | 46      |
Q14     | 11       | 6        |
Q16     | 32       | 11      |
Q21     | 53       | 56      |

Please find the attached file for the explain analyse output of these
queries on head as well as patch.
Would be working on analysing the performance of this patch on 300 scale factor.

[1] https://www.postgresql.org/message-id/flat/CAEepm%3D270ze2hVxWkJw-5eKzc3AB4C9KpH3L2kih75R5pdSogg%40mail.gmail.com
-- 
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/

Commits

  1. Add shared tuplestores.

  2. Remove BufFile's isTemp flag.

  3. Provide DSM segment to ExecXXXInitializeWorker functions.

  4. Optimize joins when the inner relation can be proven unique.

  5. Fix failure to use clamp_row_est() for parallel joins.

  6. Document lack of validation when attaching foreign partitions.

  7. Fix inclusions of c.h from .h files.

  8. Fix inclusions of postgres_fe.h from .h files.

  9. Bring plpgsql into line with header inclusion policy.

  10. Document intentional violations of header inclusion policy.

  11. Shut down Gather's children before shutting down Gather itself.

  12. btree: Support parallel index scans.

  13. Add explicit ORDER BY to a few tests that exercise hash-join code.

  14. Revise hash join code so that we can increase the number of batches

  15. Rewrite hash join to use simple linked lists instead of a