Message-ID: <446A885C.2050308@modgraph-usa.com>
Date: Tue, 16 May 2006 19:20:12 -0700
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716)
MIME-Version: 1.0
To: Christian Paul Cosinas <cpc@cybees.com>
CC: "'PFC'" <lists@peufeu.com>, pgsql-performance@postgresql.org
Subject: Re: Speed Up Offset and Limit Clause
References: <00e101c67997$6aa049f0$1e21100a@ghwk02002147>
In-Reply-To: <00e101c67997$6aa049f0$1e21100a@ghwk02002147>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Christian Paul Cosinas wrote:
> I am creating an application that gets the value of a large table and write
> it to a file.
> 
> 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.
> 
> For example:
> 
> Thread 1 : gets offset 0 limit 5000
> Thread 2 : gets offset 5000 limit 5000
> Thread 3 : gets offset 10000 limit 5000
> 
> And so on...
> 
> Would there be any other faster way than what It thought?

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:

   create sequence thread_seq 
     increment by 1
     minvalue 1 maxvalue 10
     cycle
     start with 1;

   create table mytable(
      column1    integer,
      ... other columns..., 
      thread     integer default nextval('thread_seq')
   );

   create bitmap index i_mytable_thread on mytable(thread);

Now whenever you insert into mytable, you get a value in mytable.thread between 1 and 10, and it's indexed with a highly efficient bitmap index.  So your query becomes:

   Thread 1:  select ... from mytable where ... and thread = 1;
   Thread 2:  select ... from mytable where ... and thread = 2;
   ... and so forth.

Craig