Skip site navigation (1) Skip section navigation (2)

Re: loading only few rows from huge table

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Mark <sendmailtomark(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: loading only few rows from huge table
Date: 2004-12-29 17:38:35
Message-ID: 20041229173835.GA10107@wolff.to (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, Dec 29, 2004 at 06:54:31 -0800,
  Mark <sendmailtomark(at)yahoo(dot)com> wrote:

In general you should keep replies copied back to the list so that other
people can learn from and contribute to the discussion.

Responding to messages in line makes it easier for people to follow the
discussion. I will mangle your reply to make it easier for other people
to help with your followup question.

> --- Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
> 
> > On Wed, Dec 22, 2004 at 11:31:59 -0800,
> >   Mark <sendmailtomark(at)yahoo(dot)com> wrote:
> > > Hi,
> > > I have a table with 100K rows. One of columns is a timestamp and
> > > indicates when this row inserted.
> > > 
> > > What will the the best way of getting 10 latest rows from that
> > table
> > > and  introducing partial data retrieval (rows 50-60, 100- 120,
> > etc)
> > 
> > If there is an index on the timestamp column you can efficiently
> > get
> > the 10 rows with the latest timestamps by using ORDER BY and LIMIT.
> > Getting rows out of the middle of the table by row number
> > efficiently is
> > going to be harder. You can use OFFSET to get groups of records
> > starting
> > from number other than the latest. As the offset gets bigger this
> > will
> > run slower. If the timestamps are unique and you always continue
> > from
> > where you left off and you can save the last timestamp you
> > retrieved, then
> > you can use a WHERE clause to get the offset efficiently.

> Thanks this will be fine for C/C++ client side.
> Will setMaxRows of java.sql.Statement class do the same job ?

I am not familiar with the java interface and don't know the answer to this.

In response to

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2004-12-29 18:19:24
Subject: Re: update table with row locking
Previous:From: Joshua D. DrakeDate: 2004-12-29 17:06:18
Subject: Re: BCD format

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group