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

On Thu, Jul 9, 2015 at 4:44 AM, Graeme B. Bell <graeme(dot)bell(at)nibio(dot)no> wrote:
> On 09 Jul 2015, at 05:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> If you
>> write your is_prime function purely in plpgsql, and don't bother to mark
>> it nonvolatile, *it will not scale*.
>
>> much for properly written plpgsql; but there's an awful lot of bad plpgsql
>> code out there, and it can make a huge difference for that.
>
>
> Hi Tom,
>
> I object to phrases like 'don't bother to mark it' and 'bad plpgsql' here. That is putting the blame on programmers. Clearly, if there is no end of code out there that isn't right in this regard, there's something very wrong in the project documentation.
>
> 1. I have been writing pl/pgsql on and off for a couple of years now and I've read quite a bit of the postgres doumentation, but I don't recall seeing a clear statement telling me I should mark pl/pgsql functions nonvolatile wherever possible or throw all performance and scalability out the window. I'm sure there may be a line hidden somewhere in the docs, but judging from the impact it has in practice, this seems like a very fundamental concept that should be repeatedly and clearly marked in the docs.
>
> 2. Furthermore, I have never come across anything in the documentation that made it clear to me that any pl/pgsql function I write will, by default, be taking out locks for every single statement in the code. I've written code in I dunno, maybe 15-20 different languages in my life, and I can't think of another language offhand that does that by default. From the reactions on this thread to this benchmark and the par_psql benchmarks, it doesn't seem that it was even immediately obvious to many postgres enthusiasts and developers.
>
> 3. I don't disagree that the benchmark code is objectively 'bad' in the sense that it is missing an important optimisation.

Particularly with regards documentation, a patch improving things is
much more likely to improve the situation than griping. Also,
conversation on this list gets recorded for posterity and google is
remarkably good at matching people looking for problems with
solutions. So, even in absence of a patch perhaps we've made the
lives of future head-scratchers a little bit easier with this
discussion.

merlin