Re: [HACKERS] Speedups

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

I have thought about this. If we take a query string, remove all quoted
constants and numeric constants, we can automatically split apart the
query from the parameters. We can then look up the non-parameter query
in our cache, and if it matches, replace the new contants with the old
and run the query.

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)