X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DECC53A5CFB for ; Wed, 26 Jan 2005 18:35:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32105-10 for ; Wed, 26 Jan 2005 18:35:30 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198]) by svr1.postgresql.org (Postfix) with ESMTP id 29B5A3A5CF3 for ; Wed, 26 Jan 2005 18:35:30 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 71so139030wra for ; Wed, 26 Jan 2005 10:35:27 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=jB70OHJ5NFqPGvovETAQxuuDVbwA9o3cdteFngSAlPKjonc3osRhdaJM5OK3r5zXW4cP5Hy510/45+beTFVhmHT7YRhp7fqNcSgyD4tmfdXdfjfj/vtJqk9qwMRSxnIKCT+eL+YQepwu4+xgabg8T0JdrEWA41j9cbUqvQ8GPHQ= Received: by 10.54.51.70 with SMTP id y70mr393714wry; Wed, 26 Jan 2005 10:35:27 -0800 (PST) Received: by 10.54.18.72 with HTTP; Wed, 26 Jan 2005 10:35:27 -0800 (PST) Message-ID: <33c6269f0501261035bc87695@mail.gmail.com> Date: Wed, 26 Jan 2005 13:35:27 -0500 From: Alex Turner Reply-To: alex@neteconomist.com To: PFC Subject: Re: [SQL] OFFSET impact on Performance??? Cc: Greg Stark , Merlin Moncure , Andrei Bintintan , pgsql-performance@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local> <87brbiah0f.fsf@stark.xeocode.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.039 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/505 X-Sequence-Number: 10195 The problem with this approach is TTFB (Time to first Byte). The initial query is very slow, but additional requests are fast. In most situations we do not want the user to have to wait a disproportionate amount of time for the initial query. If this is the first time using the system this will be the impression that will stick with them. I guess we could experiment and see how much extra time creating a cache table will take... Alex Turner NetEconomist On Wed, 26 Jan 2005 13:58:18 +0100, PFC wrote: > > Supposing your searches display results which are rows coming from one > specific table, you could create a cache table : > > search_id serial primary key > index_n position of this result in the global result set > result_id id of the resulting row. > > Then, making a search with 50k results would INSERT INTO cache ... SELECT > FROM search query, with a way to set the index_n column, which can be a > temporary sequence... > > Then to display your pages, SELECT from your table with index_n BETWEEN so > and so, and join to the data table. > > If you're worried that it might take up too much space : store an integer > array of result_id instead of just a result_id ; this way you insert fewer > rows and save on disk space. Generate it with a custom aggregate... then > just grab a row from this table, it contains all the id's of the rows to > display. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >