Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!ucsd!helios.ee.lbl.gov!pasteur!eden!mao
From: mao@eden (Mike Olson)
Newsgroups: comp.databases
Subject: Re: SQL Precompilers - Revisited
Message-ID: <26852@pasteur.Berkeley.EDU>
Date: 8 Aug 90 18:04:30 GMT
References: <1990Aug8.153529.12164@dg-rtp.dg.com>
Sender: news@pasteur.Berkeley.EDU
Reply-To: mao@postgres.Berkeley.EDU (Mike Olson)
Followup-To: comp.databases
Organization: University of California, Berkeley
Lines: 38
X-Local-Date: 8 Aug 90 11:04:30 PDT


In <1990Aug8.153529.12164@dg-rtp.dg.com>, cohend@roadkill.rtp.dg.com (Dave
Cohen) writes (regarding storing compiled query plans for later execution):

> I meant to stick something in to the
> effect that "database restructuring results in reoptimization
> on-the-fly, such as the next time the statement is stored.
> However, 'restructuring' must be defined in terms that
> don't result in reoptimization every time, thus reducing
> performance to the same level as dynamic execution. Thus,
> a new index would cause reoptimization while the insertion/deletion
> of 200 rows would not."

the insertion or deletion of two hundred rows (or one row, for that matter)
can change the selectivity of an index dramatically.  consider what happens
if the rows added or deleted contain columns used in joins in, say, a
nestloop plan.  the addition of a single row can force you to execute the
nestloop again for every tuple in the inner loop.

i agree that precompilation makes canned apps run faster, and that the speed
win is critical in high-throughput environments, like airline reservations.
but the database engine has to understand what changes invalidate the compiled
plan, and has to do the right thing.  this is a lot harder than it looks.
to do it right, you need perfect information on database state.  perfect
information is very expensive.

a reasonable compromise is to have a daemon that rifles your database nightly,
while it's on-line, if you like.  this daemon updates statistics on extant
relations (key ranges, number of tuples and pages, index selectivity, and
so on), and recompiles all your canned queries just for kicks.  that way,
a bad plan isn't likely to stay around for more than one day.

remarkably enough, postgres takes exactly this approach to keeping db
statistics current.
					mike olson
					postgres research group
					uc berkeley
					mao@postgres.berkeley.edu