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 4C04A3A5DA3 for ; Thu, 27 Jan 2005 04:42:36 +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 87000-09 for ; Thu, 27 Jan 2005 04:42:30 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.202]) by svr1.postgresql.org (Postfix) with ESMTP id 3E55E3A5933 for ; Thu, 27 Jan 2005 04:42:29 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 71so240389wra for ; Wed, 26 Jan 2005 20:42:21 -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=dj1wMZd/uaEXsD/xWS75BAVqTkzcHvqkATyhIe7RvjzREE84yS1faIscQyjRSUYptAS8vHBJC5h5shzPdZkAplvOPg/ktsrP2VzyrAG7OPTTHC4fxDPn65GYej418SenLLIF55gL6s5SSGYUDNhpjXjPnisAXnIamR4o11vdAOs= Received: by 10.54.51.70 with SMTP id y70mr289876wry; Wed, 26 Jan 2005 20:42:21 -0800 (PST) Received: by 10.54.18.72 with HTTP; Wed, 26 Jan 2005 20:42:21 -0800 (PST) Message-ID: <33c6269f05012620427c1ccc40@mail.gmail.com> Date: Wed, 26 Jan 2005 23:42:21 -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> <33c6269f0501261035bc87695@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.038 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/523 X-Sequence-Number: 10213 Thats a really good idea, just store a list of the sorted ids in the temp table - small amount of data for insert... I like it! Alex Turner NetEconomist On Wed, 26 Jan 2005 22:24:34 +0100, PFC wrote: > > 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... > > Do it on the second page then ;) > > Seriously : > - If you want to display the result count and page count, you'll need to > do the whole query anyway, so you might as well save the results. > - inserting the result id's in a temp table one by one will be slow, but > you can do this : > > select array_accum(id) from temp group by id/20 limit 3; > array_accum > --------------------------------------------------------------- > {1,2,4,8,16,17,9,18,19,5,10,11,3,6,12,13,7,14,15} > {32,33,34,35,36,37,38,39,20,21,22,23,24,25,26,27,28,29,30,31} > {40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59} > > - a really big search of 131072 results : > create table cache (id serial primary key, value integer[]); > explain analyze insert into cache (value) select array_accum(id) from temp > group by id/100; > Subquery Scan "*SELECT*" (cost=14382.02..17986.50 rows=131072 width=32) > (actual time=961.746..1446.630 rows=1311 loops=1) > -> GroupAggregate (cost=14382.02..16020.42 rows=131072 width=4) > (actual time=961.607..1423.803 rows=1311 loops=1) > -> Sort (cost=14382.02..14709.70 rows=131072 width=4) (actual > time=961.181..1077.662 rows=131072 loops=1) > Sort Key: (id / 100) > -> Seq Scan on "temp" (cost=0.00..2216.40 rows=131072 > width=4) (actual time=0.032..291.652 rows=131072 loops=1) > Total runtime: 1493.304 ms > > Note that the "SELECT..." part takes 1400 ms, and the INSERT part takes > the rest, which is really small. It's the sort which takes most of the > time, but you'll be doing it anyway to get your results in order, so it > comes free to you. This will generate 1000 pages with 100 results on each. > If your searches yield say 1000 results it'll be perfectly fine and can > target times in the sub-100 ms for caching the results (not counting the > total query time of course !) > > Using arrays is the key here, because inserting all the results as > individual rows in the table is gonna be a whole lot slower ! > >