Postgres Pro
Downloads
Home   >   mailing lists

Thread: cpu-intensive immutable function and parallel scan

cpu-intensive immutable function and parallel scan

From
Niels Jespersen
Date:

Hello all

 

I have a cpu-intensive plpython3u function that computes a result on the value from a single column value from a simple select.This looks largely like this.

 

select function_name (t1.val1, 'constant1') from t1 where t1.p = '202012_1' and t1.val1 is not null;

 

The function is marked immutable and parallel safe.

 

The time spent in the function is quite substantial. The same execution plan for a query that invokes the funtion and a query that does not results in a 15-times slowdown. Explain analyze show that both queries use 5 parallel workers.

 

I think I could achieve some speedup by parallelizing further, distributing the cpu-work among additional workers. But, how do I achive that best?

 

In Oracle I would either use a parallel-hint or an alter session force parallel query parallel 8.

 

Regards Niels

 

Re: cpu-intensive immutable function and parallel scan

From
David Rowley
Date:
On Tue, 22 Jun 2021 at 19:06, Niels Jespersen <NJN@dst.dk> wrote:
> I think I could achieve some speedup by parallelizing further, distributing the cpu-work among additional workers.
But,how do I achive that best?
 

You'll want to ensure max_parallel_workers_per_gather is set high
enough and you have max_parallel_workers set to something high enough.
You can then do:

alter table t1 set (parallel_workers = <number of workers>);

David

Re: cpu-intensive immutable function and parallel scan

From
Tom Lane
Date:
David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 22 Jun 2021 at 19:06, Niels Jespersen <NJN@dst.dk> wrote:
>> I think I could achieve some speedup by parallelizing further, distributing the cpu-work among additional workers.
But,how do I achive that best? 

> You'll want to ensure max_parallel_workers_per_gather is set high
> enough and you have max_parallel_workers set to something high enough.
> You can then do:
> alter table t1 set (parallel_workers = <number of workers>);

Also, if you don't have a fairly high COST value set on the function,
try raising that to make the planner understand that it's expensive.
My recollection is that the amount of parallelism it goes for is partly
predicated on the estimated query cost.

            regards, tom lane

SV: cpu-intensive immutable function and parallel scan

From
Niels Jespersen
Date:
>Fra: David Rowley <dgrowleyml@gmail.com> Sendt: 22. juni 2021 09:10
>Emne: Re: cpu-intensive immutable function and parallel scan
>On Tue, 22 Jun 2021 at 19:06, Niels Jespersen <NJN@dst.dk> wrote:

>> I think I could achieve some speedup by parallelizing further, distributing the cpu-work among additional workers.
But,how do I achive that best?
 
>You'll want to ensure max_parallel_workers_per_gather is set high enough and you have max_parallel_workers set to
somethinghigh enough.
 
>You can then do:
>alter table t1 set (parallel_workers = <number of workers>);

Thank you. That helped. A lot. 
/Niels