Re: Takes too long to fetch the data from database

On 4/21/06, soni de <soni(dot)de(at)gmail(dot)com> wrote:
>
> I don't want to query exactly 81900 rows into set. I just want to fetch 50
> or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 rows
> starting from last to end).

aha! you need to implement a 'sliding window' query. simplest is
when you are ordering one field that is unique:

1st 50:
select * from t order by k limit 50;
2nd 50:
select * from t where k > k1 order by k limit 50:

if you are ordering on two fields or on a field that is not unique, you must do:

1st 50:
select * from t order by j, k limit 50;
2nd 50:
select * from t where j >= j1 and (j > j1 or k > k1) order by j, k limit 50;
3 fields:
select * from t where i >= i1 and (i > i1 or j >= j1) and (i > i1 or j
> k1 or k > k1) order by i,j,k limit 50;

i1,j1,k1 are the values of the 50th record you pulled out of the last query.

if this seems a little complicated, either wait for pg 8.2 or get cvs
tip and rewrite as:
select * from t where (i,j,k) > (i1,j1,k1) order by i,j,k limit 50;