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

>
> 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.
>

In addition to LIMIT, Postgresql has an OFFSET clause:

http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-LIMIT

So if you want to show the records in pages of 10, your queries would
look like this:

SELECT * FROM MyTable ORDER BY my_key LIMIT 10 OFFSET 0;
SELECT * FROM MyTable ORDER BY my_key LIMIT 10 OFFSET 10;
SELECT * FROM MyTable ORDER BY my_key LIMIT 10 OFFSET 20;

The "offset" clause tells postgresql how many rows to skip. Note that
you always need an "order by" clause in there as well to get meaningful
results.