From: Alessandro Baretta <a.baretta@barettadeit.com>
To: mark@mark.mielke.cc
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Suspending SELECTs
Date: Wed, 18 Jan 2006 16:12:07 +0100
Message-ID: <43CE5AC7.50407@barettadeit.com> (raw)
In-Reply-To: <20060118143024.GA8402@mark.mielke.cc>
References: <43CB71AC.20203@barettadeit.com>
<9766.1137433907@sss.pgh.pa.us>
<43CD4BD0.9090807@barettadeit.com>
<20060117211259.GA13926@mark.mielke.cc>
<43CE030E.1070707@barettadeit.com>
<20060118143024.GA8402@mark.mielke.cc>
mark@mark.mielke.cc wrote:
> On Wed, Jan 18, 2006 at 09:57:50AM +0100, Alessandro Baretta wrote:
>
> 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.
Actually, many of the features you mention (caching executor plans--that is
imposing the use of prepared queries, caching query results and materializing
views) I have already implemented in my "middleware". Somehow, apparently, my
intuition on the problem of determining what ought to be delegated to the DBMS
and what to the "middleware" is the opposites of most people on this list. As I
mentioned earlier, I democratically accept the position of the majority--and
monarchically I accept Tom's. And, scientifically, I have taken resposibility
for proving myself wrong: I have stated my assumptions, I have formulated the
hypothesis, I have designed an experiment capable of disproving it, and I have
collected the data. Here are the steps and the results of the experiment.
Assumptions: Google defines the "best current practices" in web applications.
Hypothesis: The "best practice" for returning large data sets is to devise an
algorithm (say, persistent cursors, for example) allowing subdivision of
recordset is pages of a fixed maximum size, in such a way that sequentially
fetching pages of records requires the system to compute each record only once.
Experiment: Given the stated assumption, record the time taken by Google to
retrieve a sequence of pages of results, relative to the same query string.
Repeat the experiment with different settings. Notice that Google actually
displays on the results page the time taken to process the request.
Results: I'll omit the numerical data, which everyone can easily obtain in only
a few minutes, repeating the experiment. I used several query strings containing
very common words ("linux debian", "linux kernel", "linux tux"), each yielding
millions of results. I set Google to retrieve 100 results per page. Then I ran
the query and paged through the data set. The obvious result is that execution
time is a monotonously growing function of the page number. This clearly
indicates that Google does not use any algorithm of the proposed kind, but
rather an OFFSET/LIMIT strategy, thus disproving the hypothesis.
It must also be noted that Google refuses to return more than 1000 results per
query, thus indicating that the strategy the adopted quite apparently cannot
scale indefinitely, for on a query returning a potentially flooding dataset, a
user paging through the data would experience a linear slowdown on the number of
pages already fetched, and the DBMS workload would also be linear on the number
of fetched pages.
I do not like this approach, but the fact that Google came up with no better
solution is a clear indication that Tome et al. are more than correct.
Alex
--
*********************************************************************
http://www.barettadeit.com/
Baretta DE&IT
A division of Baretta SRL
tel. +39 02 370 111 55
fax. +39 02 370 111 54
Our technology:
The Application System/Xcaml (AS/Xcaml)
<http://www.asxcaml.org/;
The FreerP Project
<http://www.freerp.org/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a.baretta@barettadeit.com, mark@mark.mielke.cc, tgl@sss.pgh.pa.us
Subject: Re: Suspending SELECTs
In-Reply-To: <43CE5AC7.50407@barettadeit.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox