On 5/12/10 4:55 AM, Kevin Grittner wrote:
venu madhav wrote:
we display in sets of 20/30 etc. The user also has the option to
browse through any of those records hence the limit and offset.
Have you considered alternative techniques for paging? You might
use values at the edges of the page to run a small query (limit, no
offset) when they page. You might generate all the pages on the
first pass and cache them for a while.
Kevin is right. You need to you "hitlists" - a semi-temporary table that holds
the results of your initial query. You're repeating a complex, expensive query over and
over, once for each page of data that the user wants to see. Instead, using a hitlist,
your initial query looks something like this:
create table hitlist_xxx(
objectid integer,
sortorder integer default nextval('hitlist_seq')
);
insert into hitlist_xxx (objectid)
(select ... your original query ... order by ...)
You store some object ID or primary key in the "hitlist" table, and the
sequence records your original order.
Then when your user asks for page 1, 2, 3 ... N, all you have to do is join
your hitlist to your original data:
select ... from mytables join hitlist_xxx on (...)
where sortorder >= 100 and sortorder < 120;
which would instantly return page 5 of your data.
To do this, you need a way to know when a user is finished so that you can
discard the hitlist.
Craig
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
