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 3D2043A5D25 for ; Wed, 26 Jan 2005 19:48:43 +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 50794-01 for ; Wed, 26 Jan 2005 19:48:40 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 7A8193A5D1B for ; Wed, 26 Jan 2005 19:48:41 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1Ctt9P-0000Ao-00; Wed, 26 Jan 2005 14:48:27 -0500 To: alex@neteconomist.com Cc: PFC , Greg Stark , Merlin Moncure , Andrei Bintintan , pgsql-performance@postgresql.org Subject: Re: [SQL] OFFSET impact on Performance??? References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local> <87brbiah0f.fsf@stark.xeocode.com> <33c6269f0501261035bc87695@mail.gmail.com> In-Reply-To: <33c6269f0501261035bc87695@mail.gmail.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 26 Jan 2005 14:48:27 -0500 Message-ID: <871xc8ynsk.fsf@stark.xeocode.com> Lines: 31 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/511 X-Sequence-Number: 10201 Alex Turner writes: > 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... You could cheat and do queries with an offset of 0 directly but also start up a background job to fetch the complete results and cache them. queries with a non-zero offset would have to wait until the complete cache is built. You have to be careful about people arriving from bookmarks to non-zero offsets and people hitting reload before the cache is finished being built. As someone else suggested you could look into other systems for storing the cache. If you don't need to join against other database tables and you don't need the reliability of a database then there are faster solutions like memcached for example. (The problem of joining against database tables is even solvable, look up pgmemcached. No idea how it performs though.) But I think you're running into a fundamental tension here. The feature you're looking for: being able to jump around in an arbitrary non-indexed query result set which can be arbitrarily large, requires a lot of work. All you can do is shift around *when* that work is done. There's not going to be any way to avoid doing the work entirely. -- greg