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-31T03:43:09Z
Lists: pgsql-hackers

Attachments

On Tue, Mar 28, 2017 at 11:11 AM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:
> 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
> --

Before moving to higher scale I tried playing around work_mem effects
on this patch and came across following results,
All settings are kept as before with the exception of work_mem that is
set to 64MB.

Most of the queries showed similar performance except a few, details
are as follows,
(all time are given in ms)
Query | Head    | Patch
---------+----------+--------
 Q8     | 8720     | 8839
 Q18   | 370710 | 384347
 Q21   | 53270   | 65189

Clearly, regression in Q8 and Q18 is minor but that in Q21 is
significant. Just to confirm, I have applied the tweak mentioned in
[1] as before,
For the explain analyse output of Q21 on head and with patch, please
check the attached file.

 [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