Thread: Re: Cursors with Large, Ordered Result Sets

Thanks for the quick reply and sorry for the slow response.

Yes, this is very similar to what we are currently doing and it seems
to be working rather well - much to my surprise. However, I still have
the same questions regarding the actual steps being taken by the DBMS
to order a massive result set. Doesn't it need to have the entire
result in memory before it can return the first records? If so, and
the result is larger than the RAM, does it dump it to disk and then
sort?

The reason I am so curios is simply because this is running on a
mission-critical machine and I need to know what resources
(particularly disk space) will be consumed.

Thanks for your help,

Rupert

CoL <col@mportal.hu> wrote in message news:<b4h7kn$22ml$1@news.hub.org>...
> In first view, how about using offset and limit?
>
> select ... order by field offset 0 limit 10
> cursor fetch ... if(data < 10*1024)
> select ... order by field offset 10 limit 10
> cursor fetch ... if(data < 10*1024)
> select ... order by field offset 20 limit 10
> ....
> C.

On 27 Mar 2003, Rupert wrote:

> Thanks for the quick reply and sorry for the slow response.
>
> Yes, this is very similar to what we are currently doing and it seems
> to be working rather well - much to my surprise. However, I still have
> the same questions regarding the actual steps being taken by the DBMS
> to order a massive result set. Doesn't it need to have the entire
> result in memory before it can return the first records? If so, and

Whether or not you need to sort depends on the query.

For example:

select * from foo order by a;

could choose to use an index on foo.a to do the ordering rather than a
sort.  It'll choose based on what it thinks will be the best plan.

> the result is larger than the RAM, does it dump it to disk and then
> sort?

It's based on the sort_mem GUC parameter and will go to disk if necessary.