Re: Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

> On 07/07/2015 08:05 PM, Craig James wrote:
>>
>>
>> No ideas, but I ran into the same thing. I have a set of C/C++ functions
>> that put some chemistry calculations into Postgres as extensions (things
>> like, "calculate the molecular weight of this molecule"). As SQL
>> functions, the whole thing bogged down, and we never got the scalability
>> we needed. On our 8-CPU setup, we couldn't get more than 2 CPUs busy at
>> the same time, even with dozens of clients.

Hi all,

The sample code / results were put up last night at http://github.com/gbb/ppppt

Craig's problem sounds similar to my own, assuming he means running C indirectly via SQL vs running C more directly.
Lots of parallel connections to postgres but maximum 2 CPUs of scaling (and it gets worse, as you try to run more things).

Tom Lane has posted an interesting comment over on the bugs list which identies a likely source at least one of the problems, maybe both.
It seems to be linked to internal locking inside postgres (which makes sense, given the results - both problems feel 'lock-y').
Also, he mentions a workaround for some functions that scales to 8-way apparently.

http://www.postgresql.org/message-id/31265.1436317984@sss.pgh.pa.us

I think it's potentially a big problem for CPU intensive postgres libraries like pgrouting, or perhaps the postgis & postgis raster functions, things like that.
I don't know how well their functions are marked for e.g. immutability.
Are there any postgis devs on this list?

Graeme Bell