Re: Performance tuning on RedHat Enterprise Linux 3

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