Re: 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


On fös, 2008-06-27 at 14:14 -0600, Bill Thoen 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.
>
> 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
>
>
>