Re: Speed with offset clause - Mailing list 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:


<excerpt><excerpt>

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:


<excerpt>On 6/24/05, Yves Vindevogel <<yves.vindevogel@implements.be>
wrote:

<excerpt>So, when I want the last page, which is: 600k / 25 = page
24000 - 1 =

23999, I issue the offset of 23999 * 25

</excerpt>

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



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller></excerpt><<Pasted Graphic 2.tiff><smaller>

<excerpt>

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

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></excerpt></smaller></excerpt><excerpt><excerpt>



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller><<Pasted Graphic 2.tiff><smaller>


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

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller></excerpt><excerpt>


---------------------------(end of
broadcast)---------------------------

TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>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

<smaller>


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

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



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.