Re: Scrollable cursors and Sort performance - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: Scrollable cursors and Sort performance
Date
Msg-id 1139587962.1258.540.camel@localhost.localdomain
Whole thread Raw
In response to Re: Scrollable cursors and Sort performance  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Scrollable cursors and Sort performance
List pgsql-hackers
On Fri, 2006-02-10 at 10:13 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.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




pgsql-hackers by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: PostgreSQL 8.0.6 crash
Next
From: "Jim C. Nasby"
Date:
Subject: Re: Scrollable cursors and Sort performance