Re: Scrollable cursors and Sort performance

On Fri, 2006-02-10 at 10:13 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > Which shows that the *unnecessary* final merge takes 344 secs, adding
> > approximately 60% to the elapsed time of the query and nearly doubling
> > the CPU requirement.
>
> The merge step would certainly have to happen anyway, so this claim is
> not justified. You have not presented any evidence about how much of
> the reported time is actually I/O related.

You are right that the last read off disk and merge steps would still be
required, if the full result set were to be read. However, I am pointing
out a task in addition to that. Reading a large file from disk and then
writing it back down *when there is no benefit* seems like a task we
would want to avoid, whatever the size and however (sequential/random)
the I/Os are spent. We need not debate the CPU time differences.

The cited test sorted 1561238 data blocks, or 12.7 GB. Which at 120 MB/s
would take 100 seconds one-way on a stand-alone system. I think a very
large chunk of the elapsed time could reasonably be accounted for from
I/O costs, since it would need to read then write all of that data.

In the test, the post-sort retrieval of rows took 150 secs, indicating a
sequential retrieval rate of 85 MB/sec, so there is no reason to believe
that a slow disk over-emphasised performance loss for this case.

Best Regards, Simon Riggs