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

Re: Utilizing multiple cores in a function call.

From: Jean-David Beyer <jeandavid8(at)verizon(dot)net>
To: pgsql performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Utilizing multiple cores in a function call.
Date: 2009-06-29 14:52:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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 6-9 
> 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 the
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 optimize
the actual code.

Next, you need to profile the code to see where the hot spots are. There is
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 one 
> 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 the
arguments off to the server. Thus, your SQL program would normally run on
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 SQL
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 server
process did its thing.

My guess is that you need a more efficient algorithm before you go to the
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. Some
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, 4.19

In response to


pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-06-29 14:53:33
Subject: Re: random slow query
Previous:From: Hartman, MatthewDate: 2009-06-29 14:26:40
Subject: Utilizing multiple cores in a function call.

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