Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

[Paul Tillotson]

Does postgres actually do multiple concurrent sorts within a single
backend?
     

Certainly.  Consider for example a merge join with each input being
sorted by an explicit sort step.  DISTINCT, ORDER BY, UNION, and
related operators require their own sort steps in the current
implementation. It's not difficult to invent queries that require
arbitrarily large numbers of sort steps.    
   

Tom, in Bruce's document on performance tuning, the page titled
"Multiple CPUs" states:
"POSTGRESQL uses a multi-process model, meaning each database connection
has its own Unix process...POSTGRESQL does not use multi-threading to
allow a single process to use multiple CPUs."
I took this to mean that PostgreSQL was not multi-threaded at all, and
that each connection was serviced by a single, non-threaded process.
Have I interpreted this incorrectly?  Are you saying that the backend
process actually is multi-threaded?  In the example you site, multiple
sorts could be accomplished serially in a non-threaded process.
 

Guy,
You understand correctly.  Each process is only running one query at 
once, but in terms of memory usage, several sorts are executing in 
parallel. 

For example, a merge join requires that both the left and right tables 
be sorted; as the join is being executed, both the left and right tables 
are being sorted.  (Why doesn't it sort one and then the other?  It 
would be a waste of memory to require that one of the [sorted] tables be 
kept in memory or written completely to the disk and then fetched 
later.  Instead, it just sorts them both as it goes along.)

However, this does mean that the amount of per-process memory being used 
for sorting will not vary with the "workload" of the database or number 
of people running that query (as each process only runs the query 
once).  The amount of per-process memory used will vary with the 
complexity of the query and the plan chosen by the planner.

Paul Tillotson
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly