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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073