Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

Hello

apart from the increasing OFFSET method, you only need to
traverse the results sequentially, you can do a variant of
this:

let us assume your resultset has a a unique column pk, and is ordered on
column o:

initial select:
select * from foo order by o limit 10;

next page
select * from foo where (o,pk)>(o,?) order by o limit 10;
(where the ? is the last pk value in previous select)

this method will be able to make use of an index on (o,pk)

gnari

use a dynamic select in the web page

$1 = 10
$2 = 5

select * from mytable limit $1 OFFSET $2

--- On Fri, 6/27/08, Bill Thoen <bthoen@gisnet.com> wrote:
From: Bill Thoen <bthoen@gisnet.com>
Subject: [GENERAL] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
To: pgsql-general@postgresql.org
Date: Friday, June 27, 2008, 8:14 PM

What I want to do is present the results of a query in a web page, but
only 10 rows at a time. My PostgreSQL table has millions of records and
if I don't add a LIMIT 10 to the SQL selection, the request can take too
long. The worst case scenario is when the user requests all records
without adding filtering conditions (e.g. SELECT * FROM MyTable;) That
can take 10-15 minutes, which won't work on a web application.

What I'm wondering is how in PostgreSQL do you select only the first 10
records from a selection, then the next 10, then the next, and possibly
go back to a previous 10? Or do you do the full selection into a
temporary table once, adding a row number to the columns and then
performing sub-selects on that temporary table using the row id? Or do
you run the query with Limit 10 set and then run another copy with no
limit into a temporary table while you let the user gaze thoughtfully at
the first ten records?

I know how to get records form the database into a web page, and I know
how to sense user actions (PageDown, PageUp, etc.) so I'm basically
looking for techniques to extract the data quickly.

Also, if this isn't the best forum to ask this sort of question, I'd
appreciate being pointed to a more appropriate one.

TIA,

- Bill Thoen

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 6/27/08, Bill Thoen <bthoen@gisnet.com> wrote:

What I want to do is present the results of a query in a web page, but only
10 rows at a time. My PostgreSQL table has millions of records and if I
don't add a LIMIT 10 to the SQL selection, the request can take too long.
The worst case scenario is when the user requests all records without adding
filtering conditions (e.g. SELECT * FROM MyTable;) That can take 10-15
minutes, which won't work on a web application.

Also note, that huge OFFSET value can slow down the query as much as if you
weren't using LIMIT at all.

--
Sincerely yours,
Olexandr Melnyk <><
http://omelnyk.net/

On Fri, Jun 27, 2008 at 2:09 PM, Bill Thoen <bthoen@gisnet.com> wrote:

Thanks for tip on OFFSET. That's just what I needed. It's so easy when you
know the command you're looking for, and so hard when you know what you want
to do but don't know what the command is called!

I would strongly suggest taking a second look at Ragnar's suggestion.
It may be a bit more difficult to implement, but I is a clear
performance winner when you start to deal with large datasets and the
OFFSET get really big.

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Thanks for tip on OFFSET. That's just what I needed. It's so easy when
you know the command you're looking for, and so hard when you know what
you want to do but don't know what the command is called!

Thanks,
- Bill Thoen

Bill Thoen wrote:

What I'm wondering is how in PostgreSQL do you select only the first 10
records from a selection, then the next 10, then the next, and possibly
go back to a previous 10?

LIMIT with OFFSET has already been mentioned. There's another option if
your web app is backed by an application server or some other
environment that can retain resources across client queries: You can use
a scrollable database cursor to access the results.

This won't do you much (any?) good if your web app has to establish a
connection or get one from the pool for every request. It's only really
useful if you can store the connection in the user's session information.

Using cursors probably isn't very good for very high user counts,
because abandoned sessions will hold their database connections until
the session times out and is destroyed. For more complex apps with fewer
users, though, cursors could be a big win.

Note that OFFSET isn't free either. The database server must still
execute all of the query up to OFFSET+LIMIT results. With a high offset,
that can get very slow. A cursor will be OK here if you still start from
the beginning, but if you ever begin with a high offset you'll want to
look into using one of the methods suggested in other replies that
permit you to use an index.

--
Craig Ringer

On Fri, Jun 27, 2008 at 08:22:35PM +0000, Ragnar wrote:

let us assume your resultset has a a unique column pk, and is ordered on
column o:

next page
select * from foo where (o,pk)>(o,?) order by o limit 10;
(where the ? is the last pk value in previous select)

this method will be able to make use of an index on (o,pk)

Hum, I think I must be missing something. I'm not sure why you're
comparing 'o' to itself and you're not putting any ordering constraint
on the primary key. I think the query should look closer to:

SELECT * FROM foo WHERE (o,pk)>($1,$2) ORDER BY o,pk LIMIT 10;

Or am I going mad?

I'm expecting a table structure somewhat like:

CREATE TABLE foo (
pk TEXT PRIMARY KEY,
value TEXT,
o INT NOT NULL
);

CREATE INDEX foo_ord_idx ON foo (o,pk);

Sam

On Mon, Jun 30, 2008 at 8:51 AM, Sam Mason <sam@samason.me.uk> wrote:

select * from foo where (o,pk)>(o,?) order by o limit 10;

Hum, I think I must be missing something. I'm not sure why you're
comparing 'o' to itself and you're not putting any ordering constraint
on the primary key. I think the query should look closer to:

SELECT * FROM foo WHERE (o,pk)>($1,$2) ORDER BY o,pk LIMIT 10;

Or am I going mad?

yes, you are correct. you need to supply at least one value for each
ordered field. I think this is what the OP was tring to say.

usually it's much simpler than this:
select * from foo where pk > $1 order by pk limit 1;

This will pull up table in pk order which is usually fine. Any
ordering will do as long as the combination of fields being ordered
are unique. Adding pk as the second criteria is only needed if you
want to order by a non duplicate field. If 'o' is a candidate key
this is not required.

btw, the use of OFFSET for this type of problem is actually fairly
terrible...it's almost never a good idea.

merlin