Re: Performance tuning on RedHat Enterprise Linux 3

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Paul Tillotson <pntil(at)shentel(dot)net> writes:
> > 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.

I think there's a bit of misunderstanding here. He's talking about two sorts
actually being executed in parallel. I don't think Postgres actually does that
even if there are multiple sorts in the plan. Postgres isn't threaded (either
manually or via OS threads) and Postgres's sort isn't incremental and doesn't
return any tuples to the outer nodes until it's completely finished sorting
(it's not bubble sort or selection sort:).

However a sort step still takes up memory after it's finished executing
because it has to store the ordered tuples. So a merge join joining two sorted
tables needs to do the sort on one and then keep around the tuples, and do the
sort on the second and keep around the tuples for that one too.

I think the actual sort algorithm used can consume up to 3x the space of just
the sorted tuples. But I'm not really sure on that, nor am I sure whether that
space is reclaimed once the actual execution is done.

--
greg