pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSpeed with offset clause
7+ messages / 4 participants
[nested] [flat]
* Speed with offset clause
@ 2005-06-24 18:18 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 2 replies; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-24 18:18 UTC (permalink / raw)
To: pgsql-performance
Hi again all,
My queries are now optimised. They all use the indexes like they
should.
However, there's still a slight problem when I issue the "offset"
clause.
We have a table that contains 600.000 records
We display them by 25 in the webpage.
So, when I want the last page, which is: 600k / 25 = page 24000 - 1 =
23999, I issue the offset of 23999 * 25
This take a long time to run, about 5-10 seconds whereas offset below
100 take less than a second.
Can I speed this up ?
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Speed with offset clause
@ 2005-06-24 18:54 hubert depesz lubaczewski <depesz@gmail.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 0 replies; 7+ messages in thread
From: hubert depesz lubaczewski @ 2005-06-24 18:54 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
On 6/24/05, Yves Vindevogel <yves.vindevogel@implements.be> wrote:
> So, when I want the last page, which is: 600k / 25 = page 24000 - 1 =
> 23999, I issue the offset of 23999 * 25
improving this is hard, but not impossible.
if you have right index created, try to reverse the order and fetch
first adverts, and then resort it (just the 25 adverts) in correct
order.
it will be faster.
depesz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Speed with offset clause
@ 2005-06-24 19:22 John A Meinel <john@arbash-meinel.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 1 reply; 7+ messages in thread
From: John A Meinel @ 2005-06-24 19:22 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
Yves Vindevogel wrote:
> Hi again all,
>
> My queries are now optimised. They all use the indexes like they should.
> However, there's still a slight problem when I issue the "offset" clause.
>
> We have a table that contains 600.000 records
> We display them by 25 in the webpage.
> So, when I want the last page, which is: 600k / 25 = page 24000 - 1 =
> 23999, I issue the offset of 23999 * 25
> This take a long time to run, about 5-10 seconds whereas offset below
> 100 take less than a second.
>
> Can I speed this up ?
>
>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> *Yves Vindevogel*
> *Implements*
>
Postgres has the optimization that it will plan a query, and once it
reaches the limit, it can stop even though there is more data available.
The problem you are having is that it has to go through "offset" rows
first, before it can apply the limit.
If you can, (as mentioned in the other post), try to refine your index
so that you can reverse it for the second half of the data.
This is probably tricky, as you may not know how many rows you have (or
the amount might be changing).
A potentially better thing, is if you have an index you are using, you
could use a subselect so that the only portion that needs to have 60k
rows is a single column.
Maybe an example:
Instead of saying:
SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY
table1.date OFFSET x LIMIT 25;
You could do:
SELECT * FROM
(SELECT id FROM table1 OFFSET x LIMIT 25) as subselect
JOIN table1 ON subselect.id = table1.id
, table2
WHERE table1.id = table2.id;
That means that the culling process is done on only a few rows of one
table, and the rest of the real merging work is done on only a few rows.
It really depends on you query, though, as what rows you are sorting on
has a big influence on how well this will work.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Fwd: Speed with offset clause
@ 2005-06-24 20:19 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-24 20:19 UTC (permalink / raw)
To: pgsql-performance
>
> Hmm, I can't do this, i'm afraid. Or it would be rather difficult
>
> My query is executed through a webpage (link to the page in a
> navigation bar)
> I do not know how many records there are (data is changing, and
> currently is 600k records)
>
> The only thing I could do, is doing this in a function where I first
> get the page, and then decide whether to use the normal sort order or
> the reversed order
> That would put my weak point right in the middle, which is not that
> bad, but I would like to find an easier way, if that is possible
>
> Huge memory would help ?
>
> On 24 Jun 2005, at 20:54, hubert depesz lubaczewski wrote:
>
>> On 6/24/05, Yves Vindevogel <yves.vindevogel@implements.be> wrote:
>>> So, when I want the last page, which is: 600k / 25 = page 24000 - 1 =
>>> 23999, I issue the offset of 23999 * 25
>>
>> improving this is hard, but not impossible.
>> if you have right index created, try to reverse the order and fetch
>> first adverts, and then resort it (just the 25 adverts) in correct
>> order.
>> it will be faster.
>>
>> depesz
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
>
>
> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you. Then they laugh at you. Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Speed with offset clause
@ 2005-06-24 20:23 Yves Vindevogel <yves.vindevogel@implements.be>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-24 20:23 UTC (permalink / raw)
To: pgsql-performance
Hi,
Indeed, I would have to do it through a function, where I check the
number of pages, ....
It puts my weakest point in the middle then.
I could simply rewrite my query like you state, just to check.
I think all my queries are on one table only. (I report in a website
on one table, that has been denormalized into other smaller tables for
speed)
But the problem is on the big table.
I'm currently looking at another possibility, and that is generating
XML files based upon my database. This would increase disk space
enormously, but limit my problems with the database.
Since I am using Cocoon for the website, this is not such a problematic
decision, disks are cheap and I need only a few modifications to my
code.
On 24 Jun 2005, at 21:22, John A Meinel wrote:
> Yves Vindevogel wrote:
>
>> Hi again all,
>>
>> My queries are now optimised. They all use the indexes like they
>> should.
>> However, there's still a slight problem when I issue the "offset"
>> clause.
>>
>> We have a table that contains 600.000 records
>> We display them by 25 in the webpage.
>> So, when I want the last page, which is: 600k / 25 = page 24000 - 1 =
>> 23999, I issue the offset of 23999 * 25
>> This take a long time to run, about 5-10 seconds whereas offset below
>> 100 take less than a second.
>>
>> Can I speed this up ?
>>
>>
>> Met vriendelijke groeten,
>> Bien à vous,
>> Kind regards,
>>
>> *Yves Vindevogel*
>> *Implements*
>>
> Postgres has the optimization that it will plan a query, and once it
> reaches the limit, it can stop even though there is more data
> available.
> The problem you are having is that it has to go through "offset" rows
> first, before it can apply the limit.
> If you can, (as mentioned in the other post), try to refine your index
> so that you can reverse it for the second half of the data.
>
> This is probably tricky, as you may not know how many rows you have
> (or the amount might be changing).
>
> A potentially better thing, is if you have an index you are using, you
> could use a subselect so that the only portion that needs to have 60k
> rows is a single column.
>
> Maybe an example:
> Instead of saying:
>
> SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY
> table1.date OFFSET x LIMIT 25;
>
> You could do:
>
> SELECT * FROM
> (SELECT id FROM table1 OFFSET x LIMIT 25) as subselect
> JOIN table1 ON subselect.id = table1.id
> , table2
> WHERE table1.id = table2.id;
>
> That means that the culling process is done on only a few rows of one
> table, and the rest of the real merging work is done on only a few
> rows.
>
> It really depends on you query, though, as what rows you are sorting
> on has a big influence on how well this will work.
>
> John
> =:->
>
>
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Speed with offset clause
@ 2005-06-24 20:34 Yves Vindevogel <yves.vindevogel@implements.be>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-24 20:34 UTC (permalink / raw)
To: pgsql-performance
I just ran this query
select p.* from tblPrintjobs p , (select oid from tblPrintjobs limit 25
offset 622825) as subset where p.oid = subset.oid
And it seems to be a bit faster than without the subselect, probably
because I'm only getting one column.
The speed gain is not that high though
On 24 Jun 2005, at 22:19, Yves Vindevogel wrote:
>>
>> Hmm, I can't do this, i'm afraid. Or it would be rather difficult
>>
>> My query is executed through a webpage (link to the page in a
>> navigation bar)
>> I do not know how many records there are (data is changing, and
>> currently is 600k records)
>>
>> The only thing I could do, is doing this in a function where I first
>> get the page, and then decide whether to use the normal sort order or
>> the reversed order
>> That would put my weak point right in the middle, which is not that
>> bad, but I would like to find an easier way, if that is possible
>>
>> Huge memory would help ?
>>
>> On 24 Jun 2005, at 20:54, hubert depesz lubaczewski wrote:
>>
>>> On 6/24/05, Yves Vindevogel <yves.vindevogel@implements.be> wrote:
>>>> So, when I want the last page, which is: 600k / 25 = page 24000 - 1
>>>> =
>>>> 23999, I issue the offset of 23999 * 25
>>>
>>> improving this is hard, but not impossible.
>>> if you have right index created, try to reverse the order and fetch
>>> first adverts, and then resort it (just the 25 adverts) in correct
>>> order.
>>> it will be faster.
>>>
>>> depesz
>>>
>>>
>> Met vriendelijke groeten,
>> Bien à vous,
>> Kind regards,
>>
>> Yves Vindevogel
>> Implements
>>
> <Pasted Graphic 2.tiff>
>>
>> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
>>
>> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>>
>> Web: http://www.implements.be
>>
>> First they ignore you. Then they laugh at you. Then they fight you.
>> Then you win.
>> Mahatma Ghandi.
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
> <Pasted Graphic 2.tiff>
>
> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you. Then they laugh at you. Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Speed with offset clause
@ 2005-06-25 11:42 Radu-Adrian Popescu <radu.popescu@aldratech.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 7+ messages in thread
From: Radu-Adrian Popescu @ 2005-06-25 11:42 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
> I just ran this query
>
> select p.* from tblPrintjobs p , (select oid from tblPrintjobs limit 25
> offset 622825) as subset where p.oid = subset.oid
>
I'm just curious here, from a social point of view. How often do you think
someone will paginate over say 300K rows in steps of 25 ?
The way I see things, pagination is only meant for humans. If someone
really looks at 300K rows then it's really cheaper and makes more sense to
download them/import into spreadsheet program instead of clicking next
12.000 times.
If it's not intended for humans then there's better ways of doing this.
Regards,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-06-25 11:42 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-24 18:18 Speed with offset clause Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-24 18:54 ` hubert depesz lubaczewski <depesz@gmail.com>
2005-06-24 19:22 ` John A Meinel <john@arbash-meinel.com>
2005-06-24 20:23 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-24 20:19 Fwd: Speed with offset clause Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-24 20:34 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-25 11:42 ` Radu-Adrian Popescu <radu.popescu@aldratech.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