Re: [HACKERS] why not parallel seq scan for slow functions

On Fri, Mar 16, 2018 at 3:36 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Wed, Mar 14, 2018 at 12:01 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> 0003 introduces a new upper relation to represent the result of
>> applying the scan/join target to the topmost scan/join relation. I'll
>> explain further down why this seems to be needed. Since each path
>> must belong to only one relation, this involves using
>> create_projection_path() for the non-partial pathlist as we already do
>> for the partial pathlist, rather than apply_projection_to_path().
>> This is probably marginally more expensive but I'm hoping it doesn't
>> matter. (However, I have not tested.)
>>
>
> I think in the patch series this is the questionable patch wherein it
> will always add an additional projection path (whether or not it is
> required) to all Paths (partial and non-partial) for the scanjoin rel
> and then later remove it (if not required) in create_projection_plan.
> As you are saying, I also think it might not matter much in the grand
> scheme of things and if required we can test it as well,
>

I have done some tests to see the impact of this patch on planning
time. I took some simple statements and tried to compute the time
they took in planning.

Test-1
----------
DO $$
DECLARE count integer;
BEGIN
For count In 1..1000000 Loop
Execute 'explain Select ten from tenk1';
END LOOP;
END;
$$;

In the above block, I am explaining the simple statement which will
have just one path, so there will be one additional path projection
and removal cycle for this statement. I have just executed the above
block in psql by having \timing option 'on' and the average timing for
ten runs on HEAD is 21292.388 ms, with patches (0001.* ~ 0003) is
22405.2466 ms and with patches (0001.* ~ 0005.*) is 22537.1362. These
results indicate that there is approximately 5~6% of the increase in
planning time.

Test-2
----------
DO $$
DECLARE count integer;
BEGIN
For count In 1..1000000 Loop
Execute 'explain select hundred,ten from tenk1 order by hundred';
END LOOP;
END;
$$;

In the above block, I am explaining the statement which will have two
paths, so there will be two additional path projections and one
removal cycle for one of the selected paths for this statement. The
average timing for ten runs on HEAD is 32869.8343 ms, with patches
(0001.* ~ 0003) is 34068.0608 ms and with patches (0001.* ~ 0005.*) is
34097.4899 ms. These results indicate that there is approximately
3~4% of the increase in optimizer time. Now, ideally, this test
should have shown more impact as we are adding additional projection
path for two paths, but I think as the overall time for planning is
higher, the impact of additional work is not much visible.

I have done these tests on the Centos VM, so there is some variation
in test results. Please find attached the detailed results of all the
tests. I have not changed any configuration for these tests. I think
before reaching any conclusion, it would be better if someone repeats
these tests and see if they also have a similar observation. The
reason for doing the tests separately for first three patches (0001.*
~ 0003.*) is to see the impact of changes without any change related
to parallelism.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com