From: Craig A. James <cjames@modgraph-usa.com>
To: Christian Paul Cosinas <cpc@cybees.com>
Cc: 'PFC' <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Speed Up Offset and Limit Clause
Date: Tue, 16 May 2006 19:20:12 -0700
Message-ID: <446A885C.2050308@modgraph-usa.com> (raw)
In-Reply-To: <00e101c67997$6aa049f0$1e21100a@ghwk02002147>
References: <00e101c67997$6aa049f0$1e21100a@ghwk02002147>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cjames@modgraph-usa.com, cpc@cybees.com, lists@peufeu.com
Subject: Re: Speed Up Offset and Limit Clause
In-Reply-To: <446A885C.2050308@modgraph-usa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox