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