Skip site navigation (1) Skip section navigation (2)

Re: Utilizing multiple cores in a function call.

From: "Hartman, Matthew" <Matthew(dot)Hartman(at)krcc(dot)on(dot)ca>
To: "Jean-David Beyer" <jeandavid8(at)verizon(dot)net>, "pgsql performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Utilizing multiple cores in a function call.
Date: 2009-06-29 15:01:57
Message-ID: 366642367C5B354197A1E0D27BC175BD022597D8@KGHMAIL.KGH.ON.CA (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I'm pretty much at that point where I've chewed the fat off of the
algorithm, or at least at my personal limits. Occasionally a new idea
pops into my head and yields an improvement but it's in the order of

Google came back with "no sir". It seems PostgreSQL is limited to one
CPU per query unless I spawn a master/controller like you suggested.

Matthew Hartman
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294 

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Jean-David
Sent: Monday, June 29, 2009 10:53 AM
To: pgsql performance
Subject: Re: [PERFORM] Utilizing multiple cores in a function call.

Hartman, Matthew wrote:
> Good morning.
> I have developed a function call that schedules patient appointments 
> within a day based on several resource constraints. The algorithm has 
> been mentioned on here before and I have managed to tweak it down to
> seconds from the original 27 seconds.
To speed up the execution of processes, I heartily recommend the book,
"Writing Efficient Programs" by Jon Louis Bentley, Prentice-Hall, 1982.

There are many important steps. The most important is usually to refine
algorithm itself. I once speeded up a program that would have required
several weeks on a main frame running 24/7 to 6 minutes by improving the
basic algorithm of the thing. Only then would it have made sense to
the actual code.

Next, you need to profile the code to see where the hot spots are. There
little point to examining code in other parts of the program.
> Of course, I want it to be faster still. The function throttles one of

> my CPUs to 100% (shown as 50% in Task Manager) and leaves the other
> sitting pretty. Is there any way to use both CPUs?
You could write your algorithm as a separate process -- a server.
Then in you SQL program, you invoke a trivial function that just hands
arguments off to the server. Thus, your SQL program would normally run
one processor and the time-consuming algorithm would run on the other.

If you are not careful, this would not benefit you at all because your
process would wait until the server returns its answer. So you would
need to
modify your SQL program so that it could do other things while the
process did its thing.

My guess is that you need a more efficient algorithm before you go to
trouble of optimizing the execution of your current one. As far as
making it
run on multiple processors, it depends critically on the nature of your
algorithm. A few can easily be modified to run on multiple processors.
cannot run on multiple processors at all.
> Thanks,
> Matthew Hartman
> Programmer/Analyst
> Information Management, ICP
> Kingston General Hospital
> (613) 549-6666 x4294

   .~.  Jean-David Beyer          Registered Linux User 85642.
   /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
  /( )\ Shrewsbury, New Jersey
  ^^-^^ 10:40:01 up 10 days, 21:29, 3 users, load average: 4.19, 4.22,

Sent via pgsql-performance mailing list
To make changes to your subscription:

In response to


pgsql-performance by date

Next:From: Joe ConwayDate: 2009-06-29 18:20:21
Subject: Re: Utilizing multiple cores in a function call.
Previous:From: Merlin MoncureDate: 2009-06-29 15:00:36
Subject: Re: [PERFORM] Terrible Write Performance of a Stored Procedure

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group