Re: [HACKERS] Speedups - Mailing list pgsql-hackers

> Vadim B. Mikheev wrote:
> >
> > Peter T Mount wrote:
> > >
> > > On Wed, 4 Mar 1998 ocie@paracel.com wrote:
> > >
> > > > This may sound like an obvious question, but if a user defines a
> > > > query, do we save the query plan?  This would reduce the
> > > > communications between the client and server (a small gain), and allow
> > > > the server to start executing the query as soon as it recognized the
> > > > name of the stored query and parsed the arguments.
> > >
> > > Not sure ofhand, but it would be useful for JDBC's PreparedStatement and
> > > CallableStatement classes
> >
> > We can implement it very easy, and fast. Execution plan may be reused
> > many times. Is this feature in standard ?
> > What is proposed syntax if not ?
>
> I don't think it is so much a question of syntax as it is a question
> of what we do in the backend.  Suppose I create a stored query in SQL.
> We already store the SQL source for this in the database, right?  So
> when it comes time to execute the query, we take this SQL and execute
> it as if the user had entered it directly.  What I am proposing would
> be to basically store the compiled query plan as well.
>
> I do see a couple sticky points:
>
> We would need some information about which variables are to be
> substituted into this query plan, but this should be fairly
> straightforward.
>
> Some querys may not respond well to this, for example, if a table had
> an index on an integer field f1, this would probably be the best way
> to satisfy a select where f1<10.  But if this were in a query as f1<x,
> then a sufficiently high value of x might make this not such a good
> way to run the query.  I haven't looked into this, but I would assume
> that the optimizer relies on the specific values in such cases.
>
> We need to be able to handle changes to the structures and contents of
> the tables.  If the query plan is built and we add 10000 rows to a
> table it references, the query should probably be recompiled.  We
> could probably do this at vacuum time.  There is also a small chance
> that a table or index that the query plan was using is dropped.  We
> could automatically rebuild the query if the table was created after
> the query was compiled.
>
>
> Boy, to look at this, you'd think I had already built one of these :)
> I haven't but I'm willing to give it a shot.
>
> Ocie
>
Not to pile on, but, I have a great interest in this subject.   We do a
lot of work using off-the-shelf ODBC tools.  And, we have observed that
these tools use PREPARE for two purposes.

One is to speed up iterative queries which join data from different
databases.   You seem to be addressing this issue.

The other reason PREPARE is used is to retrieve a description of a
query's projection (target/result) with out actually running the
query.   Currently, ODBC drivers must simulate the prepare statement by
submitting the full query and discard the data just to get the result
description.   Obviously this slows response time greatly when the query
is a large data set.   So if you haven't considered returning the the
results description, please do.

Thank Very Much