Re: [HACKERS] Gather Merge - Mailing list pgsql-hackers

From Rushabh Lathia
Subject Re: [HACKERS] Gather Merge
Date
Msg-id CAGPqQf2YFWeM4-KMAJu6ZAYFXVWmomyXuMnVyDhBVQY8xrUYVg@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] Gather Merge  (Neha Sharma <neha.sharma@enterprisedb.com>)
List pgsql-hackers
Thanks Neha for the test LCOV report.

I run the tpch on scale 10 with the latest patch and with latest code
up to 1st Feb (f1169ab501ce90e035a7c6489013a1d4c250ac92).

- max_worker_processes = DEFAULT (8)
- max_parallel_workers_per_gather = 4
- Cold cache environment is ensured. With every query execution - server is
  stopped and also OS caches were dropped.
- power2 machine with 512GB of RAM

Here are the results: I did the three run and taken median. First
timing is without patch and 2nd is with GM.

Query 3: 45035.425   - 43935.497
Query 4: 7098.259    - 6651.498
Query 5: 37114.338   - 37605.579
Query 9: 87544.144   - 44617.138
Query 10: 43810.497  - 37133.404
Query 12: 20309.993  - 19639.213
Query 15: 61837.415  - 60240.762
Query 17: 134121.961 - 116943.542
Query 18: 248157.735 - 193463.311
Query 20: 203448.405 - 166733.112

Also attaching the output of those TPCH runs.



On Fri, Feb 3, 2017 at 5:56 PM, Neha Sharma <neha.sharma@enterprisedb.com> wrote:
Hi, 

I have done some testing with the latest patch

1)./pgbench postgres -i -F 100 -s 20
2) update pgbench_accounts set filler = 'foo' where aid%10 = 0;
3) vacuum analyze pgbench_accounts;
4) set max_parallel_workers_per_gather = 4;
5) set max_parallel_workers = 4;

Machine Configuration :-
RAM    :- 16GB
VCPU  :- 8 
Disk     :- 640 GB
 
Test case script with out-file attached.

LCOV Report :- 

File NamesLine Coverage without Test casesLine Coverage with Test casesFunction Coverage without Test casesFunction Coverage with Test cases
src/backend/executor/nodeGatherMerge.c0.0 %92.3 %0.0 %92.3 %
src/backend/commands/explain.c65.5 %68.4 %81.7 %85.0 %
src/backend/executor/execProcnode.c92.50%95.1 %100%100.0 %
src/backend/nodes/copyfuncs.c77.2 %77.6 %73.0 %73.4 %
src/backend/nodes/outfuncs.c32.5 %35.9 %31.9 %36.2 %
src/backend/nodes/readfuncs.c62.7 %68.2 %53.3 %61.7 %
src/backend/optimizer/path/allpaths.c93.0 %93.4 %100 %100%
src/backend/optimizer/path/costsize.c96.7 %96.8 %100%100%
src/backend/optimizer/plan/createplan.c89.9 %91.2 %95.0 %96.0 %
src/backend/optimizer/plan/planner.c95.1 %95.2 %97.3 %97.3 %
src/backend/optimizer/plan/setrefs.c94.7 %94.7 %97.1 %97.1 %
src/backend/optimizer/plan/subselect.c94.1 %94.1%100%100%
src/backend/optimizer/util/pathnode.c95.6 %96.1 %100%100%
src/backend/utils/misc/guc.c67.4 %67.4 %91.9 %91.9 %

On Wed, Feb 1, 2017 at 7:02 PM, Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
Due to recent below commit, patch not getting apply cleanly on
master branch.

commit d002f16c6ec38f76d1ee97367ba6af3000d441d0
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon Jan 30 17:15:42 2017 -0500

    Add a regression test script dedicated to exercising system views.

Please find attached latest patch.



On Wed, Feb 1, 2017 at 5:55 PM, Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
I am sorry for the delay, here is the latest re-based patch.

my colleague Neha Sharma, reported one regression with the patch, where
explain output for the Sort node under GatherMerge was always showing
cost as zero:

explain analyze select '' AS "xxx" from pgbench_accounts  where filler like '%foo%' order by aid;
                                                                   QUERY PLAN                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=47169.81..70839.91 rows=197688 width=36) (actual time=406.297..653.572 rows=200000 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   ->  Sort  (cost=0.00..0.00 rows=0 width=0) (actual time=368.945..391.124 rows=40000 loops=5)
         Sort Key: aid
         Sort Method: quicksort  Memory: 3423kB
         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..42316.60 rows=49422 width=36) (actual time=296.612..338.873 rows=40000 loops=5)
               Filter: (filler ~~ '%foo%'::text)
               Rows Removed by Filter: 360000
 Planning time: 0.184 ms
 Execution time: 734.963 ms

This patch also fix that issue.




On Wed, Feb 1, 2017 at 11:27 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Mon, Jan 23, 2017 at 6:51 PM, Kuntal Ghosh
<kuntalghosh.2007@gmail.com> wrote:
> On Wed, Jan 18, 2017 at 11:31 AM, Rushabh Lathia
> <rushabh.lathia@gmail.com> wrote:
>>
> The patch needs a rebase after the commit 69f4b9c85f168ae006929eec4.

Is an update going to be provided? I have moved this patch to next CF
with "waiting on author" as status.
--
Michael



--
Rushabh Lathia



--
Rushabh Lathia


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers




--

Regards,

Neha Sharma



--
Rushabh Lathia
Attachment

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: [HACKERS] [RFC] Should I embed or parameterize syscall/Win32function names from error messages?
Next
From: Amit Kapila
Date:
Subject: Re: [HACKERS] Add pgstathashindex() to get hash index table statistics.