Re: Utilizing multiple cores in a function call. - Mailing list 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
100-250ms.

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.
Shame..


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


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jean-David
Beyer
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
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    http://counter.li.org
  ^^-^^ 10:40:01 up 10 days, 21:29, 3 users, load average: 4.19, 4.22,
4.19

--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance