Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V6Vyi-0003XZ-6R for pgsql-performance@arkaria.postgresql.org; Tue, 06 Aug 2013 01:22:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V6Vyh-0002c7-JS for pgsql-performance@arkaria.postgresql.org; Tue, 06 Aug 2013 01:22:23 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V6Vyg-0002c1-Pg for pgsql-performance@postgresql.org; Tue, 06 Aug 2013 01:22:22 +0000 Received: from smtp.01.com ([199.36.142.181] helo=smtp-out-1.01.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V6Vye-00076S-BH for pgsql-performance@postgresql.org; Tue, 06 Aug 2013 01:22:22 +0000 Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id E8DD4294124 for ; Mon, 5 Aug 2013 20:22:19 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp-out-1.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id y4HbCrJ2feDW for ; Mon, 5 Aug 2013 20:22:19 -0500 (CDT) Received: from smtp-out-1.01.com (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id C6474294127 for ; Mon, 5 Aug 2013 20:22:19 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id AFC90294126 for ; Mon, 5 Aug 2013 20:22:19 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp-out-1.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id QcHy_ICjE3jR for ; Mon, 5 Aug 2013 20:22:19 -0500 (CDT) Received: from [192.168.47.104] (50-0-92-178.dsl.dynamic.sonic.net [50.0.92.178]) by smtp-out-1.01.com (Postfix) with ESMTPSA id 9EB84294124 for ; Mon, 5 Aug 2013 20:22:17 -0500 (CDT) Message-ID: <52004FC8.3070102@agliodbs.com> Date: Mon, 05 Aug 2013 18:22:16 -0700 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130623 Thunderbird/17.0.7 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: ORDER BY, LIMIT and indexes References: In-Reply-To: X-Enigmail-Version: 1.5.2 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Ivan, > Or, more generally, is there some set of circumstances under which the > catastrophic scenario will happen? Yes: SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 100000 This is the "high offset" problem, and affects all databases which support applications with paginated results, including non-relational ones like SOLR. The basic problem is that you can't figure out what is OFFSET 100000 without first sorting the first 100000 results. The easiest solution is to limit the number of pages your users can "flip through". Generally anyone asking for page 10,000 is a bot screen-scraping your site, anyway. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance