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

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 * FROMMyTable;)  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 appropriateone.

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