Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 424B02D0B10 for ; Fri, 10 Feb 2012 11:36:09 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RvsW6-00071F-3Z for pgsql-performance@postgresql.org; Fri, 10 Feb 2012 15:36:08 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q1AFZqed013842; Fri, 10 Feb 2012 10:35:52 -0500 (EST) To: David Yeu cc: "pgsql-performance@postgresql.org" , "ops@groupme.com" Subject: Re: Performance on large, append-only tables In-reply-to: References: Comments: In-reply-to David Yeu message dated "Wed, 08 Feb 2012 18:03:04 +0000" Date: Fri, 10 Feb 2012 10:35:52 -0500 Message-ID: <13841.1328888152@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/88 X-Sequence-Number: 46260 David Yeu writes: > Our queries essentially fall into the following cases: > * Š WHERE group_id = ? ORDER BY created_at DESC LIMIT 20; > * Š WHERE group_id = ? AND id > ? ORDER BY created_at DESC; > * Š WHERE group_id = ? AND id < ? ORDER BY created_at DESC LIMIT 20; > * Š WHERE group_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET ?; All of those should be extremely cheap if you've got the right indexes, with the exception of the last one. Large OFFSET values are never a good idea, because Postgres always has to scan and discard that many rows. If you need to fetch successive pages, consider using a cursor with a series of FETCH commands. Another possibility, if the data is sufficiently constrained, is to move the limit point with each new query, ie instead of OFFSET use something like WHERE group_id = ? AND created_at < last-previous-value ORDER BY created_at DESC LIMIT 20; regards, tom lane