Re: [SQL] OFFSET impact on Performance???

Hi,

What you could do is create a table containing all the fields from your SELECT, plus a per-session unique ID. Then you can store the query results in there, and use SELECT with OFFSET / LIMIT on that table. The WHERE clause for this temp-results table only needs to contain the per-session unique id.

This of course gives you a new problem: cleaning stale data out of the temp-results table. And another new problem is that users will not see new data appear on their screen until somehow the query is re-run (... but that might even be desirable, actually, depending on how your users do their work and what their work is).

And of course better performance cannot be guaranteed until you try it.

Would such a scheme give you any hope of improved performance, or would it be too much of a nightmare?

cheers,

--Tim

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org on behalf of Andrei Bintintan
Sent: Wed 1/26/2005 11:11 AM
To: alex(at)neteconomist(dot)com; Greg Stark
Cc: Richard Huxton; pgsql-sql(at)postgresql(dot)org; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] [SQL] OFFSET impact on 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(at)mit(dot)edu>
To: <alex(at)neteconomist(dot)com>
Cc: "Richard Huxton" <dev(at)archonet(dot)com>; "Andrei Bintintan"
<klodoma(at)ar-sd(dot)net>; <pgsql-sql(at)postgresql(dot)org>;
<pgsql-performance(at)postgresql(dot)org>
Sent: Tuesday, January 25, 2005 8:28 PM
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???

>
> Alex Turner <armtuk(at)gmail(dot)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
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq