From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance issues when the number of records are around 10 Million
Date: Wed, 12 May 2010 07:08:20 -0700
Message-ID: <4BEAB654.3030408@emolecules.com> (raw)
In-Reply-To: <4BEA50DE02000025000315EF@gw.wicourts.gov>
References: <4BEA50DE02000025000315EF@gw.wicourts.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Performance issues when the number of records are around 10 Million
In-Reply-To: <4BEAB654.3030408@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox