Subject: Re: getting count for a specific querry
From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@simnet.is>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <000001c53c3e$f4f5b730$797ba8c0@jfradkin>
References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin>
Content-Type: text/plain
Date: Fri, 08 Apr 2005 16:25:46 +0000
Message-Id: <1112977546.7447.39.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2005-04-08 at 09:29 -0400, Joel Fradkin wrote:

> Our app currently pulls a bunch of data to several query pages.
> 
> My idea is to use the limit and offset to return just the first 50
> records, if they hit next I can set the offset.
> 
> My understanding was this gets slower as you move further into the
> data, but we have several options to modify the search, and I do not
> believe our clients will page very far intro a dataset.

you might reduce the performance loss if your dataset is ordered by
a UNIQUE index.

select * from mytable where somecondition 
                      ORDER by uniquecol limit 50;

and next:

select * from mytable where somecondition AND uniquecol>? 
                      ORDER by uniquecol limit 50 OFFSET 50;

where the ? is placeholder for last value returned by last query.

if your unique index is a multi-column one, the method is slightly
more complicated, but the same idea.

gnari