Re: Gather Merge

From: Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
To: Neha Sharma <neha(dot)sharma(at)enterprisedb(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Kuntal Ghosh <kuntalghosh(dot)2007(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
Subject: Re: Gather Merge
Date: 2017-02-06 09:11:21
Message-ID: CAGPqQf0oO+Z+0a2JAK8xGv-6s3qjmc8n3PZmBCj44_Gzzqjo4g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 TPCH runs.

On Fri, Feb 3, 2017 at 5:56 PM, Neha Sharma <neha(dot)sharma(at)enterprisedb(dot)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 Names Line Coverage without Test cases Line Coverage with Test cases Function
> Coverage without Test cases Function Coverage with Test cases
> src/backend/executor/nodeGatherMerge.c 0.0 % 92.3 % 0.0 % 92.3 %
> src/backend/commands/explain.c 65.5 % 68.4 % 81.7 % 85.0 %
> src/backend/executor/execProcnode.c 92.50% 95.1 % 100% 100.0 %
> src/backend/nodes/copyfuncs.c 77.2 % 77.6 % 73.0 % 73.4 %
> src/backend/nodes/outfuncs.c 32.5 % 35.9 % 31.9 % 36.2 %
> src/backend/nodes/readfuncs.c 62.7 % 68.2 % 53.3 % 61.7 %
> src/backend/optimizer/path/allpaths.c 93.0 % 93.4 % 100 % 100%
> src/backend/optimizer/path/costsize.c 96.7 % 96.8 % 100% 100%
> src/backend/optimizer/plan/createplan.c 89.9 % 91.2 % 95.0 % 96.0 %
> src/backend/optimizer/plan/planner.c 95.1 % 95.2 % 97.3 % 97.3 %
> src/backend/optimizer/plan/setrefs.c 94.7 % 94.7 % 97.1 % 97.1 %
> src/backend/optimizer/plan/subselect.c 94.1 % 94.1% 100% 100%
> src/backend/optimizer/util/pathnode.c 95.6 % 96.1 % 100% 100%
> src/backend/utils/misc/guc.c 67.4 % 67.4 % 91.9 % 91.9 %
>
> On Wed, Feb 1, 2017 at 7:02 PM, Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
> wrote:
>
>> Due to recent below commit, patch not getting apply cleanly on
>> master branch.
>>
>> commit d002f16c6ec38f76d1ee97367ba6af3000d441d0
>> Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)lathia(at)gmail(dot)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(dot)paquier(at)gmail(dot)com> wrote:
>>>
>>>> On Mon, Jan 23, 2017 at 6:51 PM, Kuntal Ghosh
>>>> <kuntalghosh(dot)2007(at)gmail(dot)com> wrote:
>>>> > On Wed, Jan 18, 2017 at 11:31 AM, Rushabh Lathia
>>>> > <rushabh(dot)lathia(at)gmail(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>>
>
>
> --
>
> Regards,
>
> Neha Sharma
>

--
Rushabh Lathia

Attachment Content-Type Size
without_gm.tar.gz application/x-gzip 133.6 KB
with_gm.tar.gz application/x-gzip 146.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2017-02-06 09:36:26 Re: \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)
Previous Message Amit Langote 2017-02-06 09:01:58 Re: contrib modules and relkind check