X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 037889FA2C8 for ; Wed, 17 May 2006 16:04:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 89117-04 for ; Wed, 17 May 2006 16:04:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 414EE9FA279 for ; Wed, 17 May 2006 16:04:03 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id E0BCF56427; Wed, 17 May 2006 14:04:01 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 17 May 2006 14:04:01 -0500 Date: Wed, 17 May 2006 14:04:01 -0500 From: "Jim C. Nasby" To: "Craig A. James" Cc: Christian Paul Cosinas , 'PFC' , pgsql-performance@postgresql.org Subject: Re: Speed Up Offset and Limit Clause Message-ID: <20060517190400.GV26212@pervasive.com> References: <00e101c67997$6aa049f0$1e21100a@ghwk02002147> <446A885C.2050308@modgraph-usa.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <446A885C.2050308@modgraph-usa.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060517:cjames@modgraph-usa.com::w7WWQxW4KzU3BT0g:00000000000000 0000000000000000000000003KV8 X-Hashcash: 1:20:060517:cpc@cybees.com::SgzvpQuEAGg8ScHT:0001s34 X-Hashcash: 1:20:060517:lists@peufeu.com::UaTmF4uvIduISIXX:00Amr X-Hashcash: 1:20:060517:pgsql-performance@postgresql.org::IsHNSwaHXkIpSZ3I:00000 0000000000000000000000000A8M X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/370 X-Sequence-Number: 19157 On Tue, May 16, 2006 at 07:20:12PM -0700, Craig A. James wrote: > >Why I want to use offset and limit is for me to create a threaded > >application so that they will not get the same results. > > In order to return rows 10000 to 15000, it must select all rows from zero > to 15000 and then discard the first 10000 -- probably not what you were > hoping for. > > You might add a "thread" column. Say you want to run ten threads: Another possibility is partitioning the table. If you do that using inheritance-based partitioning, you could just select directly from different partition tables, which probably be even faster than using a single table. The downside is it's more work to setup. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461