Skip site navigation (1) Skip section navigation (2)

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

From: Ragnar <gnari(at)hive(dot)is>
To: Bill Thoen <bthoen(at)gisnet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Advice Wanted on Selecting Multi-row Data Requestsin 10-Row Blocks
Date: 2008-06-27 20:22:35
Message-ID: 1214598155.28700.8.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-general
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
> 
> 
> 


In response to

Responses

pgsql-general by date

Next:From: Lennin CaroDate: 2008-06-27 20:23:07
Subject: Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
Previous:From: Bill ThoenDate: 2008-06-27 20:14:24
Subject: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group