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 408E03A588E for ; Wed, 26 Jan 2005 21:22:34 +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 58985-06 for ; Wed, 26 Jan 2005 21:22:30 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id A21763A581E for ; Wed, 26 Jan 2005 21:22:31 +0000 (GMT) Received: (qmail 18554 invoked from network); 26 Jan 2005 22:22:48 +0100 Received: from unknown (HELO musicbox) (boutiquenumerique-lists@192.168.0.2) by boutiquenumerique.com with SMTP; 26 Jan 2005 22:22:48 +0100 Date: Wed, 26 Jan 2005 22:24:34 +0100 To: alex@neteconomist.com Subject: Re: [SQL] OFFSET impact on Performance??? Cc: "Greg Stark" , "Merlin Moncure" , "Andrei Bintintan" , pgsql-performance@postgresql.org References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local> <87brbiah0f.fsf@stark.xeocode.com> <33c6269f0501261035bc87695@mail.gmail.com> From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <33c6269f0501261035bc87695@mail.gmail.com> User-Agent: Opera M2/7.54 (Linux, build 751) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/517 X-Sequence-Number: 10207 > 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 !