Pagination - 1 or 2 queries?

[CSN]

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".
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[scott.marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[CSN]

--- "scott.marlowe" <sc***********@ihs.com> 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.

Eclipse appears to just use pg_fetch_array($result,
$index). That'd be pretty similiar to a cursor
wouldn't it? i.e. only the specified rows would be
sent to the client (but all rows would be in the
server's memory).

Eclipse's docs make the argument that "b" is better
because "a" still needs to select/examine all rows
before doing the LIMIT and OFFSET.

http://www.students.cs.uu.nl/people/...api/index.html
(PagedQuery)

CSN

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[scott.marlowe]

On Fri, 5 Sep 2003, CSN wrote:

--- "scott.marlowe" <sc***********@ihs.com> 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.

Eclipse appears to just use pg_fetch_array($result,
$index). That'd be pretty similiar to a cursor
wouldn't it? i.e. only the specified rows would be
sent to the client (but all rows would be in the
server's memory).

Eclipse's docs make the argument that "b" is better
because "a" still needs to select/examine all rows
before doing the LIMIT and OFFSET.

If they aren't explicitly declaring a cursor, then b isn't exactly the
same. If you do:

select * from table order by fieldname

then

$row = pg_fetch_array()

then the whole data set is returned to the client (i.e. php) before we can
get the row. Now, if they do:

begin;
declare bubba as cursor for select * from table order by fieldname;
move forward 100 in bubba;
fetch 5 from bubba;
rollback;

Then you get the same kind of effect, but only 5 rows have to be retrieved
from the database to the client, and pg_fetch_array will now iterate over
those 5 rows only, and then run dry, so to speak.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruce Momjian]

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
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[CSN]

--- "scott.marlowe" <sc***********@ihs.com> wrote:

begin;
declare bubba as cursor for select * from table
order by fieldname;
move forward 100 in bubba;
fetch 5 from bubba;
rollback;

Then you get the same kind of effect, but only 5
rows have to be retrieved
from the database to the client, and pg_fetch_array
will now iterate over
those 5 rows only, and then run dry, so to speak.

Actually, with this method would you be able to get
the count of all rows that could be returned (not just
the 5)?

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[CSN]

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 <pg***@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
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[scott.marlowe]

On Fri, 5 Sep 2003, CSN wrote:

--- "scott.marlowe" <sc***********@ihs.com> wrote:

begin;
declare bubba as cursor for select * from table
order by fieldname;
move forward 100 in bubba;
fetch 5 from bubba;
rollback;

Then you get the same kind of effect, but only 5
rows have to be retrieved
from the database to the client, and pg_fetch_array
will now iterate over
those 5 rows only, and then run dry, so to speak.

Actually, with this method would you be able to get
the count of all rows that could be returned (not just
the 5)?

My previous one about using absolute count was wrong, btw, so you can
either fetch forward all and get the count that returns or run select
count(*). note that if you fetch forward all on a complex query, you may
NOT be able to fetch backward all since cursors have a hard time going
backwards on complex queries.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)