On Tue, Mar 28, 2017 at 11:11 AM, Rafia Sabih <rafia.sa...@enterprisedb.com> wrote: > On Mon, Mar 27, 2017 at 12:20 PM, Thomas Munro > <thomas.mu...@enterprisedb.com> wrote: >> >> On Sun, Mar 26, 2017 at 3:56 PM, Thomas Munro >> <thomas.mu...@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/
q21_reg_ph.tar.gz
Description: GNU Zip compressed data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
