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

On Jan 26, 2005, at 5:36 AM, Leeuw van der, Tim wrote:

> 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 is what I do, but I use two columns for indexing the original
query, a user_id (not session-id) and an index to the "query_id" that
is unique within user.  This "query_id" is a foreign key to another
table that describes the query (often just a name).  I allow the user
only a fixed number of "stored" queries and recycle after hitting the
maximum.  You can timestamp your queries so that when you recycle you
drop the oldest one first.  If you don't need multiple stored query
results, then using the user_id is probably adequate (assuming the user
is not logged on in several locations simultaneously).

> 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).
>

See above.  The query refresh issue remains.

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

For the standard operating procedure of perform query===>view results,
I have found this to be a nice system.  The user is accustomed to
queries taking a bit of time to perform, but then wants to be able to
manipulate and view data rather quickly; this paradigm is pretty well
served by making a separate table of results, particularly if the
original query is costly.


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

This question still applies....

Sean

>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org on behalf of Andrei
> Bintintan
> Sent: Wed 1/26/2005 11:11 AM
> To: alex@neteconomist.com; Greg Stark
> Cc: Richard Huxton; pgsql-sql@postgresql.org;
> pgsql-performance@postgresql.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.