On Mon, 2009-06-29 at 14:42 -0400, Greg Smith wrote: > -Write a "worker server" that you prompt to pick up work from a table and > write its output to another that you can ask to handle part of the job. > You might communicate with the worker using the LISTEN/NOTIFY mechanism in > the database. > > -Some combination of these two techniques. One popular way to speed up > things that are running slowly is to run some part of them in a C UDF, so > that you could use "select my_big_computation(x,y,z)" and get faster > execution. The trouble here is that the backend may not like having threads suddenly introduced into its execution environment. If properly written, I don't really see why a C UDF that used pthreads couldn't spawn two worker threads that _NEVER_ touched _ANY_ PostgreSQL APIs, talked to the SPI, etc, and let them run while blocking the main thread until they complete. Then again, I know relatively little about Pg's guts, and for all I know initing the pthread environment could completely mess up the backend. Personally I'd want to do it out-of-process, using a SECURITY DEFINER PL/PgSQL function owned by a role that also owned some otherwise private queue and result tables for your worker server. As Greg Smith noted, LISTEN/NOTIFY would allow your worker server to avoid polling and instead sleep when there's nothing in the queue, and would also let your waiting clients avoid polling the result table. > For example, I've seen >10:1 speedups just be rewriting one small portion > of a computationally expensive mathematical function in C before, keeping > the rest of the logic on the database side. You don't necessarily have to > rewrite the whole thing. A useful dirty trick is to use Psyco in Python. It's a specializing compiler that can get massive performance boosts out of Python code without any code changes, and it seems to work with PL/Python. Just: try: import psyco psyco.full() except: # Enabing Pysco failed; don't care pass in your function should get you a pretty serious boost. This will NOT, however, allow your code to use two cores at once; you'll need threading or multiple processes for that. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
