Re: Speed Up Offset and Limit Clause

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