pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Speed Up Offset and Limit Clause
8+ messages / 6 participants
[nested] [flat]
* Re: Speed Up Offset and Limit Clause
@ 2006-05-11 06:46 Guillaume Cottenceau <gc@mnc.ch>
parent: Christian Paul Cosinas <cpc@cybees.com>
2 siblings, 0 replies; 8+ messages in thread
From: Guillaume Cottenceau @ 2006-05-11 06:46 UTC (permalink / raw)
To: Christian Paul Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
"Christian Paul Cosinas" <cpc 'at' cybees.com> writes:
> Hi!
>
> How can I speed up my server's performance when I use offset and limit
> clause.
>
> For example I have a query:
> SELECT * FROM table ORDER BY id, name OFFSET 100000 LIMIT 10000
>
> This query takes a long time about more than 2 minutes.
>
> If my query is:
> SELECT * FROM table ORDER BY id, name OFFSET 50000 LIMIT 10000
> It takes about 2 seconds.
First you should read the appropriate documentation.
http://www.postgresql.org/docs/8.1/interactive/performance-tips.html
--
Guillaume Cottenceau
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Speed Up Offset and Limit Clause
@ 2006-05-11 06:51 Chris <dmagick@gmail.com>
parent: Christian Paul Cosinas <cpc@cybees.com>
2 siblings, 0 replies; 8+ messages in thread
From: Chris @ 2006-05-11 06:51 UTC (permalink / raw)
To: Christian Paul Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
Christian Paul Cosinas wrote:
> Hi!
>
> How can I speed up my server's performance when I use offset and limit
> clause.
>
> For example I have a query:
> SELECT * FROM table ORDER BY id, name OFFSET 100000 LIMIT 10000
>
> This query takes a long time about more than 2 minutes.
>
> If my query is:
> SELECT * FROM table ORDER BY id, name OFFSET 50000 LIMIT 10000
> It takes about 2 seconds.
Please create a new thread rather than replying to someone elses post
and changing the subject. These threads can sometimes get missed.
You do have an index on id and name don't you?
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Speed Up Offset and Limit Clause
@ 2006-05-11 07:05 PFC <lists@peufeu.com>
parent: Christian Paul Cosinas <cpc@cybees.com>
2 siblings, 1 reply; 8+ messages in thread
From: PFC @ 2006-05-11 07:05 UTC (permalink / raw)
To: Christian Paul Cosinas <cpc@cybees.com>; pgsql-performance
Why do you want to use it this way ?
Explain what you want to do, there probably is another faster solution...
On Thu, 11 May 2006 16:45:33 +0200, Christian Paul Cosinas
<cpc@cybees.com> wrote:
> Hi!
>
> How can I speed up my server's performance when I use offset and limit
> clause.
>
> For example I have a query:
> SELECT * FROM table ORDER BY id, name OFFSET 100000 LIMIT 10000
>
> This query takes a long time about more than 2 minutes.
>
> If my query is:
> SELECT * FROM table ORDER BY id, name OFFSET 50000 LIMIT 10000
> It takes about 2 seconds.
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 8+ messages in thread
* Speed Up Offset and Limit Clause
@ 2006-05-11 14:45 Christian Paul Cosinas <cpc@cybees.com>
0 siblings, 3 replies; 8+ messages in thread
From: Christian Paul Cosinas @ 2006-05-11 14:45 UTC (permalink / raw)
To: pgsql-performance
Hi!
How can I speed up my server's performance when I use offset and limit
clause.
For example I have a query:
SELECT * FROM table ORDER BY id, name OFFSET 100000 LIMIT 10000
This query takes a long time about more than 2 minutes.
If my query is:
SELECT * FROM table ORDER BY id, name OFFSET 50000 LIMIT 10000
It takes about 2 seconds.
Thanks
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Speed Up Offset and Limit Clause
@ 2006-05-17 02:20 Craig A. James <cjames@modgraph-usa.com>
parent: Christian Paul Cosinas <cpc@cybees.com>
1 sibling, 1 reply; 8+ messages in thread
From: Craig A. James @ 2006-05-17 02:20 UTC (permalink / raw)
To: Christian Paul Cosinas <cpc@cybees.com>; +Cc: 'PFC' <lists@peufeu.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Speed Up Offset and Limit Clause
@ 2006-05-17 09:51 Christian Paul Cosinas <cpc@cybees.com>
parent: PFC <lists@peufeu.com>
0 siblings, 2 replies; 8+ messages in thread
From: Christian Paul Cosinas @ 2006-05-17 09:51 UTC (permalink / raw)
To: 'PFC' <lists@peufeu.com>; pgsql-performance
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?
-----Original Message-----
From: PFC [mailto:lists@peufeu.com]
Sent: Thursday, May 11, 2006 7:06 AM
To: Christian Paul Cosinas; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Speed Up Offset and Limit Clause
Why do you want to use it this way ?
Explain what you want to do, there probably is another faster
solution...
On Thu, 11 May 2006 16:45:33 +0200, Christian Paul Cosinas
<cpc@cybees.com> wrote:
> Hi!
>
> How can I speed up my server's performance when I use offset and limit
> clause.
>
> For example I have a query:
> SELECT * FROM table ORDER BY id, name OFFSET 100000 LIMIT 10000
>
> This query takes a long time about more than 2 minutes.
>
> If my query is:
> SELECT * FROM table ORDER BY id, name OFFSET 50000 LIMIT 10000
> It takes about 2 seconds.
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Speed Up Offset and Limit Clause
@ 2006-05-17 19:04 Jim C. Nasby <jnasby@pervasive.com>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-05-17 19:04 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Christian Paul Cosinas <cpc@cybees.com>; PFC <lists@peufeu.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Speed Up Offset and Limit Clause
@ 2006-05-27 21:04 PFC <lists@peufeu.com>
parent: Christian Paul Cosinas <cpc@cybees.com>
1 sibling, 0 replies; 8+ messages in thread
From: PFC @ 2006-05-27 21:04 UTC (permalink / raw)
To: Christian Paul Cosinas <cpc@cybees.com>; pgsql-performance
> Thread 1 : gets offset 0 limit 5000
> Thread 2 : gets offset 5000 limit 5000
> Thread 3 : gets offset 10000 limit 5000
>
> Would there be any other faster way than what It thought?
Yeah, sure, use a thread which does the whole query (maybe using a
cursor) and fills a queue with the results, then N threads consuming from
that queue... it will work better.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-05-27 21:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-11 14:45 Speed Up Offset and Limit Clause Christian Paul Cosinas <cpc@cybees.com>
2006-05-11 06:46 ` Guillaume Cottenceau <gc@mnc.ch>
2006-05-11 06:51 ` Chris <dmagick@gmail.com>
2006-05-11 07:05 ` PFC <lists@peufeu.com>
2006-05-17 09:51 ` Christian Paul Cosinas <cpc@cybees.com>
2006-05-17 02:20 ` Craig A. James <cjames@modgraph-usa.com>
2006-05-17 19:04 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-27 21:04 ` PFC <lists@peufeu.com>
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