Re: Pagination - 1 or 2 queries?

Behind the scenes, is there much performance
difference between:

SELECT *
FROM table_with_millions_of_rows
ORDER BY col1;

and:

SELECT *
FROM table_with_millions_of_rows
ORDER BY col1
LIMIT 100
OFFSET 100000;

?

Wouldn't the second query would use far less memory?


CSN


--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> 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


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com