Re: [SQL] OFFSET impact on Performance??? - Mailing list pgsql-performance

From Andrei Bintintan
Subject Re: [SQL] OFFSET impact on Performance???
Date
Msg-id 015a01c5038f$73a70720$0b00a8c0@forge
Whole thread Raw
In response to OFFSET impact on Performance???  ("Andrei Bintintan" <klodoma@ar-sd.net>)
Responses Re: [SQL] OFFSET impact on Performance???  (Alex Turner <armtuk@gmail.com>)
List pgsql-performance
The problems still stays open.

The thing is that I have about 20 - 30 clients that are using that SQL query
where the offset and limit are involved. So, I cannot create a temp table,
because that means that I'll have to make a temp table for each session...
which is a very bad ideea. Cursors somehow the same. In my application the
Where conditions can be very different for each user(session) apart.

The only solution that I see in the moment is to work at the query, or to
write a more complex where function to limit the results output. So no
replace for Offset/Limit.

Best regards,
Andy.


----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: <alex@neteconomist.com>
Cc: "Richard Huxton" <dev@archonet.com>; "Andrei Bintintan"
<klodoma@ar-sd.net>; <pgsql-sql@postgresql.org>;
<pgsql-performance@postgresql.org>
Sent: Tuesday, January 25, 2005 8:28 PM
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???


>
> Alex Turner <armtuk@gmail.com> writes:
>
>> I am also very interesting in this very question.. Is there any way to
>> declare a persistant cursor that remains open between pg sessions?
>> This would be better than a temp table because you would not have to
>> do the initial select and insert into a fresh table and incur those IO
>> costs, which are often very heavy, and the reason why one would want
>> to use a cursor.
>
> TANSTAAFL. How would such a persistent cursor be implemented if not by
> building a temporary table somewhere behind the scenes?
>
> There could be some advantage if the data were stored in a temporary table
> marked as not having to be WAL logged. Instead it could be automatically
> cleared on every database start.
>
> --
> greg
>
>


pgsql-performance by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: PostgreSQL clustering VS MySQL clustering
Next
From: "Leeuw van der, Tim"
Date:
Subject: Re: [SQL] OFFSET impact on Performance???