From: Tom Lane <tgl@sss.pgh.pa.us>
To: andres@anarazel.de
Cc: Craig James <cjames@emolecules.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Graeme B. Bell <graeme.bell@nibio.no>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
Date: Wed, 08 Jul 2015 23:38:38 -0400
Message-ID: <15702.1436413118@sss.pgh.pa.us> (raw)
In-Reply-To: <20150708224518.GT10242@alap3.anarazel.de>
References: <6E55113B-FA05-433C-9B21-623F798EE935@skogoglandskap.no>
<CAFwQ8rcEOiGJ79WtoO=uTX33ZhKVUWG9v8iO14TR+AoT_wd1og@mail.gmail.com>
<559CB5BF.2080509@commandprompt.com>
<CAFwQ8rdWcZwUxgQDySOzFAqHJPm6=DVH+bp0XMJzmADF1GVmPw@mail.gmail.com>
<CAHyXU0wO_WMzrbdcdtWwpk-78j_4P-M-Kr0xOyJVg1K15dQNAQ@mail.gmail.com>
<20150708202733.GS10242@alap3.anarazel.de>
<CAFwQ8rcmN5APWKS8v9JBqvQ8_x2by276LFBZxtci-32BnuMGUg@mail.gmail.com>
<20150708224518.GT10242@alap3.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
andres@anarazel.de (Andres Freund) writes:
> On 2015-07-08 15:38:24 -0700, Craig James wrote:
>> From my admittedly naive point of view, it's hard to see why any of this
>> matters. I have functions that do purely CPU-intensive mathematical
>> calculations ... you could imagine something like is_prime(N) that
>> determines if N is a prime number. I have eight clients that connect to
>> eight backends. Each client issues an SQL command like, "select
>> is_prime(N)" where N is a simple number.
> I mostly replied to Merlin's general point (additionally in the context of
> plpgsql).
> But I have a hard time seing that postgres would be the bottleneck for a
> is_prime() function (or something with similar characteristics) that's
> written in C where the average runtime is more than, say, a couple
> thousand cyles. I'd like to see a profile of that.
But that was not the case that Graeme was complaining about. He's talking
about simple-arithmetic-and-looping written in plpgsql, in a volatile
function that is going to take a new snapshot for every statement, even if
that's only "n := n+1". So it's going to spend a substantial fraction of
its runtime banging on the ProcArray, and that doesn't scale. If you
write your is_prime function purely in plpgsql, and don't bother to mark
it nonvolatile, *it will not scale*. It'll be slow even in single-thread
terms, but it'll be particularly bad if you're saturating a multicore
machine with it.
One of my Salesforce colleagues has been looking into ways that we could
decide to skip the per-statement snapshot acquisition even in volatile
functions, if we could be sure that a particular statement isn't going to
do anything that would need a snapshot. Now, IMO that doesn't really do
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.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, cjames@emolecules.com, mmoncure@gmail.com, jd@commandprompt.com, graeme.bell@nibio.no
Subject: Re: Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
In-Reply-To: <15702.1436413118@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox