Re: [HACKERS] Speedups

ocie(at)paracel(dot)com wrote:
>
> > > 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 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.

Parser, Planner/Optimizer and Executor are able to handle parameters!
No problems with this.

> 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.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Unfortunately, no!
We have to add this feature of 'course.
I don't know how we could deal with pre-compiled plans after this :(
May be, we could prepare/store not single plan, but some number of
possible plans.

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

We could mark stored plans as durty in such cases to force re-compiling
when an application tries to use this plan.

Vadim