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