Re: Pagination - 1 or 2 queries?

scott.marlowe wrote:
> On Fri, 5 Sep 2003, CSN wrote:
>
> > Since you usually need to know the total number of
> > rows a query would return, do you think it's better
> > to:
> >
> > a) Do one query with a LIMIT and OFFSET to get the
> > results, and another COUNT query to get the total
> > number of rows?
> >
> > b) Do a single query without a LIMIT and OFFSET, then
> > do a seek or similiar to get at the rows you want?
> >
> > Most tutorials, code, etc. I've seen do "a". The
> > eclipse library does "b".
>
> Either way works. Does the eclipse library use a cursor, or grab the
> whole dataset and then seek on the client side? If it uses a cursor, I'd
> expect it to be the fastest and simplest implementation. Since a lot of
> libs are designed to work with MySQL, they often are written in the first
> method, where select count(*) is quite quick on MySQL, and MySQL doesn't
> have cursor support.
>
> With Postgresql, the cursor is likely to be the faster method.

I agree --- with a LIMIT and COUNT(*), you run the query twice. With a
cursor, you run it once, and only pull the rows to the client you want.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073