Re: Performance issues when the number of records are around 10 Million

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