Re: Suspending SELECTs - Mailing list pgsql-performance

On Wed, Jan 18, 2006 at 09:57:50AM +0100, Alessandro Baretta wrote:
> mark@mark.mielke.cc wrote:
> >On Tue, Jan 17, 2006 at 08:56:00PM +0100, Alessandro Baretta wrote:
> >>I understand most of these issues, and expected this kind of reply.
> >>Please, allow me to insist that we reason on this problem and try to find
> >>a solution. My reason for doing so is that the future software industry
> >>is likely to see more and more web applications retrieving data from
> >>virtually endless databases, and in such contexts, it is sensible to ask
> >>the final client--the web client--to store the "cursor state", because
> >>web interaction is intrinsically asynchronous, and you cannot count on
> >>users logging out when they're done, releasing resources allocated to
> >>them. Think of Google.
> >What is wrong with LIMIT and OFFSET? I assume your results are ordered
> >in some manner.
> It looks like this is the only possible solution at present--and in the
> future, too--but it has a tremendouse performance impact on queries
> returning thousands of rows.

In the case of one web user generating one query, I don't see how it would
have a tremendous performance impact on large queries.

You mentioned google. I don't know how you use google - but most of the
people I know, rarely ever search through the pages. Generally the answer
we want is on the first page. If the ratio of users who search through
multiple pages of results, and users who always stop on the first page,
is anything significant (better than 2:1?) LIMIT and OFFSET are the
desired approach. Why have complicated magic in an application, for a
subset of the users?

I there is to be a change to PostgreSQL to optimize for this case, I
suggest it involve the caching of query plans, executor plans, query
results (materialized views?), LIMIT and OFFSET. If we had all of
this, you would have exactly what you want, while benefitting many
more people than just you. No ugly 'persistent state cursors' or
'import/export cursor state' implementation. People would automatically
benefit, without changing their applications.

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   |
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/